-
Notifications
You must be signed in to change notification settings - Fork 0
MySQL Performance
Las principales variables de configuracion que influyen en el rendimiento del servidor MySQL son:
This is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).
This is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.
If you are often facing the ‘Too many connections’ error, max-connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.
The query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your MySQL application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it.
The number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
I simply added a low table_definition_cache (=50) to the default my.cnf and reduced memory footprint from ~500Mb to ~80Mb. This is on a virtual server for development purposes with only one user and a few dozen tables. It was the only option that had a significant effect on memory usage.
The table_definition_cache is used as a soft limit on the InnoDB in-memory data dictionary in 5.6, and some people have reported saving a lot of memory by reducing this variable.
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 5.2.5, “Status Variables”. If the value of Opened_tables is large and you don't do FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable. For more information about the table cache, see Section 6.4.8, “How MySQL Opens and Closes Tables”. Before MySQL 5.1.3, this variable is called table_cache.
Increasing the size of the table cache may really help you. But you must be careful not to make the value too large. All operating systems have a limit on the number "open file pointer" a single process may have. If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.