Mysql – Get history of queries on MariaDB

mariadbMySQL

I'm trying to get a table of recent slow queries on a MariaDB/MySQL server.

MariaDB [(none)]> SELECT TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,SQL_TEXT FROM performance_schema.events_statements_current;
+----------+--------------------------------------------------------------------------------------------------------------------+
| Duration | SQL_TEXT                                                                                                           |
+----------+--------------------------------------------------------------------------------------------------------------------+
| 0.000221 | SELECT TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration,SQL_TEXT FROM performance_schema.events_statements_current |
+----------+--------------------------------------------------------------------------------------------------------------------+

performance_schema.events_statements_current will only give me the most recent query, so my own. When I try to use performance_schema.events_statements_history_long I don't get much.

Empty set (0.00 sec)

Why does current works and not history_long?

Best Answer

My performance_schema.setup_consumers was not properly set.

By default performance_schema.events_statements_history_long is not enabled.

update setup_consumers set enabled="YES" where name="events_statements_history";
update setup_consumers set enabled="YES" where name="events_statements_history_long";

Related Solutions

MySQL – Why Queries with Subqueries are Faster than Single Query

I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.

Percona and MariaDB – Ignoring NOT NULL on Table Fields

This is taken from the MySQL documentation:

Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

... If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug #6295, Bug#11744964). ...

Basically to change this behavior can do the following:

MariaDB> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
MariaDB> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
          @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION                                                                                               |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table      | Create Table                                                               |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (                                                |
|            |        `id` INT(11) UNSIGNED NOT NULL,                                     |
|            |        `field` VARCHAR(20) DEFAULT NULL,                                   |
|            |        `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=latin1                                     |
+------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
   VALUES
   (1, 'Value', NULL);
ERROR 1048 (23000): Column 'field_with_default_value' cannot be null

I hope this a light on the road.

Related Question