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 |
  • I'm not a mysql expert, but 3 seconds to examine 500 rows seems very excessive, whats the IO like on your system? Aug 15 '11 at 12:06
  • There is a lot of queries simultaniously. The system is highload. So I want to use more resources of server. I/O graph not show any peaks or overload.
    – Xupypr MV
    Aug 15 '11 at 12:10
  • But my database size is less 5 Gb. I think it's nice to cache all the data in memory but I don't now that is possible. Or may be it's not good appoach?
    – Xupypr MV
    Aug 15 '11 at 12:29
  • 25%? Are you on a quad-core machine? It sounds like it's hitting one core 100%. You're not in single-threaded mode, are you?
    – Richard
    Aug 15 '11 at 13:29
  • @Richard i have no key --one-thread in my config. Also I have thread_concurrency = 8 option.
    – Xupypr MV
    Aug 15 '11 at 13:57

Here is your original query from the slow log

SELECT COUNT(inboxentities.id) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

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

  • 'active' is true or false (2 values). An index of just that field is lopsided.
  • 'deleted' is true or false (2 values). An index of just that field is lopsided.
  • 'notification' is true or false (2 values). An index of just that field is lopsided.
  • 'to_' is a values worth indexing and narrowing down.

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:

SELECT COUNT(to_) FROM inboxentities
WHERE (active=true)
AND (deleted=false)
AND ((to_ = '44219ca4-a657-4909-b30d-a7ba0ed8e4b0'))
AND (notification=true);

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:

SUBSTR(' KMG',IF(PowerOf1024<0,0,
recommended_key_buffer_size FROM
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the right size InnoDB Buffer Pool for your given data set

SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;


  • (SELECT 0 PowerOf1024) generates the answer in Bytes
  • (SELECT 1 PowerOf1024) generates the answer in KB
  • (SELECT 2 PowerOf1024) generates the answer in MB
  • (SELECT 3 PowerOf1024) generates the answer in GB

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:

wget mysqltuner.pl

The output from it is something like this:

$ perl mysqltuner.pl

 >>  MySQLTuner 1.2.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
Please enter your MySQL administrative login: username
Please enter your MySQL administrative password: (password hidden)

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7G (Tables: 6)
[--] Data in InnoDB tables: 2G (Tables: 382)
[!!] Total fragmented tables: 84

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 20h 24m 6s (54M q [49.462 qps], 555K conn, TX: 287B, RX: 95B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 9.3G global + 48.2M per thread (1250 max threads)
[!!] Maximum possible memory usage: 68.2G (291% of installed RAM)
[OK] Slow queries: 0% (647/54M)
[OK] Highest usage of available connections: 5% (69/1250)
[OK] Key buffer size / total MyISAM indexes: 256.0M/19.4M
[OK] Key buffer hit rate: 100.0% (51M cached / 10 reads)
[OK] Query cache efficiency: 89.3% (47M cached / 52M selects)
[!!] Query cache prunes per day: 60670
[OK] Sorts requiring temporary tables: 0% (239 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 47% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (103 created / 555K connections)
[OK] Table cache hit rate: 21% (722 open / 3K opened)
[OK] Open file limit used: 0% (70/32K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[OK] InnoDB data size / buffer pool: 2.4G/8.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    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 is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 1G) [see warning above]
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
  • Sounds like a good idea. I'll try it tomorrow.
    – Xupypr MV
    Aug 15 '11 at 18:19
  • But it is not only one type of requests that are slow. It will be not good to resolve them all (some of them can't be optimized such way, such as request that get large amount of data or doung full text search). So, can you answer my original question. Can I use all my free resources for MySQL? (or tell me that I'm wrong, and I will not get more speed using this approach).
    – Xupypr MV
    Aug 15 '11 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.