Hi All, I had to do an alter table on a very large InnoDB table (100 million rows avg. row size 300 bytes inclusive of index length). This table is on a 200G disk and the server has 4G RAM. I also archived 50 million rows to another table. So the total table size is now 1/2 of its original.
Since the alter table was run, a reporter process (that does selects on the table) has been taking longer than before. The performance has been degrading with evey attempt to fix the issue. I tried the following to fix the problem: 1. analyze table <table name>; 2. alter table <table Name> type=InnoDB; (to fix defragmented index data after large deletes) 3. alter table <table name> order by timestamp; (to make the data sequential if the alter table from previous steps had screwed up the order) Each of the above steps (done over a few days) have progressively degraded the performance of the reporting process to an extent that it now takes twice the time to run the same queries. The disk is 100% busy when the query is being executed. Before the alter table was run, the disk was Idle all thro' the reporter's run and there was very little disk access. It seemed that the data was being served from the filesystem cache. Running explain on the queries indicate that the correct index is being used and there are no table scans. Although all the queries run by the reporter are now logged as slow the number of rows examined is always equal to number of rows sent. Has anyone come across a similar problem. If so, what should i do to fix it? Your responses would be greatly appriciated. thanks, -pradeep PS: The InnoDB buffer pool size was also increased to 800MB after the alter table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
