There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based application. We have daily schedule for "Analyze Table <>" to avoid stale statistics because of added records everyday. Besides Analyze table, I was intended to execute "Optimize Table <>" to reorganize the physical storage of table and index data for reducing space and improving I/O efficiency while access data by the application, but never successed to do in production due to time it consumes.
As an alternative, I applied "ALTER TABLE <> ENGINE=INNODB;" command to all tables as it could acheive the advantage of OPTIMIZE command. It significantly contribute to reorganize physical storage of the table and indexes in the databases as I have shown following metrics for big three tables:
data_length index_length Size_MB
Before After Before After Before After
BigTable1 9042919424 8312061952 8148451328 6464471040 16394.97 14092
BigTable2 2877292544 2425356288 3173138432 2286764032 5770.14 4493.83
BigTable3 1534066688 1232076800 2261762048 1955594240 3619.98 3040
I was assuming that selection on the tables will have better execution time by above maintenance.
Unfortunately, it's back fired during SELECT statements. 5Seconds report query taking more than a Minute, I have verified that before and after execution time is same and data growth is as usual. What could be the reason in this context?
Aucun commentaire:
Enregistrer un commentaire