Optymalizacja serwera bazy MySQL
Optymalizacja parametrów pracy bazy danych MySQL pozwala na przyspieszenie działania systemu InfoBiz Server. Obserwowane przyspieszenie może osiągnąć 500% na tej samym serwerze fizycznym.
Key buffer
Bufor indeksów (key buffer) przechowuje w pamięci klucze indeksowe bazy danych. Limit tej przestrzeni powinien pozwolić na przechowanie w pamięci wszystkich kluczy. Wielkość tego parametru trzeba określić doświadczalnie. Większe witryny potrzebują większej przestrzeni do przechowywania indeksów. Przykładowy zapis pozwoli alokować przestrzeń 500MB:
key_buffer = 500M
To find a suitable value for the key buffer, investigate the status variables key_read_requests and key_reads. The key_read_requests is the total number of key requests served from the cache while the key_reads shows the number of times MySQL had to access the filesystem to fetch the keys.
The lower the number of key_reads the better. The more memory you allocate to the key buffer the more requests will be served from the cache. There will always be some keys that need to be read from disk (for example when data changes), so the value will never be zero. By comparing the two values you see the hit ratio of your key buffer. The key_read_requests should be much larger than the key_reads. 99% cached requests is a good number to aim for in a read-intensive environment.
Table cache
The table cache tells MySQL how many tables it can have open at any one time. In SQL queries, several tables are typically joined. The rule of thumb is that you should multiply the maximum number of connections (described below) by the maximum number of tables used in joins. For example, if the maximum number of connections is set to 400, the table cache should be at least 400 * 10. The configuration setting below shows a table cache of 4000:
table_cache = 4000
Sort buffers
MySQL sorts query results before they are returned. The sort buffer is per connection, so you must multiply the size of the sort buffer by the maximum number of connections to predict the server memory requirements. In our case we use a 3MB sort buffer with 400 max connections, which can use a total of 1.2GB of memory.
sort_buffer_size = 3M
Max connections
MySQL has a limitation on the number of concurrent connections it can keep open. If you are using persistent connections in PHP, each process in Apache will keep a connection to MySQL open. This means that you need to set the number of max connections in MySQL to equal or greater than the number of Apache processes that can connect to the database. In a clustered environment, you must add up the processess on each webserver to determine the maximum. Setting sufficient max connections also ensures that users do not get errors about connecting to the MySQL database. The setting for 400 connections is shown below.
max_connections = 400
Query cache
MySQL is capable of caching the results of a query. The next time the same query is executed the result is immediately returned, as it is read from the cache rather than the database. For a read-intensive site, this can provide a significant performance improvement.
To enable the query cache, set the type to "1":
query_cache_type = 1
You can set the maximim size of each query that can be cached. If the query result is larger than the query cache limit, the results will not be cached. This is normally set to 1M:
query_cache_limit = 1M
The amount of memory globally available for query caches is set with the query cache size setting. This should be fairly large, and should be increased in size for large databases.
query_cache_size = 100M
To tune the query cache, use the show status command. This can be used to determine which settings need to be altered and to see the effect of alterations. The show status command will show you if the query cache is heavily in use and if you have free memory, which indicates whether the query cache buffer settings should be increased or decreased.
mysql> show status like "qcache%"; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 34 | | Qcache_free_memory | 16466312 | | Qcache_hits | 1313227 | | Qcache_inserts | 78096 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3328 | | Qcache_queries_in_cache | 140 | | Qcache_total_blocks | 346 | +-------------------------+----------+ 8 rows in set (0.00 sec)
do góry