• slow queries are a good place to start investigating high CPU usage
  • https://mariadb.com/kb/en/library/optimization-and-tuning/
  • https://www.percona.com/blog/2014/01/24/mysql-server-memory-usage-2/ mysql> SHOW ENGINE INNODB STATUS;

1. Memory usage

1.1. Per server (Global)

1.1.1. key_buffer_size
1.1.2. query_cache_size
1.1.3. table_open_cache
1.1.4. tmp_table_size
1.1.5. innodb_buffer_pool_size
1.1.6. innodb_buffer_pool_bytes_data
1.1.7. innodb_log_buffer_size
1.1.8. log_tc_size
1.1.9. max_heap_table_size * number of tables

1.2. Per each connection/thread

The number of currently open connections is visible in this variable: threads_connected. Each thread can allocate the following:

1.2.1. read_buffer_size
1.2.2. sort_buffer_size
1.2.3. myisam_sort_buffer_size
1.1.4. innodb_sort_buffer_size
1.2.5. read_rnd_buffer_size
1.2.6. join_buffer_size
1.2.7. thread_stack
1.2.8. binlog_cache_size
1.2.10. bulk_insert_buffer_size
1.2.11. max_sort_length

1.3. In memory tables

SELECT CONCAT(table_schema, ".", table_name), data_length, index_length FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'MEMORY' and table_schema <> "information_schema";
SELECT SUM(data_length), SUM(index_length) FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'MEMORY' and table_schema <> "information_schema";

2. User management

-- 2.1. Add a new user:
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';

-- 2.2. Deleting an existing user:
mysql> DROP USER 'finley'@'localhost';

-- 2.3. Updating the password of an existing user:
mysql> SET PASSWORD FOR 'finley'@'localhost' = PASSWORD('auth_string');

3. Dump

# 3.1. A Database:
mysqldump -h db1 -ppass databaseName

# 3.2. A table from a database:
mysqldump -h db1 -ppass databaseName tableName

# 3.3. Copy table by dumping between two hosts:
mysqldump -h db1 -ppass1 dbname tableX | mysql -h db2 -ppass2 dbname

4. Repair table

repair table tableX;

5. Create a new table using the same structure as another table:

create table users_backup like users;

6. Show currently running queries:

show processlist;

7. Engine statistics:

# List of databases and size of each in MiB
mysql -h server1 -P 3306 -u root -psecret -e "select table_schema as database_name, round(sum(data_length + index_length) / 1024 / 1024, 2) as size_mib from information_schema.tables group by table_schema order by size_mib desc;"