1. Install MySQL instance
cd /mysqldatabase/product/binary/5.7.35
bin/mysqld --defaults-file=/mysqldatabase/TEST/admin/my.cnf --initialize --user=mysql
For more interesting posts on MySQL, please click on INDEX
2. Start MySQL instance
cd /mysqldatabase/product/binary/5.7.35/
bin/mysqld_safe --defaults-file=/mysqldatabase/TEST/admin/my.cnf --user=mysql &
3. Shutdown MySQL Instance
/mysqldatabase/product/binary/5.7.35/bin/mysqladmin -S/mysqldatabase/SLAVE/admin/mysql-slave.sock -uroot -pmysql shutdown
4. Check for Error logs locations
mysql> SELECT @@GLOBAL.log_error;
+------------------------------------+
| @@GLOBAL.log_error |
+------------------------------------+
| /mysqldatabase/TEST/logs/mysql.log |
+------------------------------------+
1 row in set (0.00 sec)
5. Check for different engines and the default engine :
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
6. Turn on General query log
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@GLOBAL.general_log;
+----------------------+
| @@GLOBAL.general_log |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
7. Check General Log location
mysql> show global variables like '%general%';
+------------------+-----------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------+
| general_log | ON |
| general_log_file | /mysqldatabase/TEST/data/test.log |
+------------------+-----------------------------------+
2 rows in set (0.01 sec)
8. Check General log events :
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from general_log;
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
| 2021-10-02 16:29:00.665183 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT @@GLOBAL.general_log |
| 2021-10-02 16:31:50.133097 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SHOW CREATE TABLE mysql.general_log |
| 2021-10-02 16:31:56.595550 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT @@GLOBAL.general_log |
| 2021-10-02 16:34:17.277467 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT @@GLOBAL.log_output |
| 2021-10-02 16:35:13.170662 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Quit | |
| 2021-10-02 16:37:45.812546 | [root] @ localhost [127.0.0.1] | 8 | 1 | Connect | root@localhost on using SSL/TLS |
| 2021-10-02 16:37:45.812973 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | select @@version_comment limit 1 |
| 2021-10-02 16:38:08.905390 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | show databases |
| 2021-10-02 16:39:13.453771 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | SELECT DATABASE() |
| 2021-10-02 16:39:13.454129 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Init DB | mysql |
| 2021-10-02 16:39:13.456316 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | show databases |
| 2021-10-02 16:39:13.456982 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | show tables |
| 2021-10-02 16:39:13.458135 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | columns_priv |
| 2021-10-02 16:39:13.458813 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | db |
| 2021-10-02 16:39:13.459559 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | engine_cost |
| 2021-10-02 16:39:13.459948 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | event |
| 2021-10-02 16:39:13.460566 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | func |
| 2021-10-02 16:39:13.460856 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | general_log |
| 2021-10-02 16:39:13.461123 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | gtid_executed |
| 2021-10-02 16:39:13.461601 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | help_category |
| 2021-10-02 16:39:13.463192 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | help_keyword |
| 2021-10-02 16:39:13.465049 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | help_relation |
| 2021-10-02 16:39:13.466871 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | help_topic |
| 2021-10-02 16:39:13.469245 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | innodb_index_stats |
| 2021-10-02 16:39:13.471671 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | innodb_table_stats |
| 2021-10-02 16:39:13.473234 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | ndb_binlog_index |
| 2021-10-02 16:39:13.475585 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | plugin |
| 2021-10-02 16:39:13.475836 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | proc |
| 2021-10-02 16:39:13.484287 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | procs_priv |
| 2021-10-02 16:39:13.485341 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | proxies_priv |
| 2021-10-02 16:39:13.486006 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | server_cost |
| 2021-10-02 16:39:13.486354 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | servers |
| 2021-10-02 16:39:13.486790 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | slave_master_info |
| 2021-10-02 16:39:13.488048 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | slave_relay_log_info |
| 2021-10-02 16:39:13.488771 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | slave_worker_info |
| 2021-10-02 16:39:13.489326 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | slow_log |
| 2021-10-02 16:39:13.492986 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | tables_priv |
| 2021-10-02 16:39:13.493782 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | time_zone |
| 2021-10-02 16:39:13.494244 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | time_zone_leap_second |
| 2021-10-02 16:39:13.498391 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | time_zone_name |
| 2021-10-02 16:39:13.498743 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | time_zone_transition |
| 2021-10-02 16:39:13.499186 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | time_zone_transition_type |
| 2021-10-02 16:39:13.499674 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Field List | user |
| 2021-10-02 16:39:15.909552 | root[root] @ localhost [127.0.0.1] | 8 | 1 | Query | select * from general_log |
+----------------------------+------------------------------------+-----------+-----------+--------------+-------------------------------------+
44 rows in set (0.00 sec)
9. Check slow query log location :
mysql> show global variables like '%slow%';
+---------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /mysqldatabase/TEST/data/test-slow.log |
+---------------------------+----------------------------------------+
5 rows in set (0.00 sec)
10. Check tablespaces that have free space
SELECT s.schema_name, table_name,
IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
HAVING data_free > 100
ORDER BY data_free DESC;
mysql> SELECT s.schema_name, table_name,
-> IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free
-> FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
-> WHERE s.schema_name = t.table_schema
-> GROUP BY s.schema_name, table_name
-> HAVING data_free > 100
-> ORDER BY data_free DESC;
+--------------------+-----------------+-----------+
| schema_name | table_name | data_free |
+--------------------+-----------------+-----------+
| information_schema | PARAMETERS | 8192.00 |
| information_schema | PLUGINS | 8192.00 |
| information_schema | OPTIMIZER_TRACE | 8192.00 |
| information_schema | EVENTS | 8192.00 |
| information_schema | TRIGGERS | 8192.00 |
| information_schema | ROUTINES | 8192.00 |
| information_schema | PARTITIONS | 8192.00 |
| information_schema | COLUMNS | 8192.00 |
| information_schema | VIEWS | 8192.00 |
| information_schema | PROCESSLIST | 8192.00 |
| mysql | help_topic | 4096.00 |
+--------------------+-----------------+-----------+
11 rows in set (0.16 sec)
11. Check for users and their authentication string in MySQL
mysql> select user,Host,authentication_string from mysql.user;
+---------------+----------------+-------------------------------------------+
| user | Host | authentication_string |
+---------------+----------------+-------------------------------------------+
| root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| TEST | % | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
| TEST | localhost | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
| TEST | 172.168.190.25 | *47A6B0EA08A36FAEBE4305B373FE37E3CF27C357 |
+---------------+----------------+-------------------------------------------+
6 rows in set (0.00 sec)
12. Identify current Data dictionary
SELECT @@datadir
13. Purging binary logs
mysql> show binary logs;
mysql> purge binary logs to 'mysql-bin.000496'; -- This will delete binary logs before 00496.
14. To Check Default Authentication Plugin
show variables like '%plugin%';
15. Check the available plugins and its status
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS;
16. Create an user without the default plugin
create user 'test'@'%' identified with caching_sha2_password by 'test';
17. To Check user with which you are logged in
Select user();
OR
Select current_user();
18. Drop Multiple user
DROP USER 'infraxpertzz'@'localhost', 'infralinux@%' ;
19. How to set MySQL Root password?
If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.
# mysqladmin -u root password YOURNEWPASSWORD
20. How to Change MySQL Root password?
If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.
mysqladmin -u root -p123456 password 'xyz123'
22. How to check MySQL Server is running?
To find out whether MySQL server is up and running, use the following command.
# mysqladmin -u root -p ping
Enter password:
mysqld is alive
23. How to Check which MySQL version I am running?
The following command shows MySQL version along with the current running status .
# mysqladmin -u root -p version Enter password: mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.5.28 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 days 14 min 45 sec Threads: 2 Questions: 36002 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
24. How to Find out current Status of MySQL server?
To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.
# mysqladmin -u root -ptmppassword status
Enter password:
Uptime: 606704 Threads: 2 Questions: 36003 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.059
25. How to check status of all MySQL Server Variable’s and value’s?
To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.
# mysqladmin -u root -p extended-status
Enter password:
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 3 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 6400357 |
| Bytes_sent | 2610105 |
| Com_admin_commands | 3 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
+------------------------------------------+-------------+
26. How to see all MySQL server Variables and Values?
To see all the running variables and values of MySQL server, use the command as follows.
# mysqladmin -u root -p variables
Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
+---------------------------------------------------+----------------------------------------------+
27. How to check all the running Process of MySQL server?
The following command will display all the running process of MySQL database queries.
# mysqladmin -u root -p processlist
Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep | 5590 | | |
| 18020 | root | localhost | | Query | 0 | | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
28. How to create a Database in MySQL server?
To create a new database in MySQL server, use the command as shown below.
# mysqladmin -u root -p create databasename
Enter password:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| databasename |
| mysql |
| test |
+--------------------+
8 rows in set (0.01 sec)
29. How to drop a Database in MySQL server?
To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y’.
# mysqladmin -u root -p drop databasename
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped
30. How to reload/refresh MySQL Privileges?
The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.
# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh
31. Some useful MySQL Flush commands
Following are some useful flush commands with their description.
flush-hosts: Flush all host information from host cache.
flush-tables: Flush all tables.
flush-threads: Flush all threads cache.
flush-logs: Flush all information logs.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status
32. How to kill Sleeping MySQL Client Process?
Use the following command to identify sleeping MySQL client process.
# mysqladmin -u root -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 5 | root | localhost | | Sleep | 14 | | |
| 8 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Now, run the following command with kill and process ID as shown below.
# mysqladmin -u root -p kill 5
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
If you like to kill multiple process, then pass the process ID's with comma separated as shown below.
# mysqladmin -u root -p kill 5,10
33. How to run multiple mysqladmin commands together?
If you would like to execute multiple ‘mysqladmin’ commands together, then the command would be like this.
# mysqladmin -u root -p processlist status version
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 8 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801 Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003
mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.5.28
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 3 min 21 sec
Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003
34. How to Connect remote mysql server
To connect remote MySQL server, use the -h (host) with IP Address of remote machine.
# mysqladmin -h 172.168.190.1 -u root -p
35. How to execute command on remote MySQL server
Let’s say you would like to see the status of remote MySQL server, then the command would be.
# mysqladmin -h 172.16.25.126 -u root -p status
36. How to start/stop MySQL replication on a slave server?
To start/stop MySQL replication on slave server, use the following commands.
# mysqladmin -u root -p start-slave
# mysqladmin -u root -p stop-slave
37. How to store MySQL server Debug Information to logs?
It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.
# mysqladmin -u root -p debug
Enter password:
38. How to view mysqladmin options and usage
To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.
# mysqladmin --help
We have tried our best to include almost all of 'mysqladmin' commands with their examples in this article, If still, we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.
39. Check the current connections
SELECT thread_id, name AS 'thread_name', type, processlist_user AS user FROM performance_schema.threads;
SELECT thread_id, name AS 'thread_name', type, processlist_user AS user FROM performance_schema.threads where processlist_user is not null;
40. Check the table belongs to and the approximate number of rows, generally refer to when adding field indexes
select TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.tables where TABLE_NAME='table name';
41. Check the fragmentation of the table, whether it is necessary to arrange the table to release physical space
select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2),'MB') as size,DATA_FREE/1024/1024 AS data_free_MB from information_schema.TABLES where table_schema='db1' order by DATA_LENGTH desc;
Single Table Size :
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'db01' and TABLE_NAME='t1';
42. Is there currently a lock?
select * from information_schema.innodb_locks;
43. Current lock blockage
select * from information_schema.innodb_lock_waits;
44. The current lock waiting details
select it.trx_mysql_thread_id, il.lock_id, il.lock_table, il.lock_mode, il.lock_type, it.trx_state, pl.USER||'@'||pl.HOST as user_host, pl.db, pl.command, pl.info, it.trx_started, it.trx_wait_started, now()-trx_wait_started as wait_seconds, il.lock_index, it.trx_weight, it.trx_rows_locked, it.trx_rows_modified
from information_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist pl
where it.trx_id = il.lock_trx_id and it.trx_mysql_thread_id = pl.id
45. The latest deadlock, uncommitted things, CHECKPIONT, BUFFER POOL, etc.
show engine innodb status;
46. Use pager to filter useless thread information
pager grep -v Sleep;show processlist;
47. View the detailed SQL currently running
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info is not null;
48. View the execution time of each stage of a certain SQL, and enable the profiling function
set global profiling=on;
49. View user information
select user,host,password from mysql.user group by user;
50. Generate SQL statements in batches when splitting tables
select concat("select IP as ",TABLE_NAME," from ",TABLE_SCHEMA,".",TABLE_NAME," group by id;")
from information_schema.TABLES
where TABLE_NAME like 'table_%';
(Note: PERFORMANCE_SCHEMA is a new database since 5.5. MySQL5.5 is closed by default and needs to be opened manually. 5.6 is opened by default)
[mysqld]
performance_schema=ON
51. Check which SQL is executed the most
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY COUNT_STAR desc LIMIT 1;
52. Which SQL has the most average response time
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY AVG_TIMER_WAIT desc LIMIT 1
53. Which SQL scan has the most rows (IO consumption)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1
54. Which SQL uses the most temporary tables
SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1
55. Which SQL returns the most result set (net consumption)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_ROWS_SENT desc LIMIT 1
56. Which SQL has the most sorts (CPU consumption)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_SORT_ROWS desc LIMIT 1
57. Which table and file have the most logical IO (hot data)
SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 2
58. Which index is used the most
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY SUM_TIMER_WAIT DESC
limit 1;
59. Which index has not been used?
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
60. Which wait event consumes the most time
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT
FROM events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 1;
61. Obtain the statistical information of database operation through the performance_schema library to better analyze and locate problems and improve monitoring information
Open standard innodb monitoring:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
Turn on lock monitoring of innodb:
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
Open the innodb table space monitoring:
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
Turn on innodb table monitoring:
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
62. Add primary key
alter table xxx add constraint primary key(id);
63. Delete foreign keys
alter table test drop foreign key FK_XXX;
64. QPS
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';
SELECT
VARIABLE_VALUE AS Uptime_seconds,
NOW() AS "Now",
NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",
DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Uptime';
Questions/Uptime
65. TPS
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
SHOW GLOBAL STATUS LIKE 'Uptime';
(Com_commit + Com_rollback)/Uptime
66. Export
select * from MacConfig into outfile '/tmp/MacConfig.sql' fields terminated by ',' lines terminated by '\n';
67. Import
load data infile '/tmp/MacConfig.sql' into table MacConfig fields terminated by ',' lines terminated by '\n';
68. Clean up binlog
PURGE BINARY LOGS TO 'XXX';
PURGE BINARY LOGS BEFORE '2017-11-09 00:00:00';
69. Foreign key isolation level and other information
select @@FOREIGN_KEY_CHECKS;
select @@global.tx_isolation,@@tx_isolation;
select @@character_set_database;
select @@GLOBAL.sql_mode;
70. ACCOUNTS NOT PROPERLY CLOSING CONNECTIONS
Works since 5.6
SELECT ess.user, ess.host
, (a.total_connections - a.current_connections) - ess.count_star as not_closed
, ((a.total_connections - a.current_connections) - ess.count_star) * 100 /
(a.total_connections - a.current_connections) as pct_not_closed
FROM performance_schema.events_statements_summary_by_account_by_event_name ess
JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
WHERE ess.event_name = 'statement/com/quit'
AND (a.total_connections - a.current_connections) > ess.count_star
;
71. UNUSED INDEXES
Works since 5.6
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name
;
72. WHO CREATED TEMPORARY (DISK) TABLES
Works since 5.6
SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
FROM performance_schema.events_statements_summary_by_account_by_event_name
WHERE sum_created_tmp_disk_tables > 0
OR sum_created_tmp_tables > 0
;
SELECT schema_name, substr(digest_text, 1, 40) AS statement, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_created_tmp_disk_tables > 0
OR sum_created_tmp_tables > 0
;
73. ACCOUNTS WHICH NEVER CONNECTED SINCE LAST START-UP [ 3 ]
Works since 5.6
SELECT DISTINCT m_u.user, m_u.host
FROM mysql.user m_u
LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND m_u.host = ps_a.host
WHERE ps_a.user IS NULL
ORDER BY m_u.user
;
74. USERS WHICH NEVER CONNECTED SINCE LAST START-UP
Works since 5.6
SELECT DISTINCT m_u.user
FROM mysql.user m_u
LEFT JOIN performance_schema.users ps_u ON m_u.user = ps_u.user
WHERE ps_u.user IS NULL
ORDER BY m_u.user
;
75. TOTALLY UNUSED ACCOUNTS (NEVER CONNECTED SINCE LAST RESTART AND NOT USED TO CHECK STORED PROGRAM OR VIEW PRIVILEGES) SINCE LAST START-UP
Works since 5.6
SELECT DISTINCT m_u.user, m_u.host
FROM mysql.user m_u
LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host
LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER'
LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER'
LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host)
LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host)
WHERE ps_a.user IS NULL
AND is_v.definer IS NULL
AND is_r.definer IS NULL
AND is_e.definer IS NULL
AND is_t.definer IS NULL
ORDER BY m_u.user, m_u.host
;
76. SHOW FULL PROCESSLIST
Works since 5.5
But with filter on Sleep and sorting by time to find the evil query…
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS info
FROM information_schema.processlist
WHERE command NOT IN ('Sleep', 'Binlog Dump')
ORDER BY time ASC
;
77. Non blocking version, since 5.6:
SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db
, PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state, LEFT(PROCESSLIST_INFO, 80) AS info
FROM performance_schema.threads
WHERE PROCESSLIST_ID IS NOT NULL
AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
ORDER BY PROCESSLIST_TIME ASC
;
78. STORAGE ENGINES PER SCHEMA
For defining backup strategy, preparing migration to InnoDB or Galera Cluster for MySQL, etc.
Works since 5.5
SELECT table_schema AS `schema`, engine, COUNT(*) AS `tables`
, ROUND(SUM(data_length)/1024/1024, 0) AS data_mb, ROUND(SUM(index_length)/1024/1024, 0) index_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
AND engine IS NOT NULL
GROUP BY table_schema, engine
;
+---------------------+--------+--------+---------+----------+
| schema | engine | tables | data_mb | index_mb |
+---------------------+--------+--------+---------+----------+
| mantis | MyISAM | 31 | 0 | 0 |
| mpm | InnoDB | 3 | 0 | 0 |
| mysql_sequences | InnoDB | 2 | 0 | 0 |
| mysql_sequences | MEMORY | 1 | 0 | 0 |
| otrs | InnoDB | 73 | 13 | 4 |
| quartz | InnoDB | 12 | 0 | 0 |
| tracking | MyISAM | 1 | 0 | 0 |
+---------------------+--------+--------+---------+----------+
79. TABLES WITHOUT A PRIMARY KEY
Galera Cluster, InnoDB, M/S replication with row based replication does not work well with tables without a Primary Key. To find those the following query helps:
Works since 5.5 (5.1?)
SELECT DISTINCT t.table_schema, t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.column_key = "PRI"
WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
AND c.table_name IS NULL AND t.table_type != 'VIEW'
;
+--------------+--------------------+
| table_schema | table_name |
+--------------+--------------------+
| test | t_wo_pk |
| test | t_wo_pk_with_Index |
+--------------+--------------------+
80. BAD SQL QUERIES OF USERS
Sometimes it could be interesting to find users who do evil SQL Queries which do for examplecreated_tmp_disk_tables, select_full_join, select_range_check or sort_merge_passes. Those can be found as follows:
SELECT user, host, event_name
, sum_created_tmp_disk_tables AS tmp_disk_tables
, sum_select_full_join AS full_join
, sum_select_range_check AS range_check
, sum_sort_merge_passes AS sort_merge
FROM performance_schema.events_statements_summary_by_account_by_event_name
WHERE sum_created_tmp_disk_tables > 0
OR sum_select_full_join > 0
OR sum_select_range_check > 0
OR sum_sort_merge_passes > 0
ORDER BY sum_sort_merge_passes DESC
LIMIT 10
;
+-------+-------------+---------------+-----------------+-----------+-------------+------------+
| user | host | event_name | tmp_disk_tables | full_join | range_check | sort_merge |
+-------+-------------+---------------+-----------------+-----------+-------------+------------+
| user1 | 192.168.0.3 | insert_select | 0 | 7033 | 0 | 10947 |
| user2 | 192.168.0.4 | insert_select | 0 | 6837 | 0 | 10792 |
| user1 | 192.168.0.1 | select | 10742308 | 2095 | 23061 | 16 |
| user2 | 192.168.0.2 | select | 10958067 | 2639 | 23162 | 14 |
+-------+-------------+---------------+-----------------+-----------+-------------+------------+
81. SHOW PROFILE IN PERFORMANCE_SCHEMA
On systems with heavy traffic the PERFORMANCE_SCHEMA tables might be too small.
mysql> SHOW GLOBAL VARIABLES LIKE 'perf%events%stage%hist%long%';
+----------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size | 10000 |
+----------------------------------------------------+-------+
mysql> pager grep history_long
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
| performance_schema | events_waits_history_long.row_size | 184 |
| performance_schema | events_waits_history_long.row_count | 1000 |
| performance_schema | events_waits_history_long.memory | 184000 |
| performance_schema | events_stages_history_long.row_size | 88 |
| performance_schema | events_stages_history_long.row_count | 1000 |
| performance_schema | events_stages_history_long.memory | 88000 |
| performance_schema | events_statements_history_long.row_size | 3024 |
| performance_schema | events_statements_history_long.row_count | 1000 |
| performance_schema | events_statements_history_long.memory | 3024000 |
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/%';
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements_%';
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_stages_%';
mysql> SELECT ;
mysql> SELECT eshl.event_id AS Query_ID, TRUNCATE(eshl.timer_wait/1000000000000, 6) as Duration
, LEFT(eshl.sql_text, 120) AS Query
FROM performance_schema.events_statements_history_long AS eshl
JOIN performance_schema.threads AS t ON t.thread_id = eshl.thread_id
WHERE t.processlist_id = CONNECTION_ID();
+----------+-----------+-------------------------+
| Query_ID | Duration | Query |
+----------+-----------+-------------------------+
| 12 | 13.560737 | select * from test.test |
+----------+-----------+-------------------------+
mysql> SELECT event_name AS Stage, TRUNCATE(timer_wait/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long
WHERE nesting_event_id = 12;
+--------------------------------+-----------+
| Stage | Duration |
+--------------------------------+-----------+
| stage/sql/starting | 0.000043 |
| stage/sql/checking permissions | 0.000004 |
| stage/sql/Opening tables | 0.002700 |
| stage/sql/init | 0.000025 |
| stage/sql/System lock | 0.000009 |
| stage/sql/optimizing | 0.000002 |
| stage/sql/statistics | 0.000014 |
| stage/sql/preparing | 0.000013 |
| stage/sql/executing | 0.000000 |
| stage/sql/Sending data | 13.557683 |
| stage/sql/end | 0.000002 |
| stage/sql/query end | 0.000008 |
| stage/sql/closing tables | 0.000006 |
| stage/sql/freeing items | 0.000215 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+-----------+
SELECT, INSERT, UPDATE AND DELETE PER TABLE
82. Statements executed against a specific table
Sometimes it is interesting to know how many SELECT, INSERT, UPDATE or DELETE (DML) statementes have been executed against a specific table (for example for OPTIMZE TABLE). This can be found as follows:
Works since MySQL 5.6
SELECT object_type, object_schema, object_name
, count_star, count_read, count_write, count_fetch
, count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0
;
SELECT object_type, object_schema, object_name, index_name
, count_star, count_read, count_write, count_fetch
, count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE count_star > 0
;
83. TOP LONG RUNNING QUERIES
Works since MySQL 5.6
UPDATE setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long';
SELECT left(digest_text, 64)
, ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms
, ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms
, ROUND(MIN(timer_end-timer_start)/1000000000, 1) AS min_exec_ms
, ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms
, ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms
, ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms
, ROUND(MIN(timer_wait)/1000000000, 1) AS min_wait_ms
, ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms
, ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms
, ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms
, ROUND(MIN(lock_time)/1000000000, 1) AS min_lock_ms
, ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms
, MIN(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS first_seen
, MAX(LEFT(DATE_SUB(NOW(), INTERVAL (isgs.VARIABLE_VALUE - TIMER_START*10e-13) second), 19)) AS last_seen
, COUNT(*) as cnt
FROM events_statements_history_long
JOIN information_schema.global_status AS isgs
WHERE isgs.variable_name = 'UPTIME'
GROUP BY LEFT(digest_text,64)
ORDER BY tot_exec_ms DESC
;
+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
| left(digest_text, 64) | tot_exec_ms | tot_wait_ms | tot_lock_ms | first_seen | last_seen | cnt |
+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
| INSERT INTO `test` SELECT ? , DATA , ? FROM `test` | 50493.5 | 50493.5 | 26.3 | 2015-11-12 16:41:35 | 2015-11-12 16:42:04 | 20 |
| SELECT LEFT ( `digest_text` , ? ) , `ROUND` ( SUM ( `timer_end` | 14434.6 | 14434.6 | 25.8 | 2015-11-12 16:48:44 | 2015-11-12 17:07:15 | 6 |
| SELECT * FROM `test` | 7483.0 | 7483.0 | 0.2 | 2015-11-12 16:41:16 | 2015-11-12 16:42:34 | 2 |
| SHOW ENGINE INNODB STATUS | 1912.4 | 1912.4 | 0.0 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SHOW GLOBAL VARIABLES | 1091.1 | 1091.1 | 68.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SHOW GLOBAL STATUS | 638.7 | 638.7 | 40.8 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
| SELECT LEFT ( `digest_text` , ? ) , SUM ( `timer_end` - `timer_s | 356.2 | 356.2 | 42.4 | 2015-11-12 16:42:38 | 2015-11-12 16:45:00 | 6 |
| SELECT `digest_text` , SUM ( `timer_end` - `timer_start` ) / ? A | 325.3 | 325.3 | 0.4 | 2015-11-12 16:40:44 | 2015-11-12 16:42:18 | 3 |
| SELECT `DIGEST_TEXT` , ( `TIMER_END` - `TIMER_START` ) / ? AS `e | 163.2 | 163.2 | 1.0 | 2015-11-12 16:37:44 | 2015-11-12 16:39:22 | 9 |
| SELECT LOWER ( REPLACE ( trx_state , ?, ... ) ) AS state , COUNT | 133.9 | 133.9 | 80.2 | 2015-11-12 16:37:19 | 2015-11-12 17:07:36 | 687 |
+------------------------------------------------------------------+-------------+-------------+-------------+---------------------+---------------------+-----+
84. TABLES NEVER WRITTEN TO
If you want to find tables which it was never written to (or read from) since last instance restart you can use the following query. Works since MySQL 5.6
Caution:
count_read can only be taken as argument if a physical backup method (NOT mysqldump) is used.
If count_write is 0 it does not necessarily mean that there was no write statement (no matching write)!
If tables are empty SELECT statements are not counted (count_read = 0).
SELECT t.table_schema, t.table_name, t.table_rows, tio.count_read, tio.count_write
FROM information_schema.tables AS t
JOIN performance_schema.table_io_waits_summary_by_table AS tio
ON tio.object_schema = t.table_schema AND tio.object_name = t.table_name
WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND tio.count_write = 0
ORDER BY t.table_schema, t.table_name
The most consuming one
-----------------------
> show tables like 'statements_with%';
+---------------------------------------------+
| Tables_in_sys (statements_with%) |
+---------------------------------------------+
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
+---------------------------------------------+
We will then use the statements_with_runtimes_in_95th_percentile to achieve our first task. However we will use the version of the view with raw data (not human readable formatted), to be able to sort the results as we want. The raw data version of sysschema views start with x$:
SELECT schema_name, format_time(total_latency) tot_lat,
exec_count, format_time(total_latency/exec_count) latency_per_call,
query_sample_text
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
ON t2.digest=t1.digest
WHERE schema_name NOT in ('performance_schema', 'sys')
ORDER BY (total_latency/exec_count) desc LIMIT 1\G
85. Full table scans
Another query I would try to optimize is the one doing full table scans:
SELECT schema_name, sum_rows_examined, (sum_rows_examined/exec_count) avg_rows_call,
format_time(total_latency) tot_lat, exec_count,
format_time(total_latency/exec_count) AS latency_per_call,
query_sample_text
FROM sys.x$statements_with_full_table_scans AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
ON t2.digest=t1.digest
WHERE schema_name NOT in ('performance_schema', 'sys')
ORDER BY (total_latency/exec_count) desc LIMIT 1\G
86. Variable Source check
SELECT VARIABLE_NAME
FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE = 'COMMAND_LINE'
ORDER BY VARIABLE_NAME;
87. Display variables set from persistent storage:
SELECT VARIABLE_NAME
FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE = 'PERSISTED'
ORDER BY VARIABLE_NAME;
88. Join variables_info with the global_variables table to display the current values of persisted variables, together with their range of values:
SELECT
VI.VARIABLE_NAME, GV.VARIABLE_VALUE,
VI.MIN_VALUE,VI.MAX_VALUE
FROM performance_schema.variables_info AS VI
INNER JOIN performance_schema.global_variables AS GV
USING(VARIABLE_NAME)
WHERE VI.VARIABLE_SOURCE = 'PERSISTED'
ORDER BY VARIABLE_NAME;
89. The default InnoDB page size is 16K (16384 bytes). To determine the InnoDB page size for your MySQL instance, query the innodb_page_size setting:
mysql> SELECT @@GLOBAL.innodb_page_size;
90. To determine the AUTOEXTEND_SIZE for any InnoDB tablespace, query the INFORMATION_SCHEMA.INNODB_TABLESPACES table. For example:
mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME LIKE 'test/t1';
+---------+-----------------+
| NAME | AUTOEXTEND_SIZE |
+---------+-----------------+
| test/t1 | 4194304 |
+---------+-----------------+
mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME LIKE 'ts1';
+------+-----------------+
| NAME | AUTOEXTEND_SIZE |
+------+-----------------+
| ts1 | 4194304 |
+------+-----------------+
91. Set the read lock, which ensures that there are no database operations in order to get a consistent snapshot.
-- Lock the current, Modifications such as additions, deletions and alterations of other sessions will be blocked
FLUSH TABLES WITH READ LOCK;
92. Check the current application connections and troubleshoot the sudden increase in the number of connections
select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as count,db from information_schema.processlist where host not in ('localhost') and user not in ('replicater') group by ip order by count;