We don't collect your data, we need the following information: schema information, queries, and plan of queries execution.
To collect information from your MySQL server for query optimization, please follow these instructions.
1. Create a directory to store the exported data, set permissions and export schema
To collect information from your MySQL server for query optimization, please follow these instructions.
1. Create a directory to store the exported data, set permissions and export schema
mkdir -p /var/lib/mysql-files/
chown -R mysql. /var/lib/mysql-files/
mysqldump --no-data -A > /var/lib/mysql-files/dump_all.sql
2. Execute the following queries with the root MySQL user to save indexes, performance schema and column information into CSV files in the created directory
SELECT *
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
INTO OUTFILE '/var/lib/mysql-files/indexes.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT *
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
INTO OUTFILE '/var/lib/mysql-files/digest.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT *
FROM performance_schema.file_summary_by_instance
INTO OUTFILE '/var/lib/mysql-files/IO_Summary_Tables_DAY.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT *
FROM information_schema.columns
INTO OUTFILE '/var/lib/mysql-files/columns.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT *
FROM information_schema.tables
INTO OUTFILE '/var/lib/mysql-files/tables.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
SELECT *
FROM performance_schema.table_io_waits_summary_by_index_usage
INTO OUTFILE '/var/lib/mysql-files/IO_Waits_Summary_by_index.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
3. To provide us with the slow query log, find its location by executing the following SQL query
SHOW VARIABLES WHERE Variable_name = 'slow_query_log_file';
4. Please send us /var/lib/mysql-files/ folder and slow query log file using the following form.