Optimizar MySQL

MySQL optimization (I)

Posted by Javier Andrés Alonso in on lunes 27 de septiembre de 2010

When MySQL is installed by a software packages manager such as yum or aptitude, we get instantly a valid database server ready to work.

It turns out that installation is performed with a series of settings applied by default, which are not the most optimal for our system in most cases, since there are many more variables (memory, CPU, other services configured, etc.) that we have to consider.

Therefore, it is the best that once the databases have been created, to leave a margin large enough time (for example a couple of days) to make the system stable and to be able to acquire its real work load.

In that moment , we can apply any sort of MySQL optimization or tunning tool, such as MySQL Performance Tuning Primer Script or MySQLTuner.

Then we are going to use the first tool on a Zabbix 1.8.1 installation, utilizing CentOS 5.4 64 bits with a MySQL 5.0.77 database.

[root@centos ~]# ./tuning-primer.sh

Using login values from ~/.my.cnf
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /var/lib/mysql/mysql.sock
Using: /var/lib/mysql/mysql.sock
Would you like to provide a different socket?: [y/N]
Do you have your login handy ? [y/N] : y
User: root

Would you like me to create a ~/.my.cnf file for you? [y/N] :

 - By: Matthew Montgomery -

MySQL Version 5.0.77 x86_64

Uptime = 2 days 0 hrs 53 min 41 sec
Avg. qps = 19
Total Questions = 3375129
Threads Connected = 15

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 3375150 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

Current max_connections = 100
Current threads_connected = 15
Historic max_used_connections = 21
The number of used connections is 21% of the configured maximum.
Your max_connections variable seems to be fine.

Current InnoDB index space = 112 M
Current InnoDB data space = 240 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

Max Memory Ever Allocated : 75 M
Configured Max Per-thread Buffers : 274 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 292 M
Physical Memory : 1.96 G
Max memory limit seem to be within acceptable norms

Current MyISAM index space = 71 K
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 6
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

Query cache is supported but not enabled
Perhaps you should set the query_cache_size

Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

Current join_buffer_size = 132.00 K
You have had 4 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

Current table_cache value = 64 tables
You have a total of 105 tables
You have 64 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache

Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 85195 temp tables, 31% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

Current read_buffer_size = 128 K
Current table scan ratio = 0 : 1
read_buffer_size seems to be fine

Current Lock Wait ratio = 0 : 3375450
Your table locking seems to be fine

When we run the second tool, we pick up the following information:

[root@centos ~]# ./mysqltuner.pl

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 240M (Tables: 88)
[!!] BDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 56m 0s (3M q [19.168 qps], 23K conn, TX: 530M, RX: 331M)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (15% of installed RAM)
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/67.0K
[!!] Key buffer hit rate: 85.3% (239K cached / 35K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[!!] Temporary tables created on disk: 31% (38K on disk / 123K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 9K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[!!] InnoDB data size / buffer pool: 240.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 240M)

In the next two article, we will see how to fit these values.

MySQL optimization (II)

Posted by Javier Andrés Alonso in on domingo 3 de octubre de 2010

Continuing with the previous article about MySQL optimization (I), we are going to start with one of the suggestions provided by the tunning-primer.sh script: The slow query log is NOT enabled.

The queries which spend a lot of CPU (its running time is very high, for example more than 5 seconds) are named slow queries, and it is appropriate to register them in order to be optimized by the developers.

Other good measure can be to activate the logging of those queries which do not use indexes, since this kind of query increases the computer resources consumption because it is necessary more time to loop through the tables. This sort of query should be treated too.

[root@centos ~]# cat /etc/my.cnf
long_query_time = 5

Other variable showed is related to the thread cache (thread_cache_size), which indicates us that seems to be fine.

The size of this parameter depends on the speed with which the new threads are created (Threads_created). For the case that we are discussing (Zabbix database), many threads are not generated quickly, thus we will enable this cache for safety and we will set a low value, such as 32.

[root@centos ~]# cat /etc/my.cnf
thread_cache_size = 32

So as to display the threads state, we can run the following order:

mysql> show status like 'threads%';
| Variable_name | Value |
| Threads_cached | 0 |
| Threads_connected | 15 |
| Threads_created | 23428 |
| Threads_running | 1 |
4 rows in set (0.00 sec)

Another parameter offered by the script which seems to be also properly configured is the maximum number of allowed connections (Your max_connections variable seems to be fine). In order to see the maximum number of connections which have been used, we can run the following command:

mysql> show status like 'max_used_connections';
| Variable_name | Value |
| Max_used_connections | 21 |
1 row in set (0.00 sec)

If we would want to increase the maximum number of allowed connections (100 by default), we could edit the max_connections parameter in the MySQL configuration file:

[root@centos ~]# cat /etc/my.cnf

wait_timeout = 10
max_connect_errors = 100

Two other parameters to consider are wait_timeout (when this time is exceeded by an idle connection, it will be closed) and max_connect_errors (maximum number of times that a connection can abort or fail – 10 by default).

Another recommendation given by the script with regard to the InnoDB data storage engine, is to set the innodb_buffer_pool_size variable around 60-70% of the total system memory. For the installation of Zabbix, we will allocate 1024 MB because the computer has got 2 GB.

[root@centos ~]# cat /etc/my.cnf
innodb_buffer_pool_size = 1024M

In the case of tables created by the MyISAM engine, the key parameter is key_buffer_size, which is already correctly adjusted (Your key_buffer_size seems to be fine) because the Zabbix database does not use this kind of tables.

For databases which utilize this sort of search engine with its tables, it is recommended to set this parameter around 25% of the total system memory.

Another way to adjust it is consulting the key_read_requests and key_reads values. The first of them indicates the number of requests which have used the index (memory) and the second, the number of requests made directly from the disk. Then it is clear that is interesting that key_reads is as low as possible and key_read_requests as high.

mysql> show status like '%key_read%';
| Variable_name | Value |
| Key_read_requests | 242148 |
| Key_reads | 35618 |
2 rows in set (0.00 sec)

An optimal ratio should be around 1% (for each disk request made, 100 are performed from the buffer in memory).

If we want to fit this variable, we have to set its value into the my.cnf file.

[root@centos ~]# cat /etc/my.cnf
key_buffer_size = 32M

MySQL optimization (IV)

Posted by Javier Andrés Alonso in on domingo 24 de octubre de 2010

This is the last article about MySQL tunning, and we are going to present the way to change the mentioned parameters. The previous issue was MySQL optimization (III).

These parameters were established into the MySQL configuration file (my.cnf). Such modifications will not take effect until the mysqld service is rebooted. But there may be the case where we cannot reset the service, for example because the computer is on a production environment.

In this situation we must know that in MySQL, there are several dynamic variables wich can be modified at runtime. In order to see all system variables, we can run the following order:

mysql> show global variables;
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8384512 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 262144 |

So as to show the value of a concrete variable:

mysql> show global variables like 'table_cache';
| Variable_name | Value |
| table_cache | 64 |
1 row in set (0.00 sec)

And if we want to modify its value at runtime (it must be changed globally wherever possible):

mysql> set global table_cache=1024;
Query OK, 0 rows affected (0.00 sec)

MySQL optimization (III)

Posted by Javier Andrés Alonso in on domingo 10 de octubre de 2010

Let’s go on with the series of articles about MySQL tunning. Remember that in the previous issue, MySQL optimization (II), we got going to break down the suggestions provided by the Tunning Primer Script.

Now, we are going to continue regarding the query cache, since the script shows us which is disabled (Query cache is supported but not enabled).

When a query is executed, the database engine always performs the same task: processes the query, determines how to run it, loads the information from the disk and returns the value to the client. Through this cache, MySQL saves the result of a particular query in memory, so that in many cases the system performance can be significantly improved.

In order to display the query cache status, we can run the following order:

mysql> show status like 'qcache%';
| Variable_name | Value |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
8 rows in set (0.00 sec)

The most important values are Qcache_free_memory (free cache memory), Qcache_inserts (insertions performed in the cache), Qcache_hits (successful insertions) and Qcache_lowmem_prunes (number of times that the cache runs out of memory and must be cleaned).

The result of Qcache_inserts/Qcache_hits division is known as percentage of losses. If the value of this ratio is for example 20%, it means that the 80% of the queries are attended from the cache.

Other important parameter is Qcache_free_blocks, which indicates us that the memory is fragmented whether it has got a high value. To defragment the non contiguous memory blocks, we can run the following command (in fact, there should be set a cron job to run this command every 4 or 8 hours).

[root@centos ~]# mysql -u root -p -e "flush query cache"

[root@centos ~]# crontab -e
0 */4 * * * mysql -u root -pxxxxxx -e "flush query cache"

The parameter wich allows us to adjust the cache size is Qcache_lowmem_prunes, since the larger it is, the more times the cache must be restarted. In order to fit the query cache size, we must set a value for the query_cache_size parameter, inside the MySQL configuration file.

[root@centos ~]# cat /etc/my.cnf
query_cache_size = 128M
query_cache_limit = 4M
query_cache_type = 1

The query_cache_limit parameter establishes the maximum result wholes size stored in the query cache. If we want whenever possible a query is cached, we must activate the query_cache_type variable.

Another recommendation is related to the table cache (You should probably increase your table_cache). The table_cache variable indicates how many tables can be simultaneously opened. Each table is represented by one disk file (descriptor) and it must be opened before being read.

In order to adjust this parameter, we must take a look at the Open_tables (currently open tables) and Opened_tables (tables wich have been opened) variables.

mysql> show global status like 'open%tables';
| Variable_name | Value |
| Open_tables | 64 |
| Opened_tables | 30 |
2 rows in set (0.00 sec)

If the Opened_tables grows up very quicly, it means that are opening and closing tables for lack of descriptors. In that case, we should increase the table_cache value.

To modify this value in MySQL, we have to edit its configuration file. As well we have to take into account that this variable has to be always less than open_files_limit. Otherwise, we must change it.

And besides, we can also set the table_definition_cache variable, which represents the number of table definitions that can be stored in the definition cache (it should be normally the same as table_cache) and unlike the table_cache, it does not use file descriptors.

[root@centos ~]# cat /etc/my.cnf
table_cache = 512
table_definition_cache = 512

open_files_limit = 1024

The script also shows us that around 30% of the temporary tables are created in the disk, with what we could increase the size of the tmp_table_size (if a temporary table in memory exceeds this size, it is automatically moved to disk) and/or max_heap_table_size (maximum value that the tables can grow up in memory) variables.

So as to set correctly these values, you can analyze the Created_tmp_disk_tables (number of temporary tables created on disk) and Created_tmp_tables (number of temporary tables created in memory).

mysql> show status like 'created_tmp%';
| Variable_name | Value |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 1 |
3 rows in set (0.00 sec)

As you can see in the previous results, no table is made on disk, then this situation does not correspond with the 30% data provided by the script. This is due to the script does not check the real value of the temporary tables created on disk, since looking its code we can confirm that the script runs a benchmark to generate 5000 aleatory registers and measures its performance (“show /*!50000 global */ status like…“).

In the MySQL configuration file, we can change the tmp_table_size and max_heap_table_size values.

[root@centos ~]# cat /etc/my.cnf
tmp_table_size = 64M
max_heap_table_size = 32M

MySQL optimization (IV)

Posted by Javier Andrés Alonso in on domingo 24 de octubre de 2010

This is the last article about MySQL tunning, and we are going to present the way to change the mentioned parameters. The previous issue was MySQL optimization (III).

These parameters were established into the MySQL configuration file (my.cnf). Such modifications will not take effect until the mysqld service is rebooted. But there may be the case where we cannot reset the service, for example because the computer is on a production environment.

In this situation we must know that in MySQL, there are several dynamic variables wich can be modified at runtime. In order to see all system variables, we can run the following order:

mysql> show global variables;
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8384512 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 262144 |

So as to show the value of a concrete variable:

mysql> show global variables like 'table_cache';
| Variable_name | Value |
| table_cache | 64 |
1 row in set (0.00 sec)

And if we want to modify its value at runtime (it must be changed globally wherever possible):

mysql> set global table_cache=1024;
Query OK, 0 rows affected (0.00 sec)

EXTRAIDO DE: [http://redes-privadas-virtuales.blogspot.com/search?q=MySQL]

If you are interested in how to create fast MySQL queries, this article is for you

  1. Use persistent connections to the database to avoid connection overhead.
  2. Check all tables have PRIMARY KEYs on columns with high cardinality (many rows match the key value). Well,`gender` column has low cardinality (selectivity), unique user id column has high one and is a good candidate to become a primary key.
  3. All references between different tables should usually be done with indices (which also means they must have identical data types so that joins based on the corresponding columns will be faster). Also check that fields that you often need to search in (appear frequently in WHERE, ORDER BY or GROUP BY clauses) have indices, but don’t add too many: the worst thing you can do is to add an index on every column of a table (I haven’t seen a table with more than 5 indices for a table, even 20-30 columns big). If you never refer to a column in comparisons, there’s no need to index it.
  4. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.
  5. Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them.
  6. Use leftmost index prefix — in MySQL you can define index on several columns so that left part of that index can be used a separate one so that you need less indices.
  7. When your index consists of many columns, why not to create a hash column which is short, reasonably unique, and indexed? Then your query will look like: SELECT * FROM table WHERE hash_column = MD5( CONCAT(col1, col2) ) AND col1=’aaa’ AND col2=’bbb’;
  8. Consider running ANALYZE TABLE (or myisamchk –analyze from command line) on a table after it has been loaded with data to help MySQL better optimize queries.
  9. Use CHAR type when possible (instead of VARCHAR, BLOB or TEXT) — when values of a column have constant length: MD5-hash (32 symbols), ICAO or IATA airport code (4 and 3 symbols), BIC bank code (3 symbols), etc. Data in CHAR columns can be found faster rather than in variable length data types columns.
  10. Don’t split a table if you just have too many columns. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row.
  11. A column must be declared as NOT NULL if it really is — thus you speed up table traversing a bit.
  12. If you usually retrieve rows in the same order like expr1, expr2, …, make ALTER TABLE … ORDER BY expr1, expr2, … to optimize the table.
  13. Don’t use PHP loop to fetch rows from database one by one just because you can — use IN instead, e.g. SELECT * FROM `table` WHERE `id` IN (1,7,13,42);
  14. Use column default value, and insert only those values that differs from the default. This reduces the query parsing time.
  15. Use INSERT DELAYED or INSERT LOW_PRIORITY (for MyISAM) to write to your change log table. Also, if it’s MyISAM, you can add DELAY_KEY_WRITE=1 option — this makes index updates faster because they are not flushed to disk until the table is closed.
  16. Think of storing users sessions data (or any other non-critical data) in MEMORY table — it’s very fast.
  17. For your web application, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database.
  18. If you have to store big amounts of textual data, consider using BLOB column to contain compressed data (MySQL’s COMPRESS() seems to be slow, so gzipping at PHP side may help) and decompressing the contents at application server side. Anyway, it must be benchmarked.
  19. If you often need to calculate COUNT or SUM based on information from a lot of rows (articles rating, poll votes, user registrations count, etc.), it makes sense to create a separate table and update the counter in real time, which is much faster. If you need to collect statistics from huge log tables, take advantage of using a summary table instead of scanning the entire log table every time.
  20. Don’t use REPLACE (which is DELETE+INSERT and wastes ids): use INSERT … ON DUPLICATE KEY UPDATE instead (i.e. it’s INSERT + UPDATE if conflict takes place). The same technique can be used when you need first make a SELECT to find out if data is already in database, and then run either INSERT or UPDATE. Why to choose yourself — rely on database side.
  21. Tune MySQL caching: allocate enough memory for the buffer (e.g. SET GLOBAL query_cache_size = 1000000) and define query_cache_min_res_unit depending on average query resultset size.
  22. Divide complex queries into several simpler ones — they have more chances to be cached, so will be quicker.
  23. Group several similar INSERTs in one long INSERT with multiple VALUES lists to insert several rows at a time: quiry will be quicker due to fact that connection + sending + parsing a query takes 5-7 times of actual data insertion (depending on row size). If that is not possible, use START TRANSACTION and COMMIT, if your database is InnoDB, otherwise use LOCK TABLES — this benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed; in this case unlock your tables each 1000 rows or so to allow other threads access to the table.
  24. When loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times faster.
  25. Log slow queries on your dev/beta environment and investigate them. This way you can catch queries which execution time is high, those that don’t use indexes, and also — slow administrative statements (like OPTIMIZE TABLE and ANALYZE TABLE)
  26. Tune your database server parameters: for example, increase buffers size.
  27. If you have lots of DELETEs in your application, or updates of dynamic format rows (if you have VARCHAR, BLOB or TEXT column, the row has dynamic format) of your MyISAM table to a longer total length (which may split the row), schedule running OPTIMIZE TABLE query every weekend by crond. Thus you make the defragmentation, which means more speed of queries. If you don’t use replication, add LOCAL keyword to make it faster.
  28. Don’t use ORDER BY RAND() to fetch several random rows. Fetch 10-20 entries (last by time added or ID) and make array_random() on PHP side. There are also other solutions.
  29. Consider avoiding using of HAVING clause — it’s rather slow.
  30. In most cases, a DISTINCT clause can be considered as a special case of GROUP BY; so the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Also, if you use DISTINCT, try to use LIMIT (MySQL stops as soon as it finds row_count unique rows) and avoid ORDER BY (it requires a temporary table in many cases).
  31. When I read “Building scalable web sites”, I found that it worth sometimes to de-normalise some tables (Flickr does this), i.e. duplicate some data in several tables to avoid JOINs which are expensive. You can support data integrity with foreign keys or triggers.
  32. If you want to test a specific MySQL function or expression, use BENCHMARK function to do that.


  1. Separation of Database and zabbix services
    If you have a large environment, it pays off to run your database on a different server than your zabbix service and webinterface.
    I have 2 servers for our DM master node. 1 for the zabbix_server + webinterface, 1 running mysql.
    If your environment is large, do NOT run either on a VM!
  2. Mysql tuning
    Use mysql tuning scripts to optimize your database settings, like memory usage, buffer sizes, etc. For example:
    http://rackerhacker.com/mysqltuner/ <– I only use this one, its good.
    They will all bitch about query optimization, allas you need to ignore those
  3. Seperate innodb file per table
    This option in the my.cf is the only way you are able to use the mysqlcheck -o (optimize / shrink) functionality.
    If you use the default InnoDB setting (1 file) you are not able to clear out empty record space like PostgreSQL does in its housekeeping. There is a thread on the forum about this.
    Another option with this is that you could move/link the history/trends table file to a different set of disk spindles they don’t interfere with each other when a full table scan is needed (often if it’s done by the webinterface).
  4. Dump/restore db
    In relation to the previous point, if you are using the ‘one-file-for-all-dbs-tables’ default setting for InnoDB, you can only clear out all empty records by dumping all databases, remove the innodb data files and restore all databases in a fresh mysql data instance.
    You could try this during some maintenance period and see how many gig’s this will save, i think lots
  5. Other database system?
    You could try a different database.
    In my personal experience PostgreSQL performs the same at best with a lot more maintenance requirement in the long term. I have switched mysql->posgresql->mysql and will stay with mysql5. I’ve had postgresql guru’s look at the configs and systems, no go. The main issue is table indexes for the history, trends and events tables. The only switch i am willing to explore is Oracle with the help of a team of Oracle DBA’s and custom database setups to tacke the index issue.

– This is what i could think of in 5 min, if i think of more options or tips will will try to add it to this post.
– Yes the post is probably full of typo’s, deal with it
– This post is based on 1.4.x, but generally usable for all versions

Some Question:
-> Why you say no VMWare for mysql database ?
-> What type records do manualy clean in database ?? (i’ve seen sometimes message, in web interface, like ‘error : host xxx doesn’t exist’)
-> I’ve try mySqlTunner … Great : but it’s not very easy to find good tunning of my.cnf

 >>  MySQLTuner 0.9.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Unable to check for the latest MySQLTuner version
[OK] Currently running supported MySQL version 5.0.26-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2K (Tables: 8)
[--] Data in InnoDB tables: 16G (Tables: 66)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6h 13m 32s (26M q [1K qps], 11K conn, TX: 407M, RX: 3B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 18.2M per thread and 2.3G global
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 4.1G (206% of installed RAM) [OK] Slow queries: 0% (5/26M)
[OK] Highest usage of available connections: 28% (29/100)
[OK] Key buffer size / total MyISAM indexes: 500.0M/74.0K
[!!] Key buffer hit rate: -1224.5% [!!] Query cache efficiency: 15.9%
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0%
[!!] Joins performed without indexes: 3914 [!!] Temporary tables created on disk: 48%
[OK] Thread cache hit rate: 99%
[OK] Table cache hit rate: 97%
[OK] Open file limit used: 2%
[OK] Table locks acquired immediately: 100%
[!!] InnoDB data size / buffer pool: 17.0G/1.5G

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage exceeds your installed memory ***
  *** Add more RAM before increasing any MySQL buffer variables  ***
    query_cache_limit (> 16M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 100M)
    max_heap_table_size (> 99M)
    innodb_buffer_pool_size (>= 16G)

-> I’m affraid by

[!!] Key buffer hit rate: -1224.5%

But don’t know why …
-> i’m increase query_cache_limit, join_buffer_size, tmp_table_size and max_heap_table_size but it wan’t more and more memory … i’ve only 2 Gb ….
-> And “innodb_buffer_pool_size (>= 16G)” seem to me very strange … RAM = database size ??!!!

You need to run mysql for at least 2 days straigt in order to get decent values from mysqltuner. (thus the -12k values).
Also, the inno_db_cache thiny: use 50%-75% of your total mem for this.

This is mostly trial and error.
Don’t enlarge the values too fast, always document every step you take so you can roll back.

Below are the tuned values i’m currently using (as partly suggested by mysqltuner). This is for a 4G mem server.

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 300table_cache = 128
tmp_table_size = 256M
max_heap_table_size = 256M
join_buffer_size = 5Minnodb_buffer_pool_size = 2G# This is too high, need to lower next reboot
query_cache_limit = 256M
query_cache_size = 512M

Hope this helps

MySQL performance tweaks

I have had a few conversations in other threads about doing some fine tuning and performance tweaks to MySQL as I have reason to believe that MySQL is what is causing some slowdowns on my systems. I have done some implementations and experimenting on the suggestions given in the forums and I have also been looking into other possibilities and researching them. As I am just now learning about the inner workings of Zabbix, I would appreciate it if anyone could tell me if these tweaks help, hinder, or if I just did a serious no-no. I am just listing some of the changes I have made based off of what I have found as well as some items that keep popping up that I am still trying to get more information on.I have not committed any changes to my production server, just my test Zabbix server so if I did just really screw something up…meh who cares? Thats what the test system is there for, right?

While I know about 3x more on MySQL then I did a few weeks ago, I am still in the starting stages of learning. I would really appreciate any comments, suggestions, and/or further information on MySQL improvements.

I went to a few sites and I saw a lot of similar suggestions. These are just the few sites that I bookmarked.

I did try to modify a few settings based on my server memory. I wanted to ensure that I had plenty left over for the kernel and other programs while allowing MySQL to use a size able amount as well. My test server has 1.2GB of memory

key_buffer = 256 (1/5 of my servers memory)
query_cache_size = 256MB (1/5 of my servers memory)
query_cache_limit = 4MB
table_cache = 512

The next two were commented out in my my.conf and I have not done enough research yet to figure out if it they are worth turning on
#sort_buffer_size = 32M
#myisam_sort_buffer_size = 32M

These next three are not in my my.conf. I want to find out more before I add them.
tmp_table_size = 64MB
delay_key_write = 1
wait_timeout = 60

I commented the next line out as I have a cron process that backs up the MySQL database nightly and copies it to a different server. Therefore I believe that this is unneeded. One website said this was a noticeable gain on large databases that are frequently updating. I have not found a site that can show any type of graph or proof on this but it seems to have been mentioned on several sites. There is also a nifty warning in my.conf that I followed and commented out the expire_logs_days and max_binlog_size
#log-bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
#expire_logs_days = 10
#max_binlog_size = 100M

I have read several places that state I can safely turn off InnoDB and see a speed increase (such as link 2 states) but in this thread (http://www.zabbix.com/forum/showthread.php?t=250 ) from 2005 Alexei states that it increases parallelism and does not lock tables. From what I understand, locking tables can do bad things if a process freezes. So I did some more research and found this ( http://www.zabbix.com/forum/showthread.php?t=7771 ). I am still reading through those links (its a bit of information to go through ) but I have been playing with the suggested changes.

The query_cache stuff suggested I had already done up there ^ .

I also found this thread ( http://www.zabbix.com/forum/showthread.php?t=6316 ) and followed a few suggestions
max_allowed_packet = 128M (was set to 16M)
innodb_buffer_pool_size=350M (From the other thread I have it set to 350M which is about 1/4 of my physical memory but in this thread alj recommended that 70% (~896M) of my physical memory be put here! That sounds way high from other places I found; can someone verify?)
thread_cache_size = 80

I did all of these edits to my.conf, so again if I screwed something up or a change should be placed elsewhere please let me know. Any comments / criticism / suggestions are welcome but instead of posting “don’t do that. its stupid” I would really appreciate an explanation or a link with details that I can gather knowledge from.

Thanks guys! I really do appreciate any help!

My final my.conf looks like this:

# For reference the Zabbix server has 1.2 GB of ram
# The MySQL database server configuration file.
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

# * Basic Settings
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            =
# * Fine Tuning
key_buffer              = 256M
max_allowed_packet      = 128M
thread_stack            = 128K
thread_cache_size       = 80
#max_connections        = 100
table_cache            = 512
#thread_concurrency     = 10
# * Query Cache Configuration
query_cache_type        = 1
query_cache_limit       = 4M
query_cache_size        = 256M
# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql/mysql.log
# Error logging goes to syslog. This is a Debian improvement :)
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
# The following can be used as easy to replay backup logs or for replication.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
#expire_logs_days        = 10
#max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
# * BerkeleyDB
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.

## Produce informations about wrong informations in tables.


# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

max_allowed_packet      = 16M

#no-auto-rehash # faster start of mysql but no tab completition

key_buffer              = 256M

# * NDB Cluster
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
# ndb-connectstring=

# * IMPORTANT: Additional settings that can override those from this file!
!includedir /etc/mysql/conf.d/


OK I found out some more 
information.First off, I decided to look at what was turned on/off. I 
ran a 'mysqladmin -uroot -p variables'
Variable_name		       |Value
auto_increment_increment       |1
auto_increment_offset	       |1
automatic_sp_privileges	       |ON
back_log		       |50
basedir			       |/usr/
binlog_cache_size	       |32768
bulk_insert_buffer_size	       |8388608
character_set_client	       |latin1
character_set_connection       |latin1
character_set_database	       |latin1
character_set_filesystem       |binary
character_set_results	       |latin1
character_set_server	       |latin1
character_set_system	       |utf8
character_sets_dir	       |/usr/share/mysql/charsets/
collation_connection	       |latin1_swedish_ci
collation_database	       |latin1_swedish_ci
collation_server	       |latin1_swedish_ci
completion_type		       |0
concurrent_insert	       |1
connect_timeout		       |5
datadir			       |/var/lib/mysql/
date_format		       |%Y-%m-%d
datetime_format		       |%Y-%m-%d %H:%i:%s
default_week_format	       |0
delay_key_write		       |ON
delayed_insert_limit	       |100
delayed_insert_timeout	       |300
delayed_queue_size	       |1000
div_precision_increment	       |4
engine_condition_pushdown      |OFF
expire_logs_days	       |0
flush			       |OFF
flush_time		       |0
ft_boolean_syntax	       |+ -><()~*:""&|
ft_max_word_len		       |84
ft_min_word_len		       |4
ft_query_expansion_limit       |20
ft_stopword_file	       |(built-in)
group_concat_max_len	       |1024
have_archive		       |YES
have_bdb		       |NO
have_blackhole_engine	       |YES
have_compress		       |YES
have_crypt		       |YES
have_csv		       |YES
have_dynamic_loading	       |YES
have_example_engine	       |NO
have_federated_engine	       |YES
have_geometry		       |YES
have_innodb		       |YES
have_isam		       |NO
have_merge_engine	       |YES
have_ndbcluster		       |DISABLED
have_openssl		       |DISABLED
have_ssl		       |DISABLED
have_query_cache	       |YES
have_raid		       |NO
have_rtree_keys		       |YES
have_symlink		       |YES
hostname		       |AS1
init_connect		       |
init_file		       |
init_slave		       |
innodb_autoextend_increment    |8
innodb_buffer_pool_awe_mem_mb  |0
innodb_buffer_pool_size	       |367001600
innodb_checksums	       |OFF
innodb_commit_concurrency      |0
innodb_concurrency_tickets     |500
innodb_data_file_path	       |ibdata1:10M:autoextend
innodb_data_home_dir	       |
innodb_doublewrite	       |OFF
innodb_fast_shutdown	       |1
innodb_file_io_threads	       |4
innodb_file_per_table	       |ON
innodb_flush_log_at_trx_commit |0
innodb_flush_method	       |
innodb_force_recovery	       |0
innodb_lock_wait_timeout       |50
innodb_locks_unsafe_for_binlog |OFF
innodb_log_arch_dir	       |
innodb_log_archive	       |OFF
innodb_log_buffer_size	       |8388608
innodb_log_file_size	       |5242880
innodb_log_files_in_group      |2
innodb_log_group_home_dir      |./
innodb_max_dirty_pages_pct     |90
innodb_max_purge_lag	       |0
innodb_mirrored_log_groups     |1
innodb_open_files	       |300
innodb_rollback_on_timeout     |OFF
innodb_support_xa	       |OFF
innodb_sync_spin_loops	       |20
innodb_table_locks	       |ON
innodb_thread_concurrency      |8
innodb_thread_sleep_delay      |10000
interactive_timeout	       |28800
join_buffer_size	       |131072
key_buffer_size		       |268435456
key_cache_age_threshold	       |300
key_cache_block_size	       |1024
key_cache_division_limit       |100
language		       |/usr/share/mysql/english/
large_files_support	       |ON
large_page_size		       |0
large_pages		       |OFF
lc_time_names		       |en_US
license			       |GPL
local_infile		       |ON
locked_in_memory	       |OFF
log			       |OFF
log_bin			       |OFF
log_error		       |
log_queries_not_using_indexes  |OFF
log_slave_updates	       |OFF
log_slow_queries	       |OFF
log_warnings		       |1
long_query_time		       |10
low_priority_updates	       |OFF
lower_case_file_system	       |OFF
lower_case_table_names	       |0
max_allowed_packet	       |134217728
max_binlog_cache_size	       |4294967295
max_binlog_size		       |1073741824
max_connect_errors	       |10
max_connections		       |100
max_delayed_threads	       |20
max_error_count		       |64
max_heap_table_size	       |16777216
max_insert_delayed_threads     |20
max_join_size		       |18446744073709551615
max_length_for_sort_data       |1024
max_prepared_stmt_count	       |16382
max_relay_log_size	       |0
max_seeks_for_key	       |4294967295
max_sort_length		       |1024
max_sp_recursion_depth	       |0
max_tmp_tables		       |32
max_user_connections	       |0
max_write_lock_count	       |4294967295
multi_range_count	       |256
myisam_data_pointer_size       |6
myisam_max_sort_file_size      |2147483647
myisam_recover_options	       |OFF
myisam_repair_threads	       |1
myisam_sort_buffer_size	       |8388608
myisam_stats_method	       |nulls_unequal
ndb_autoincrement_prefetch_sz  |32
ndb_force_send		       |ON
ndb_use_exact_count	       |ON
ndb_use_transactions	       |ON
ndb_cache_check_time	       |0
ndb_connectstring	       |
net_buffer_length	       |16384
net_read_timeout	       |30
net_retry_count		       |10
net_write_timeout	       |60
new			       |OFF
old_passwords		       |OFF
open_files_limit	       |1134
optimizer_prune_level	       |1
optimizer_search_depth	       |62
pid_file		       |/var/run/mysqld/mysqld.pid
port			       |3306
preload_buffer_size	       |32768
profiling		       |OFF
profiling_history_size	       |15
protocol_version	       |10
query_alloc_block_size	       |8192
query_cache_limit	       |4194304
query_cache_min_res_unit       |4096
query_cache_size	       |268435456
query_cache_type	       |ON
query_cache_wlock_invalidate   |OFF
query_prealloc_size	       |8192
range_alloc_block_size	       |2048
read_buffer_size	       |131072
read_only		       |OFF
read_rnd_buffer_size	       |262144
relay_log_purge		       |ON
relay_log_space_limit	       |0
rpl_recovery_rank	       |0
secure_auth		       |OFF
secure_file_priv	       |
server_id		       |0
skip_external_locking	       |ON
skip_networking		       |OFF
skip_show_database	       |OFF
slave_compressed_protocol      |OFF
slave_load_tmpdir	       |/tmp/
slave_net_timeout	       |3600
slave_skip_errors	       |OFF
slave_transaction_retries      |10
slow_launch_time	       |2
socket			       |/var/run/mysqld/mysqld.sock
sort_buffer_size	       |2097144
sql_big_selects		       |ON
sql_mode		       |
sql_notes		       |ON
sql_warnings		       |OFF
ssl_ca			       |
ssl_capath		       |
ssl_cert		       |
ssl_cipher		       |
ssl_key			       |
storage_engine		       |MyISAM
sync_binlog		       |0
sync_frm		       |ON
system_time_zone	       |CDT
table_cache		       |512
table_lock_wait_timeout	       |50
table_type		       |MyISAM
thread_cache_size	       |80
thread_stack		       |131072
time_format		       |%H:%i:%s
time_zone		       |SYSTEM
timed_mutexes		       |OFF
tmp_table_size		       |33554432
tmpdir			       |/tmp
transaction_alloc_block_size   |8192
transaction_prealloc_size      |4096
tx_isolation		       |REPEATABLE-READ
updatable_views_with_limit     |YES
version			       |5.0.45-Debian_1
version_comment		       |Debian etch distribution
version_compile_machine	       |i486
version_compile_os	       |pc-linux-gnu
wait_timeout		       |28800

If I read this statement right, then apprently the my.conf did not enable InnoDB.

storage_engine		 |MyISAM

Also, from what I have found using InnoDB probably isn’t the best thing unless you know you are going to have a huge database. This website hasn’t really helped convince me that InnoDB is the way to go ( http://www.daniweb.com/forums/thread40911.html Started a year ago, but last updated less then a day ago so the posting is still active).

Another thing is wait_timeout. The second article I posted in the previous post suggested 60. It looks like mine is currently set to 28800. Thats quite the difference.

delay_key_write: According to this website ( http://www.petefreitag.com/item/441.cfm ) “the option makes index updates faster because they are not flushed to disk until the table is closed.”
Please correct me if I am wrong but this sounds very dangerous to me. First on small tables, I doubt it would do much at all. Second on large tables, if the database crashed, server died, network hiccup, ect then data could be lost and / or corrupted. Unless someone can give me a better reason, I think I am just going to go with the default (apparently its ON) and leave this one alone and just as it is.

Last item (for now anyway ) is the sort_buffer_size. I have found several sites that claim something along lines of ‘if you do a lot of sorting on your tables or if you know you are going to be sorting a lot, then increase this somewhere between 10MB and 256MB. Otherwise, don’t sweat it.’ Could someone tell me if Zabbix does enough big sorts that increasing this would be good?

I will post back if I find more info.
Thanks guys!


In my work MySQL plays a big part of the dynamic web data chain, especially for many PHP based sites (like Drupal). Here are tips I’ve collected along the way and of course YMMV (your mileage may vary).

MySQL Tuning notes

  • Disable or remove any extensions (or modules) not being used.
  • Separate the database onto its own server (Master), and try to have a (Slave) copy as a backup or for redundancy.
  • Master-Master configurations are not easy.
  • Use EXPLAIN to dig into queries and find where optimizations can be made for performance.
  • Profile MySQL during development using the SET PROFILING flag. Enable profiling, query information_schema table to review results. Disable profiling when done.
  • Enable and tune the Query cache.
  • Enable Table cache.
  • Enable (or raise) Key buffer.
  • Enable Slow Query Log to find bottleneck queries.
  • Use MyISAM table types for fast reads, but stick with InnoDB for transactional data.

Tools, Utilities, and Resources

mysqltuner – http://blog.mysqltuner.com

Useful Commands



Config changes

Here is some examples of tuned MySQL settings for a Drupal site (taken from random places). InnoDB table types are recommended over MyISAM. Consider looking at mysqlnd when using PHP 5.3 on your web server(s); it’s a replacement for libmysql.
key_buffer = 48M
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M
table_cache = 750
#table_cache_size = 1000 # Newer MySQL can handle 1000+

# Separate disk spindle
datadir = /data/mysql

innodb-buffer_pool_size = 256M

long_query_time = 2

thread_stack = 128K
thread_cache_size = 64
max_allowed_packet = 16M
max_connections = 100
max_heap_table_size = 256M
tmp_table_size = 256M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M

long_query_time = 5
thread_stack = 128K
thread_cache_size = 64
#max_connections = 100 #TBD


mysqlreport – Perl shell script that displays stats.

Db tuning – shell script that reads variables from MySQL.

mtop – like top but for MySQL. Real time monitoring. Shows slow queries and locks.

mytop – also like top but for MySQL. Real time monitoring. Shows slow queries and locks.

`mysqladmin processlist` or `mysqladmin -v processlist`









Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )


Conectando a %s