alxolr

quality articles on software engineering topics

Mysql swiss army knife queries

Mysql swiss-army knife queries

Safe mode

SET SQL_SAFE_UPDATES = 0;

Always forget this one. When you want to update multiple rows, it's requested to disable the safe mode.

Processes

Often our metrics show that the database is passing through a severe burnout where the CPU is at 100%. A way to peel into what exactly is happening is:

SHOW FULL PROCESSLIST;
9    root    172.17.0.1:52296    sys Sleep   393     
10    root    172.17.0.1:52300        Sleep   393     
11    root    172.17.0.1:52304    sys Query   5   executing   "/*.. */"
16    root    172.17.0.1:52358    sys Query   0   starting    /* ...*/ show full processlist

To kill the problematic process, you can use:

KILL QUERY 11;

Performance

If you are into stored procedures and MySQL functions, the benchmark can help you identify how fast they are.

SELECT BENCHMARK(1000000000, CONCAT("Hello", "World"));

Sooner or later, you will run explain to see what MySQL thinks and what plans he has about your query.

EXPLAIN SELECT * FROM `table`;

If you stumbled into zombi transactions, this one would help you find them.

SHOW ENGINE INNODB STATUS;

Data size

Proper query to see the index and data size per table.

SELECT
    TABLE_NAME as `Table`,
    ROUND(DATA_LENGTH / 1024 / 1024) as `Data (Mb)`,
    ROUND(INDEX_LENGTH / 1024 / 1024) as `Index (Mb)`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) as ` Total (Mb)`
FROM
    information_schema.TABLES t
WHERE
    TABLE_SCHEMA = 'db_name'
ORDER by
    (DATA_LENGTH + INDEX_LENGTH) DESC;

Similar to the previous one, but you need full rights to the database to access it.

SHOW TABLE STATUS FROM [dbname];

Structure

Use it to analyze the table structure, also clone/duplicate tables.

SHOW CREATE TABLE `table_name`;

I hope that this article was helpful. If you like it, please share it with your friends and leave a comment; I will gladly answer all the questions.
×