MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Rick James
Date: January 11, 2012 10:59AM

Most advice is captured here:
http://mysql.rjweb.org/doc.php/memory
It will say that this is too big for an InnoDB-only system:
key_buffer_size = 512M
and that this is too small:
innodb_buffer_pool_size = 15G

long_query_time = 10
Change to 2 (seconds), so that more useful info can be gathered in the slowlog.

> to reduce disk-io
Optimize the queries; have the right indexes; etc.
Once you have some info in the slowlog, find the naughtiest queries, then come back here for more advice. Be sure to include SHOW CREATE TABLE and EXPLAIN.

To get a head-start on looking for I/O bottlenecks, decrease innodb_buffer_pool_size to a few GB (smaller than your data+index), run for awhile, then look into the slowlog. This technique exposes inherently=I/O-bound queries so they cannot hide in a plenty-big cache.

"Huge", by itself, is not the issue...
* If the access patterns involve PRIMARY KEY access, and "hot spots" (such as accessing only 'recent' data), the database can be efficient even when it is much bigger than RAM.
* If you have _random_ keys (GUIDs, MD5s, etc), even INSERTs will be I/O bound.
* A missing index can lead to a "table scan", which leads to lots of I/O, plus purging the cache, thereby hurting all other queries.
* Data Warehousing can be sped up by order(s) of magnitude by creating 'summary tables' ("materialized views"), but this is a manual process in MySQL.
* PARTITIONing can help in a _small_ number of use cases -- it is not a panacea.
* If you need to purge old data (eg, over 90 days old) see
http://mysql.rjweb.org/doc.php/deletebig
* etc.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.1.49 - Optimal settings for memory usage
3841
January 11, 2012 10:59AM


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.