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?
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:
(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

Written By
Re: Need help to improve innodb performance
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.