These configurations are tested on Percona Server 5.5 which is forked from mysql server. These configurations should also appy to standard Mysql server. Percona server is running on a Centos machine.
1. Quick Server Configuration
If you are looking for a quick configuration for your server (MySQL Enterprise/Community or Percona Server), you can use following link:https://tools.percona.com/wizard
This link will provide a wizard that asks needs of your application and will try to create a my.cnf file that will fulfill your needs.
2. Quick Server Performance Check
Before dealing with performance issues, you should know the misconfigured/lacking points. You can use following script to get a overview of your server performance.http://www.day32.com/MySQL/
When you run this script, it will give a analysis of different configuration points.
3. Configuration Parameters
Mysql is configured via system variables. All system variables have a default value. These variables can be given at server startup from command line or from configuration files. On Linux this configuration file is my.cnf and itsWindows counterpart is my.ini. Some system variables are dynamic meaning that they can be modified within mysql shell when server is running.To see current values:
mysql> SHOW VARIABLES;
3.1. Query Cache (query_cache_size)
Quick Configuration (my.cnf file):query_cache_size=128M query_cache_limit=1M query_cache_type=1
query_cache_size is the amount of memory allocated to cache all queries
query_cache_limit is maximum size of SELECT result that can be cached. Query results bigger than this limit are not cached.
query_cache_type has 3 options:
- 0: query cache is disabled
- 1: all queries are cached, except the ones beginning with SELECT NO_SQL_CACHE
- 2: queries only beginning with SELECT SQL_CACHE are cached
Key points:
- The query cache stores the text of a SELECT statement together with the corresponding result.
- If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again
- The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
- Mysql does not return stale data, when table data is changed, cached queries using table are flushed.
- Queries must be exactly the same to be counted as identical. For example, following queries are not identical:
select * from test; SELECT * FROM test;
The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. In an environment with tables are constantly changing, maintaining query cache can cause performance impact.
Monitor Query Cache:
Following command gives query cache statistics:
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 134182560 | | Qcache_hits | 3694 | | Qcache_inserts | 19 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 9 | | Qcache_queries_in_cache | 14 | | Qcache_total_blocks | 31 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
3.2. Table Cache (table_open_cache/table_cache)
When different clients tries to operate on the same table simultaneously, table is opened independently for each client. If open tables count reaches maximum number specified by table_open_cache, performance can degrade.table_cache is renamed as table_open_cache with mysql 5.1.3 release.
Quick Configuration (my.cnf file):
table_open_cache=2048 open-files-limit=65536
table_open_cache is the maximum number of open tables that mysql can have
open_files_limit is the maximum number of file descriptors which mysql can have. This number cannot be smaller than operating system wide limit.
In Unix systems, you can get the maximum number of file descriptors per process, by typing :
> ulimit -n
Key Points:
- table_open_cache is related to max_connections. If there are 100 concurrent connections and your joins include 10 tables, it is wise to give table_open_cache as 100*10=1000.
- If MyISAM usage is heavy, you should consider that MyISAM tables takes 2 file descriptors instead of 1. Additionally, if you use same MyISAM table again in the query, for example joining by itself, 1 additional file descriptor is created.
- Mysql is limited by operating system limitations in terms of file descriptors. On Unix systems, you can see file descriptor limit per process by ulimit -n. You can increase this limit from mysql configuration by open-files-limit variable. If this limit is reached, mysql gives "Too many open files" error.
Monitor Table Cache:
You can monitor and observe table_open cache by typing:
mysql> SHOW GLOBAL STATUS LIKE '%Opened_tables%';
Caveat: You can run this command several times in busiest times of server. If there are increases in number, you should think increasing table_open_cache
3.3. Key Buffer Size (key_buffer_size)
MyISAM indexes are shared by all clients and key_buffer_size specifies the limit for this buffer in memory. The limit is 4GB-1 for 32 bit machines and larger for 64 bit machines. It is recommended that this limit should be close to 25% of system memory. If you are using MyISAM tables heavily, you can try to increase this value.Quick Configuration (my.cnf file):
key_buffer_size=4GB
Key Points:
- Under heavy MyISAM usage, this variable can be crucial.
Monitor Key Buffer Size:
You can list status of operations related to key reads and writes.
mysql> SHOW GLOBAL STATUS LIKE 'Key_%';
4. Further Reading
You can get more detail from following sites.http://www.mysqlperformanceblog.com/
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html
Hiç yorum yok:
Yorum Gönder