Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Page 7
The following example causes all queries that take more than 0.5 second to be captured in
/data1/mysql/mysqld-slow.log:
mysql> set global slow_query_log=1; // Turns on the logging
mysql> set global long_query_time=0.5 // 500 ms
mysql> show global variables like 'slow_query_log_file';
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_query_log_file | /data1/mysql/mysqld-slow.log |
+---------------------+------------------------------+
1 row in set (0.00 sec)
This is an example of an entry from the log file:
# Time: 091125 15:05:39
# User@Host: root[root] @ localhost []
# Query_time: 0.017187 Lock_time: 0.000078 Rows_sent: 6 Rows_examined: 22
SET timestamp=1259161539;
SELECT sub_age.age FROM sub_name, sub_age WHERE sub_name.name='Bill' AND
sub_name.sub_id=sub_age.sub_id;
Note: any changes to the long_query_time variable (including setting it for the first time – which is actually
changing it from the default of 10 seconds) do not effect active client connections to the MySQL Server –
those connections must be dropped and then reestablished. Note also that the variable is local to the MySQL
Server and so it needs to be set on each MySQL Server in the cluster separately.
A tool – mysqldumpslow – is provided to help browse the contents of the log file but you may need to view
the log file directly to get sufficient resolution for the times.
As a first step to understanding why a query might be taking too long, the EXPLAIN command can be used to
see some of the details as to how the MySQL Server executes the query (for example, what indexes – if any –
are used):
mysql> EXPLAIN SELECT * FROM sub_name, sub_age WHERE sub_name.sub_id=sub_age.sub_id
ORDER BY sub_age.age;
+----+-------------+----------+--------+----------------+---------+---------+--------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+----------------+---------+---------+--------------------------+------+----------------+
| 1 | SIMPLE | sub_age | ALL | PRIMARY,index2 | NULL | NULL | NULL | 8 | Using filesort |
| 1 | SIMPLE | sub_name | eq_ref | PRIMARY,index1 | PRIMARY | 4 | clusterdb.sub_age.sub_id | 1 | |
+----+-------------+----------+--------+----------------+---------+---------+--------------------------+------+----------------+
2 rows in set (0.07 sec)
The slow query log and the EXPLAIN are not unique to MySQL Cluster and they can be used with all MySQL
storage engines.
If the slow query is not sufficient, then the General Log can be enabled to generate a complete view of all
queries executed on a MySQL server. The general log is enabled with:
mysql> set global general_log=1; // Turns on the logging of all queries – only use for
a short period of time as it is expensive!
MySQL Cluster provides information on what’s happening within the data node – this data is exposed through
a virtual database called NDBINFO. As an example, NDBINFO presents information on the use of Disk Page
Buffer. The Disk Page Buffer is a cache on each data node which is used when using disk-based tables. Like
any cache, the higher the hit rate the better the performance. Tuning the size of this cache can have a
significant effect on your system. The data exposed for the Disk Page Buffer can be accessed directly from
the mysql command line in order to calculate the cache hit ratio: