Skip to content

Tag Archives: MyISAM

Top My.CNF Optimizations for Innodb

Friday, August 20, 2010

These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb.   If you are suffering from poor performance, try changing the following settings.  Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks.  The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL.  If you need help optimizing your queries, let us know.

innodb_buffer_pool_size =8G
Set this to ~80% of free server memory.  If you have a dedicated MySQL server with 10G, set to 8G

innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement.  However, this can actually have a negative affect, so make sure you test appropriately.
Related articles
Filed in MySQL, Optimization | Also tagged , , , , , , , , , , , , , , , , | Comments (0)

Top 10 MySQL Tips and Tricks

Friday, March 26, 2010

1) Optimize your WHERE clause.

  • Remove unnecessary parentheses, this will speed up evaluation
  • COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables, but NOT Innodb tables. Beware.
  • If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table
Filed in MySQL | Also tagged , , , | Comments (1)

MySQL Foreign Key Night-mare

Friday, March 26, 2010

I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables.  I can never understand why developers will create foreign keys, however then not write their code properly to prevent errors, or at least attempt to catch those errors.

The code below will quickly create a script that removes all Foreign Keys from a database.  Many folks will simply change the engine type to MyIsam and then back to InnoDB.  This may work, but it is very slow if you already have a lot of data in your tables.

select concat(‘alter table ‘,table_schema,’.',table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;') from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;

You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’

Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.

Filed in MySQL | Also tagged , , , , , , , , , , , , , , , | Comments (1)