Good afternoon, I made MySQL write to the log slow queries that are executed for more than 2 seconds.

Here is the content at the moment:

/usr/sbin/mysqld, Version: 5.5.35-0ubuntu0.13.10.2-log ((Ubuntu)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 140216 15:10:09 # User@Host: root[root] @ localhost [] # Query_time: 5.453749 Lock_time: 0.000000 Rows_sent: 64394 Rows_examined: 64394 use travian; SET timestamp=1392556209; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_odata`; # Time: 140216 15:10:15 # User@Host: root[root] @ localhost [] # Query_time: 4.653896 Lock_time: 0.000000 Rows_sent: 65006 Rows_examined: 65006 SET timestamp=1392556215; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_units`; # Time: 140216 15:10:26 # User@Host: root[root] @ localhost [] # Query_time: 11.289096 Lock_time: 0.000000 Rows_sent: 641601 Rows_examined: 641601 SET timestamp=1392556226; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_wdata`; # Time: 140216 15:13:41 # User@Host: root[root] @ localhost [] # Query_time: 5.317493 Lock_time: 0.000000 Rows_sent: 64394 Rows_examined: 64394 SET timestamp=1392556421; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_odata`; # Time: 140216 15:13:48 # User@Host: root[root] @ localhost [] # Query_time: 6.602040 Lock_time: 0.000000 Rows_sent: 65006 Rows_examined: 65006 SET timestamp=1392556428; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_units`; # Time: 140216 15:14:00 # User@Host: root[root] @ localhost [] # Query_time: 11.427378 Lock_time: 0.000000 Rows_sent: 641601 Rows_examined: 641601 SET timestamp=1392556440; SELECT /*!40001 SQL_NO_CACHE */ * FROM `tx5_wdata`;

What to do next? Please help me with tips on how to optimize these queries and understand what is written here ... Because the following entry is not entirely clear:

 SELECT /*!40001 SQL_NO_CACHE */ * FROM tx5_wdata;
  • So you need to write the request itself in the journal, and here only the metadata. - Opalosolo
  • And how to write the request itself in the journal? - spoilt
  • one
    @ eprivalov1, your requests go without where. Are you sure that you need all 65k records at once? - Yura Ivanov
  • 2
    @ ua6xh> SELECT / *! 40001 SQL_NO_CACHE * / * FROM tx5_wdata ; I'm stupid or is it still? - etki
  • @Fike, it's only /*!40001 SQL_NO_CACHE */ should be thrown out, and we get the text of the query. Tupit here, just, @ ua6xh. - Risto

1 answer 1

You have three slow queries.

 SELECT * FROM `tx5_odata`;

Retrieves and sends to the client the entire contents of the tx5_odata table - 65006 records.

 SELECT * FROM `tx5_units`;

Retrieves and sends to the client the entire contents of the tx5_odata table - 641601 records.

 SELECT * FROM `tx5_wdata`;

Retrieves and sends to the client the entire contents of the tx5_odata table - 64394 records.

The entry /*!40001 SQL_NO_CACHE */ simply reports that the result table does not fit into the cache (it is either disabled, or the result size is too large to fit into it).

In any case, this is not normal when you retrieve the entire contents of the table with a query. It is possible, it is possible to rework the logic of the program so that only a part of the database is extracted using the LIMIT and OFFSET keywords. The size of the resulting table will be orders of magnitude smaller and queries will be executed faster.