We had a similar problem (though not quite as bad).
I re-organized the datafiles (and fixed some indexes, etc) and we got a vast speed improvement.
I'd suggest you shutdown the database, use mysqldump to take a dump of the database, move the old datafiles out of the way, fix your my.cnf to create new datafiles of about the same size (and if you can, on different disks), and then reimport the dump. Note that it would be much faster on the import if you used the new extended insert (-e or --extended-insert), assuming the version of MySQL you are using supports it.
You didn't indicate the version, which might be helpful.
David
Ronan Lucio wrote:
Mayuran,
Well, I´m not a MySQL expert, but I think that a good configuration in the my.cf file can make it better.
Ronan
----- Original Message ----- From: "Mayuran Yogarajah" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 26, 2004 5:37 PM Subject: huge innodb data files
Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ?
thanks, M
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
