MySQL performance tuning

September 24, 2006 4:28 PM

How to improve MySQL (5.0.22-max-log, PPC) + InnoDB table performance?

I am using a default my.cnf for a "large system" installation.

I have pre-existing InnoDB tables which work fine with the my.cnf file in its default state.

After stopping the mysqld service, I uncomment InnoDB-specific variables in my.conf and restart the service.

The service starts fine, but when I run my web application, I get exceptions thrown from attempting to login (an authentication table sits within my InnoDB-based table set) or access the data within.

If I stop the service, revert back to the deafult my.cnf, and restart the service, my application runs fine again.

What can I do to improve the performance of my MySQL server if I cannot edit the my.cnf file? How can I fix the my.cnf settings, or the server, so that I can add InnoDB variables?

I have the MySQL Performance Tuning book from O'Reilly. If there is a relevant paragraph or setting I have overlooked from this book, please point it out!

Moving to another database server is not an option, so please do not suggest dropping MySQL.

posted by Blazecock Pileon to computers & internet (9 comments total)

"... I have pre-existing InnoDB tables which work fine with the my.cnf file in its default state. ..."
posted by Blazecock Pileon to computers & internet [+ add to favorites] [!]

What, exactly, does "pre-existing" mean? Did you upgrade your database from a previous version? If you created the database in another instance of MySQL, have you properly created the mysql user table to match on the new machine? Have you changed platforms from Windows to UNIX, and have some capital characters in table names? Have you tried the basic InnoDB troubleshooting steps, and tried to collect information from the monitors?

More importantly, if your app is running correctly with default MyISAM table settings, and turning on InnoDB causes your problem, how sure are you that your tables aren't MyISAM in the first place? Has installation of your Web app created duplicate MyISAM database and table structures, and a working authorization table?
posted by paulsc at 5:04 PM on September 24, 2006

What, exactly, does "pre-existing" mean?

The InnoDB tables existed prior to trying to enable InnoDB variables in the my.cnf file.

Did you upgrade your database from a previous version?

From a minor point revision (5.0.16 to 5.0.22). The upgrade script checked that all my tables were "OK".

If you created the database in another instance of MySQL, have you properly created the mysql user table to match on the new machine?

I copied over the entire ~/data directory.

Have you changed platforms from Windows to UNIX, and have some capital characters in table names?

No.

Have you tried the basic InnoDB troubleshooting steps, and tried to collect information from the monitors?

The error file is somewhat cryptic:

060924 19:06:03060924 19:06:03 [ERROR] Cannot find table weight/users from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem.

From this page, apparently I need to delete my table's .frm files (as well as InnoDB log files) and recreate them.

How can I do this without having to recreate my InnoDB tables from scratch, and reimport the data? Or do I not need to do all of this, and the mysqld engine will recreate the .frm and log files for me?

More importantly, if your app is running correctly with default MyISAM table settings, and turning on InnoDB causes your problem, how sure are you that your tables aren't MyISAM in the first place?

If I do a dump of my schema, I can verify I am using InnoDB for the relevant tables.
posted by Blazecock Pileon at 5:33 PM on September 24, 2006

1. What specific exception(s) do you get when trying to log in? Knowing this will help diagnose the problem. Can you log in successfully using the mysql command line tool?

2. "Premature optimization is the root of all evil". Is there an actual performance problem you're trying to solve? If so, it's preferable to solve in in DDL, specifically by adding or removing indices, or changing table structures. Changing the MySQL configuration will (negatively) affect everything else running on the MySQL server box.

3. If you must change configuration options, change one at a time until you find the change (or combination of changes) that's causing the problem. Then ask about that setting specifically.




Blazecock Pileon writes "From this page, apparently I need to delete my table's .frm files (as well as InnoDB log files) and recreate them.

"How can I do this without having to recreate my InnoDB tables from scratch, and reimport the data?"

And ibdata files; that's where the data lives. Either copy the data to a MyISAM table, or yes, re-import it.

HOWEVER, since you only have the problem after messing with the my.cnf file, perhaps the problem is "mysqld cannot read the proper my.cnf or my.ini option file, and consequently does not see the options that you specified."

Check the file permissions on the new my.cnf, and make sure the mysqld process owner has read rights on it.
posted by orthogonality at 5:37 PM on September 24, 2006

Try deleting the .frm files only, first.
posted by orthogonality at 5:39 PM on September 24, 2006

If you haven't already deleted those .frm files, read this before you do. Looks like your best option for InnoDB tables with inconsistencies of this sort is to either locate and copy the missing .frm file to its expected location, or to treat the affected table as a DROP, then recreate it, and import data.

If you are going to mess around with your .frm files, try renaming or moving them, instead of deleting, until you're sure you can really do without them.
posted by paulsc at 6:20 PM on September 24, 2006

Is there an actual performance problem you're trying to solve?

I have about 50000 records in a "main" table, with details from about 35 dependent tables. Some queries take upwards of five to ten seconds to perform, some longer, some shorter. Querying a single record can take between 0.01 to 3 seconds. Is that normal?

I can't drop and reimport tables because of the dependencies between them — I'd have to reimport data in a specific order. Given the amount of data I cannot start from scratch again.
posted by Blazecock Pileon at 6:25 PM on September 24, 2006

The error message you posted says your 'weight/users' table is missing, and MySQL/InnoDB expects it to be in your data directory, because its corresponding .frm file is still there.

If you deleted weight/users awhile back, instead of dropping it, the .frm file is orphaned. The page I linked above suggests that you can simply delete the corresponding .frm file, as the corrective action. [That's what orthogonality advised.] But review your tablespace first, to be sure you haven't simply got a renamed version of 'weight/users' around.

If you've got tables without corresponding .frm files, you'll have to recreate them in another database, to generate corresponding .frm files, populate them over there with your data, DROP the orphaned table, and move over the new table with its data and .frm file.
posted by paulsc at 6:42 PM on September 24, 2006

Blazecock Pileon writes "I have about 50000 records in a 'main' table, with details from about 35 dependent tables. Some queries take upwards of five to ten seconds to perform, some longer, some shorter. Querying a single record can take between 0.01 to 3 seconds. Is that normal?"

Do an explain on the queries. You'll find they're not using indices. Add the correct indices. This will speed things up enormously. (I or another consultant can be hired to do sort of optimization.)


Blazecock Pileon writes "I can't drop and reimport tables because of the dependencies between them — I'd have to reimport data in a specific order. Given the amount of data I cannot start from scratch again."

Copy each table to a MyISAM table. Drop and recreate the innodb tables. Drop the foreign key constraints and copy back from the myisam tables; copy any autoincrement columns explicitly. Re-add the foreign key. adjust autoincrement keys to get the correct next value for each re-created table.constraints.

You can use the system metadata or some tool to autogen the copy sql; it's just an "insert into copy ( column name, .. .) select column_name, ... from innotable". If you do it directly from the system tables you'll have to do some minor edits by hand to remove trailing commas and add closing parentheses.
posted by orthogonality at 6:50 PM on September 24, 2006

Adding a missing index (where I thought I had added it already) seems to be helping out a little.

I'd try the .frm option but I'm on a deadline to deliver this in a week. There are too many dependencies and I can't risk breaking things just yet — things are running a little better for now, anyway.

Thanks for your help, ortho and paulsc.
posted by Blazecock Pileon at 11:21 PM on September 24, 2006