So, it's supposed to hit the index and run almost instantly, and it did when the testing started, but now, after millions of rows were removed, it gets stuck in the 'optimizing' state for several minutes. The advantages of turning this on can be found in mysql documentation Using Per-Table Tablespaces.Īlso, comment out log_bin and expire_logs_days, max_binlog_size in my.cnf before importing data to avoid excessive binary logging which may consume disk space (MySQL restart required to take effect). The explain command on this query shows: '1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'. Note: Because option B is more complicated and involves down time, you may turn on innodb_file_per_table in my.cnf before step 6. Something is causing a load average of 500, but it does not show in the screenshot you provided. Mysql>GRANT ALL PRIVILEGES ON pac.* to IDENTIFIED BY 'pacuser' Since you have a huge amount of RAM, but MySQL is barely using any of it, and you have 500MB of data, suggest raising innodbbufferpoolsize from 128M to at least 1G.

Re-create pac database and grant permission to user 'pacuser'.# mysqldump -u root -p pac > out_put_dumpfile ibd files and run MySQL query ' OPTIMIZE TABLE ' on corresponding tables to shrink disk space usage.Īll tables including system tables are stored in / ibdata1. If turned OFF, then all tables and their indexes are stored in the system dataspace ibdata1 and this file requires optimization.Įach table has its own. While the s/w accesses the DB the DB cpus get all to 100, while processlist report 99 of queries stuck in 'checking permissions'. Modifying the software is not an option (it is bought), and it seems most of the cpu is consumed by 'checking permissions'. See Section 8.4.3.1, How MySQL Opens and Closes Tables. We have about 20 databases with about 8000 tables each. If innodb_file_per_table is ON, each InnoDB table and their indexes are stored in their own file that requires optimization. SHOW OPEN TABLES lists the non- TEMPORARY tables that are currently open in the table cache. Mysql> show variables like '%innodb_file_per_table%' Check if innodb_file_per_table is turned on with MySQL statement.

Mysql show tables show too long