I have a MySQL database and some of my queries became slow. Query time is not stable. Most of the queries are fast, but some of them (may be that reads and returns less data) take long time.
I know that best practices is add indexes or refactor code, but i'm already added indexes and I don't want to refactor code (at least while I have other variants).
I have 8 Gb free memory and CPU is loaded only to 25% at peak. So I want to use all my resources.
I try to tune MySQL configuration but I havn't experience in such tuning, so i increase productivity no so much that I want. Here is an example:
# Query_time: 3.019647 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 504
SET timestamp=1313380874;
SELECT COUNT(inboxentities.id) FROM inboxentities WHERE (active=true)AND(deleted=false)AND((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))AND(notification=true);
P. S. inboxentities has 500.000 records. I have index1 (idx_to) : to_ and index2 (idx_complex): deleted,notification,active,to_
Here is result of explain select:
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | inboxentities | ref | idx_status,idx_statusToname,idx_to,idx_complex | idx_to | 768 | const | 286 | Using where |
+----+-------------+---------------+------+------------------------------------------------+--------+---------+-------+------+-------------+
Best Answer
Here is your original query from the slow log
The most effective way to index for a query like this is to generate an index that covers as many of the fields in the WHERE clause as possible. An index produced with that concept in mind is called a covering index.
Look at the WHERE clause
Although you have idx_complex defined, there is one more element to the query causing the idx_complex index not be used: the COUNT(inboxentities.id) clause.
You are counting something in a table. The index has no reference point to the table except to_. The MySQL Query Optmizer would choose idx_to, the simplest index. To force the MySQL Query Optimizer to choose the covering index you want (idx_complex) just COUNT from the index rather than the table. My suggestion is to change the query slightly:
Give it a Try !!!
UPDATE 2011-08-15 15:16 EDT
Some have seen marginal-to-significant performance increases scaling up by changing my.cnf to satisfy Storage Engine performance needs.
You need to set the MyISAM Key Cache and InnoDB Buffer Pool.
This will recommend the right size MyISAM Key Cache for your given data set:
This will recommend the right size InnoDB Buffer Pool for your given data set
BTW
If you go beyond this, email me.
Once you have these settings, make sure that the combined number for recommended_key_buffer_size and recommended_innodb_buffer_pool_size does not exceed 75% of installed RAM.
UPDATE 2011-08-15 15:35 EDT
You may want to run mysqltuner.pl on the server to let it tell you how to tweek memory useage for DB Connections. Settings governing memory usage per DB Connection include
These are usually multiplied by max_connections.
You can get mysqltuner.pl get downloading it as follows from the Linux command line:
The output from it is something like this: