MariaDB – How to Get Query Time in MariaDB

mariadbMySQLperformancetuning

I'm using MariaDB 10.1. How to get actual query time for all of executed query? It seems that SHOW PROFILES; was deprecated. Is there a better solution for database tuning?

Edit: It's deprecated only in MySQL 5.6.7.

Best Answer

Set long_query_time=0 and turn on the slowlog to a FILE (in my.cnf, and restart). Then the slowlog will contain a variety of info about every command executed, including the query time.

You might also want to run pt-query-digest to summarize the slow log. This will include min/max/avg/etc for each 'different' query.

Related Solutions

Mariadb – Connection string for MariaDB using ODBC

Although this is late answer, but MariaDB have that documented here

SQLWCHAR *ConnStr= L"Driver={MariaDB ODBC 2.0 Driver};Server=localhost;UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306";

MySQL 100% CPU Usage – Reading from a 500,000 Rows Table

How much RAM? Many of the entries in my.cnf look "too big" unless you have a lot of RAM.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

InnoDB would be faster because the data is clustered with the PRIMARY KEY.

What is happening... Each run of that query is scanning half the index, starting with IPLONG=ip_from and going to the end of the index. Meanwhile, it is reaching over to the data (since it is separate in MyISAM) for the other columns.

Are the ip_from..ip_to pairs non-overlapping? If so, you could check one row, then quit. If they are overlapping, there is no hope.

The LIMIT 1 will not be sufficient without ORDER BY ip_from.

You realize that the table cannot handle IPv6?

Make sure that country_code is CHARACTER SET ascii. Don't include country_name in the same table; build a lookup (normalization) table mapping country_code to country_name.

Best Answer

Related Solutions

Mariadb – Connection string for MariaDB using ODBC

MySQL 100% CPU Usage – Reading from a 500,000 Rows Table

Related Question