Archive for category MySQL 5.7

MySQL 5.x – How to capture the number of inserts in a MySQL database

MySQL 5.x – How to capture the number of inserts in a MySQL database

I was asked to get the number of DML on a MySQL database recently. However, the performance schema was not installed so I couldn’t query from it. I found a status variable called innodb_rows_inserted.

mysql> show status like 'Innodb_rows_inserted'; select sleep(60) ; show status like 'Innodb_rows_inserted' ;
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| Innodb_rows_inserted | 576654182 |
+----------------------+-----------+
1 row in set (0.00 sec)

+-----------+
| sleep(60) |
+-----------+
| 0 |
+-----------+
1 row in set (1 min 0.00 sec)

+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| Innodb_rows_inserted | 576684821 |
+----------------------+-----------+
1 row in set (0.01 sec)

To get the value/second, simply perform the calculation below;

(576684821-576654182)/60 = ~510 inserts per second

Obviously, the number above is only for inserts. You can also modify the command above to include the following;

  • Innodb_rows_inserted
  • Innodb_rows_read
  • Innodb_rows_updated
  • Innodb_rows_deleted

Hope this helps!

Regards,
Wei Shan

,

Leave a comment

MySQL 5.7 – Index Condition Pushdown Optimization Bug

MySQL 5.7 – Index Condition Pushdown Optimisation Bug

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. It uses the indexed columns to determine if they should retrieve the row instead of retrieving the entire table then filtering off the unnecessary rows.

Disable ICP if you are running a query with ORDER BY DESC against partitioned tables. It slows down the query considerably. You will definitely hit this bug if you are running zabbix server on MySQL database.

To disable ICP, use the following:

SET optimizer_switch='index_condition_pushdown=off';

Hope this helps!

Regards,
Wei Shan

References:

,

Leave a comment