Z-Car

Ramblings about everything from Cars to Computers

MySQL – Best way to speed up Slave replication

By Prodromus May 11, 2010 MySQL, Replication No Comments

The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.

Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.

MySQL Removing duplicate rows – Part II

By Prodromus May 11, 2010 MySQL, Programming No Comments

Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.

If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :

delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster

If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.

MySQL Master and Slave have different Query Execution Plans

By Prodromus May 11, 2010 MySQL, Replication No Comments

I recently ran into a situation where a Delete across a large time period was taking an extensive amount of time to run when replicated to the Slave, although the query ran very quickly when running on the Master. On the Master, the query took

After much head scratching and verifying that indexes existed on both Master and Slave, we took a few minutes to compare the query execution plan using EXPLAIN. It was quickly obvious that the Slave was using a different index than the Master when executing the query. We quickly update the table statistics using Analyze Table, and re-ran EXPLAIN. After the Analyze Table, the problem was solved, and after restarting replication using Start Slave, the offending query quickly executed in 2 minutes.

Moral of the Story: Running Analyze Table on larger tables every couple days is probably a good idea.

MySQL How to delete duplicate records and rows of data

By Prodromus May 3, 2010 MySQL, Programming No Comments

The fastest and easiest way to delete duplicate records is my issuing a very simple command.

alter ignore table [tablename] add unique index `unique_index` ([fieldname])

What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.

This also will work, but is not as elegant:

delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )

MySQL – Restoring from a Dump Syntax

By Prodromus March 26, 2010 MySQL, Restore Backup No Comments

Here’s how to pass the arguments to MySQL to restore from a Dump:

mysql [database name] < [backup file name]

The “[database name]” specifies the name of the database to restore, and “[backup file name]” specifies the full path and filename to the file generated by mysqldump, such as backup.sql. You should also specify your MySQL username and password as usual using -uroot -ppassword.

If I wanted to restore the database named maindatabase from the file named backup.sql, I would run:

mysql maindatabase< backup.sql

Top 10 MySQL Tips and Tricks

By Prodromus March 26, 2010 MySQL 2 Comments

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