List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:February 19 2011 5:17pm
Subject:Re: Super active table optimization
View as plain text  
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 = 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 = 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                 = 5120M
innodb_buffer_pool_instances            = 5
innodb_purge_threads                    = 1
innodb_max_purge_lag                    = 200000
innodb_max_dirty_pages_pct              = 60
innodb_additional_mem_pool_size         = 64M
innodb_log_file_size                    = 512M
innodb_log_buffer_size                  = 256M
innodb_thread_concurrency               = 32
innodb_thread_sleep_delay               = 10
innodb_flush_log_at_trx_commit          = 2
innodb_support_xa                       = 1
innodb_lock_wait_timeout                = 50
innodb_table_locks                      = 0
innodb_file_format                      = barracuda
innodb_file_per_table                   = 1
innodb_open_files                       = 600
innodb_io_capacity                      = 800
innodb_read_io_threads                  = 8
innodb_write_io_threads                 = 8
transaction-isolation                   = READ-COMMITTED


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Super active table optimizationBryan Cantwell19 Feb
  • Re: Super active table optimizationWalter Heck19 Feb
  • Re: Super active table optimizationReindl Harald19 Feb