MySQL Forums
Forum List  »  InnoDB

Re: Need help to improve innodb performance
Posted by: Phoenix Kiula
Date: April 17, 2009 03:50PM

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. 

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need help to improve innodb performance
6503
April 17, 2009 03:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.