Rick, there's some really good advice. So I hope you can help.
My question is similar. I have a 32GB RAM on 2 x quad core servers with RAID 10 and SCSI hard disks 15k rpm.
We get about 100,000 users connecting to our servers at the same time. The db server chokes every time! I have the max_connections variable set at 1500, but I suppose this means a lot of memory at 1.5G per connection? The buffer_pool is 15G.
Following are my settings but would love to hear what else to do:
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 8
innodb_thread_concurrency = 16
max_connections=1500
innodb_buffer_pool_size = 15G
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=8M
innodb_commit_concurrency = 0
innodb_concurrency_tickets = 500
query_cache_limit=2G
query_cache_size=2G
query_cache_type=1
table_cache=1200
thread_cache_size=256
Below is my InnoDB status information from phpmyadmin:
Variable Value Description
Variables InnoDB Status Documentation
Innodb_buffer_pool_pages_data 5,468 The number of pages containing data (dirty or clean).
Innodb_buffer_pool_pages_dirty 5 The number of pages currently dirty.
Innodb_buffer_pool_pages_flushed 1,378 The number of buffer pool pages that have been requested to be flushed.
Innodb_buffer_pool_pages_free 977 k The number of free pages.
Innodb_buffer_pool_pages_latched 0 The number of latched pages in InnoDB buffer pool. These are pages currently being read or written or that can't be flushed or removed for some other reason.
Innodb_buffer_pool_pages_misc 222 The number of pages busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index. This value can also be calculated as Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data.
Innodb_buffer_pool_pages_total 983 k Total size of buffer pool, in pages.
Innodb_buffer_pool_read_ahead_rnd 1 The number of "random" read-aheads InnoDB initiated. This happens when a query is to scan a large portion of a table but in random order.
Innodb_buffer_pool_read_ahead_seq 34 The number of sequential read-aheads InnoDB initiated. This happens when InnoDB does a sequential full table scan.
Innodb_buffer_pool_read_requests 9,619 k The number of logical read requests InnoDB has done.
Innodb_buffer_pool_reads 3,326 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb_buffer_pool_wait_free 0 Normally, writes to the InnoDB buffer pool happen in the background. However, if it's necessary to read or create a page and no clean pages are available, it's necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size was set properly, this value should be small.
Innodb_buffer_pool_write_requests 12 k The number writes done to the InnoDB buffer pool.
Innodb_data_fsyncs 384 The number of fsync() operations so far.
Innodb_data_pending_fsyncs 0 The current number of pending fsync() operations.
Innodb_data_pending_reads 0 The current number of pending reads.
Innodb_data_pending_writes 0 The current number of pending writes.
Innodb_data_read 92 M The amount of data read so far, in bytes.
Innodb_data_reads 3,428 The total number of data reads.
Innodb_data_writes 1,034 The total number of data writes.
Innodb_data_written 46 M The amount of data written so far, in bytes.
Innodb_dblwr_pages_written 1,378 The number of doublewrite writes that have been performed and the number of pages that have been written for this purpose.
Innodb_dblwr_writes 60 The number of doublewrite writes that have been performed and the number of pages that have been written for this purpose.
Innodb_log_waits 0 The number of waits we had because log buffer was too small and we had to wait for it to be flushed before continuing.
Innodb_log_write_requests 788 The number of log write requests.
Innodb_log_writes 191 The number of physical writes to the log file.
Innodb_os_log_fsyncs 255 The number of fsyncs writes done to the log file.
Innodb_os_log_pending_fsyncs 0 The number of pending log file fsyncs.
Innodb_os_log_pending_writes 0 Pending log file writes.
Innodb_os_log_written 485 k The number of bytes written to the log file.
Innodb_page_size 16 k The compiled-in InnoDB page size (default 16KB). Many values are counted in pages; the page size allows them to be easily converted to bytes.
Innodb_pages_created 10 The number of pages created.
Innodb_pages_read 5,458 The number of pages read.
Innodb_pages_written 1,378 The number of pages written.
Innodb_row_lock_current_waits 0 The number of row locks currently being waited for.
Innodb_row_lock_time 0 The total time spent in acquiring row locks, in milliseconds.
Innodb_row_lock_time_avg 0 The average time to acquire a row lock, in milliseconds.
Innodb_row_lock_time_max 0 The maximum time to acquire a row lock, in milliseconds.
Innodb_row_lock_waits 0 The number of times a row lock had to be waited for.
Innodb_rows_deleted 48 The number of rows deleted from InnoDB tables.
Innodb_rows_inserted 1,365 The number of rows inserted in InnoDB tables.
Innodb_rows_read 23 M The number of rows read from InnoDB tables.
Innodb_rows_updated 521 The number of rows updated in InnoDB tables.