Hello.
The problem could be related to the filesystem layer. You may use a raw disk partition or decrease the size of your tablespace (if it is autoextend). Pradeep Hodigere <[EMAIL PROTECTED]> wrote: > 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. > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
