MySQL Forums
Forum List  »  Newbie

Re: Query performance
Posted by: Rick James
Date: November 21, 2014 02:15PM

> query_cache_size = 2G

Very, very, bad!
Don't set it to more than 50M. Why... When an insert occurs, it has to scan the 2G to find which entries refer to the same table and purge them. Generally, production servers with continual inserts should turn off the QC.

> key_buffer = 2G

To high for a 4GB machine; change to 200M if your tables are ENGINE=MyISAM.
If they are not, then what is the value of innodb_buffer_pool_size?

What version?

Is it a 32-bit or a 64-bit build? (On the 8GB machine, a 32-bit build cannot access more than 4GB.)

> max_allowed_packet = 500M

Dangerously high.
Change to 16M or make a convincing argument for a larger value.

Here are some tips on how to set the important tunables:
http://mysql.rjweb.org/doc.php/memory
Don't randomly set other VARIABLEs without understand their side effects. Most default values are good enough.

As for why the two machines act differently...
* The above changes will help both machines.
* It is hard to say what to look at next to figure out the differences -- diff the SHOW VARIABLES; MySQL version number; traffic diffs (more inserts on one machine would slow down that machine due to the QC); etc.

Options: ReplyQuote


Subject
Written By
Posted
November 21, 2014 09:45AM
November 21, 2014 12:56PM
Re: Query performance
November 21, 2014 02:15PM
November 22, 2014 09:00AM
November 22, 2014 11:11AM


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.