Allocating RAM for MySQL
The Short Answer If using just MyISAM, set key_buffer_size to 20% of _available_ RAM. (Plus innodb_buffer_pool_size=0)
If using just InnoDB, set innodb_buffer_pool_size to 70% of _available_ RAM. (Plus key_buffer_size = 10M, small, but not zero.)
Rule of thumb for tuning mysql:
SHOW GLOBAL STATUS LIKE 'Key%'; then calculate Key_read_requests / Key_reads If it is high (say, over 10), then the key_buffer is big enough.
What is the buffer_pool?InnoDB does all its caching in a the "buffer pool", whose size is controlled by innodb_buffer_pool_size. It contains 16KB data and index blocks from the open tables, plus some maintenance overhead.
MySQL 5.5 (and 5.1 with the "Plugin") lets you declare the block size to be 8KB or 4KB. MySQL 5.5 allows multiple buffer pools; this can help because there is one mutex per pool, thereby relieving some of the Mutex bottleneck.
Mutex bottleneck MySQL was designed in the days of single-CPU machines, and designed to be easily ported to many different architectures. Unfortunately, that lead to some sloppiness in how to interlock actions. There are small number (too small) of "mutexes" to gain access to several critical processes.
HyperThreading and Multiple cores (CPUs) Short answers:
If you have more than 4GB of RAM, the excess beyond 4GB is _totally_ inaccessable and unusable on a 32-bit OS.
Secondly, the OS probably has a limit on how much RAM it will allow any process to use. Example: FreeBSD's maxdsiz, which defaults to 512MB. Example: $ ulimit -a ... max memory size (kbytes, -m) 524288 .
So, once you have determined how much RAM is available to mysqld, then apply the 20%/70%, but round down some. If you get an error like [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes), it probably means that MySQL exceeded what the OS is willing to give it.
Decrease the cache settings.
64-bit OS with 32-bit MySQLThe OS is not limited by 4GB, but MySQL is. If you have at least 4GB of RAM, then maybe these would be good:
innodb_buffer_pool_size = 70% of RAM. If you have lots of RAM and are using 5.5, then consider having multiple pools. Recommend 1-16 instances, such that each one is no smaller than 1GB. (Sorry, no metric on how much this will help; probably not a lot.)
Meanwhile, set key_buffer_size = 20M (tiny, but non-zero) If you have a mixture of engines, lower both numbers. max_connections, thread_stack Each "thread" takes some amount of RAM.
This used to be about 200KB; 100 threads would be 20MB, not a signifcant size. If you have max_connections = 1000, then you are talking about 200MB, maybe more. Having that many connections probably implies other issues that should be addressed. Thread stack overrun rarely happens. If it does, do something like thread_stack=256K table_cache (table_open_cache) (The name changed in some version.) The OS has some limit on the number of open files it will let a process have.
Each table needs 1 to 3 open files. Each PARTITION is effectively a table. Most operations on a partitioned table open _all_ partitions. In *nix, ulimit tells you what the file limit is. The maximum value is in the tens of thousands, but sometimes it is set to only 1024. This limits you to about 300 tables. More discussion on ulimit (This paragraph is in disputed.) On the other side, the table cache is (was) inefficiently implemented -- lookups were done with a linear scan.
Hence, setting table_cache in the thousands could actually slow down mysql. (Benchmarks have shown this.) You can see how well your system is performing via SHOW GLOBAL STATUS; and computing the opens/second via Opened_files / Uptime If this is more than, say, 5, table_cache should be increased. If it is less than, say, 1, you might get improvement by decreasing table_cache. Query Cache Short answer: query_cache_type = OFF and query_cache_size = 0 The QC is effectively a hash mapping SELECT statements to resultsets.
There are many aspects of the "Query cache"; many are negative.