DB CPU load

DB CPU load

The CPU load of the database server from one of our clients was real high. After moving a copy of their website to a development server they started investigating and optimize their website. These optimizations led to a reduction of the CPU load for the database server. But not enough. According to the slow queries log a simple SQL statement still took too much time to execute. Using the SQL commando EXPLAIN they discovered possibilities to set the missing index field in the database. This resulted in faster queries and – as you can see in the graph below after the second weekend – an enormous drop of CPU load of the database server. Faster queries, faster website and less load to the CPU of the database server.

 

Database CPU belasting

Database CPU load

Use EXPLAIN on Slow queries log 

  • The MySQL slow queries log will log all queries which took over define number of seconds to execute and a minimum set of rows to be examined.
  • Using the SQL statement EXPLAIN you can obtain information about how MySQL executes a statement.
    (MySQL documentation about EXPLAIN)
  • Use the slow queries log to pinpoint the queries that need to be optimized. Pay attention to the small SQL statements that run often. The huge SQL statements which run once a day by a cronjob is less interesting to investigate. 

    When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order. 

    With this information you can start optimizing tables. We have a good example in our Dutch blog Database-indexering: toch best handig (indien goed toegepast) by Gert-Jan 08-10-2010. It’s translated below for the purpose of this blog.

    How to improve a SQL statement

    Autumn 2010 shortly after work one of our database servers had a heavy load. 

    A simple SQL statement is executed a lot on a table containing 12 million rows.

    Almost 9sec for a simple query. Perhaps an index problem. According to the SQL statement I expect the KEY is set to field ArticleID. Since the statement took almost 9 seconds there might be no KEY set. The next SQL statement is to show the indexes of the table.

    According to the SQL statement above the table article_artist has KEY fields (ID and ArticleID). The next SQL statement is to give us more information about the simple SQL statement which causes all the trouble. 

    According the results we can conclude:

    • Using temporary -> MySQL will store the result of the SQL statement in a temporary table. 
      This will be stored on disk when there is not enough internal memory. Storing temporary tables on disk is real slooooow. Avoid storing on disk.
    • MySQL recognizes a possible_key (possible_keys = ArticleID) but it’s not using it (key = NULL). MySQL chooses not to use the key since not using it the SQL statement is faster. 9sec is not fast… I wonder how long it would take if MySQL did use the key.

     We have to dig further… yet another SQL statement.

     According the results we can conclude:

    • ArticleID is configured as a VARCHAR allowing 100 characters in that field. This can result in a huge index… probably the reason why MySQL didn’t use this field as key.

    Exploring the content of ArticleID we notice that it only contains numbers. This field can be a INT instead. Alter the field ArticleID to solve this and EXPLAIN the select statement again.

    JACKPOT! Changing field type VARCHAR to INT for field ArticleID did the trick. MySQL is using ArticleID as key and the amount of rows to check has been reduced from 589228 to 1. The “Using temporary” has been vanished too. This should result in a faster SQL statement. 

    A reduction from 8.9sec to 0.01sec in executing the simple SQL statement by correction of the fieldtype. 

    Final conclusion:

    • Although the index was set correct the SQL statement was slow due to a wrong chosen datatype. Choose your datatype wisely so MySQL can choose the right strategy performing the given SQL statement.
    • Using EXPLAIN is very important when writing your own SQL statements.
    • Start using Slow queries log when optimizing SQL statements

     

    Thanks to our Dutch blog Database-indexering: toch best handig (indien goed toegepast) by Gert-Jan 08-10-2010. It’s still a good example to show how to use EXPLAIN.

    Scan je eigen Magento shop op veiligheidslekken