- Should use MySQLTunner tool to analyze
- query_cache_size parameter is used to allocate an amount of memory to cache the frequently executed queries and return the result set back to the client. E.g.: 128M
- query_cache_limit (default 1MB) parameter sets the maximum result sets size stored in the query cache. E.g.: 4M
- To log slow queries:
- tmp_table_size and max_heap_table_size (default = 16MB): The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size). If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. E.g.: 256M.
- join_buffer_size: The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. E.g.: 2M.
- max_allowed_packet(default = 16MB): When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.
- worker_processes: should be = number of CPU cores
- worker_connections: max_clients = worker_processes * worker_connections