MySQL Forums
Forum List  »  InnoDB

Re: Need help to improve innodb performance
Posted by: Rick James
Date: April 17, 2009 10:00PM

Phoenix --
The innodb stats seem rather low, as if it is not very active.
Is it entirely InnoDB?
SHOW GLOBAL STATUS LIKE 'Threads%';
Do that several times -- If Threads_running is more than, say, 10, then the connections are stumbling over each other and we should look at your slow queries.
If Threads_connected is > 1000, or if Max_used_connections > 1000, then I question whether the clients are disconnecting when they are finished.

Also, this would give you a feel for the above items:
SHOW PROCESSLIST;
(when logged in with sufficient privileges to see more than yourself).

query_cache_size=2G -- NO! This is a case where "bigger is _not_ better". I would not go more than 200M. Why? Because
* Locks have to be acquired to get at the QC; this inhibits making much use of multiple CPUs.
* Some of the actions on the QC are linear -- bigger -> slower
* "Pruning" is very costly.

2 x quad core servers -- unless you have the latest patches from Google, turn off 4 of the cores. The locks make 8 cores run slower than 4 (often).

RAID -- I assume it has BBWC and its write cache is active? That will help writes a lot.

100K users at once. No you don't. You have limited it to 1500. Each connection takes some amount of RAM (200K?) even when idle; can take more when it is sorting, reading, etc.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need help to improve innodb performance
3068
April 17, 2009 10:00PM


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.