From: Reindl Harald
Date: February 19 2011 5:17pm
Subject: Re: Super active table optimization
List-Archive: http://lists.mysql.com/mysql/224459
Message-Id: <4D5FFB2B.2090807@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enig83E976E40EDF23809D6EA75A"

--------------enig83E976E40EDF23809D6EA75A
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Am 19.02.2011 16:59, schrieb Bryan Cantwell:
> I have an innodb table with 200 million rows and growing.

more interesting is the db-size!

> innodb_buffer_pool_size =3D 768M

roo small, in the best case it is as large as the database
or so large as pissoble to avoid swapping

> innodb_log_file_size =3D 192M

1/4 of buffer_pool is ok but if your buffer-pool is too small
it is a little difficult to increase this, i would never set
this lower than 512 MB which is also ok for larger databases

this are our innodb settings for a dbmail-instance
and mysql 5.5 and we are speaking about 15 GB data
and 3 Mio. rows, the backend is a san-storage

in this setup we can increase up to 1.000 inserts per second

innodb_buffer_pool_size                 =3D 5120M
innodb_buffer_pool_instances            =3D 5
innodb_purge_threads                    =3D 1
innodb_max_purge_lag                    =3D 200000
innodb_max_dirty_pages_pct              =3D 60
innodb_additional_mem_pool_size         =3D 64M
innodb_log_file_size                    =3D 512M
innodb_log_buffer_size                  =3D 256M
innodb_thread_concurrency               =3D 32
innodb_thread_sleep_delay               =3D 10
innodb_flush_log_at_trx_commit          =3D 2
innodb_support_xa                       =3D 1
innodb_lock_wait_timeout                =3D 50
innodb_table_locks                      =3D 0
innodb_file_format                      =3D barracuda
innodb_file_per_table                   =3D 1
innodb_open_files                       =3D 600
innodb_io_capacity                      =3D 800
innodb_read_io_threads                  =3D 8
innodb_write_io_threads                 =3D 8
transaction-isolation                   =3D READ-COMMITTED


--------------enig83E976E40EDF23809D6EA75A
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1f+ysACgkQhmBjz394AnnVJQCghjRSOhqkPS2zBqmYftCz8nYg
mNMAn0eHGg0auEgiWWCDjtyOUw7l7DFH
=83LC
-----END PGP SIGNATURE-----

--------------enig83E976E40EDF23809D6EA75A--