Bad application design won't get you anywhere with PostgreSQL or MySQL

I am so sick and tired of engaging into flame wars comparing MySQL and PostgreSQL especially when people haven't used both databases together. Both databases have their own strengths, however, if your application is not tuned for the underlying database, you are out of help.

Why do people hate MySQL so much without looking under its hood?

In my opinion, none is the right tool for *every* job.

• 5 reasons why you should never use PostgreSQL
  
• PostgreSQL vs. MySQL
• PostgreSQL vs. MySQL vs. Commercial databases
• PostgreSQL vs. MySQL Comparison (Slashdot)
• PostgreSQL or MySQL
  
• Laughing at Homeland Security

I will add more as time goes by and would appreciate other resources discussing MySQL vs. PostgreSQL.

UPDATE LOW_PRIORITY

TIP: If your UPDATEs on a read intensive environment are taking as much as 1800 seconds, and if you can afford it, try using UPDATE LOW_PRIORITY ..., it can help!

You can also restart MySQL server with --low-priority-updates to make all UPDATEs low priority.

Or, you can make your SELECTs HIGH_PRIORITY.

BTW, does any one know why my last post about MySQL performance tuning went missing on Planet MySQL?

Back from MySQL Performance Tuning Course in Washington, DC

About an hour ago, I got back from Washington, DC after spending four days attending a very productive MySQL performance tuning training session. I really enjoyed conversations with old and new friends including:

• Tobias Asplund, Instructor
  
• Ronald Bradford, Senior Consultant MySQL Inc.
• Michael "Mike" Amedeo Alaimo, IT Manager, Distributive Networks
  
• Stephen Spitzer, Learning Resource Center, USUHS
• Nyk Cowham, Systems Architect, Forum One Communications
• Joe Mansour, Web App. Programmer, and Donnie Lynn, DBA, Computing Center, University of Louisiana Monroe

I couldn't get cards of other friends I made there. If you were there I would love to hear again from you.

I enjoyed the class very much. We had many engaging discussions and tips were flying everywhere.

Tobias was very generous in answering questions, eager to jump to the whiteboard to take an in depth dive. Well done, my friend.

Towards the end of the session I was feeling strongly that this course actually should be a two part course. What we did in the first 1.5 days should be part of a different course or that material be given to students a few days so it can be reviewed by them. That way, we can intensify discussions even more from the very start of the course.

While talking with my friends there I also felt that there should be an addition to the course consisting of real-life problems and solutions as experienced by the companies using MySQL. I would be willing to contribute generic form of problems and gotchas I have encountered related to MySQL performance tuning on Linux and Solaris.

After having attending the performance training tutorial session by Tobias Asplund and Peter Zaitsev at MySQL Conference 2006, I was very excited before going to this session and I am glad that it was a very good decision. I will highly recommend this session for anyone who is interested in MySQL performance tuning. Professionals of all skill levels will get something out of this.

A big thanks once again to Warren and Ronald.

MySQL Performance Tuning Training in Washington, DC

I will be spending the coming week in Washington, DC to attend a four day MySQL performance tuning training session.

I believe Tobias Asplund will be the presenter. During the MySQL conference last year (then known as MySQL Users Conference), I attended a three hour performance tuning session presented by Tobias and Peter Zaitsev and enjoyed it very much so I cannot wait to attend this one.

The training will be held at MySQL's training facility in downtown Washington, DC.

I will be staying at Washington Suites in Georgetown, DC. Their suites are less expensive than many hotel rooms.

A big thanks to Ronald Bradford for recommending the training session, my boss Warren Habib for agreeing to sponsor me and Kerry Ancheta for assisting me in the invoicing process.

Ronald and Michelle have more on the trip.

If you are planning to attend the training, drop me a line and let's get together for a drink or two.

If you have a MySQL performance tuning question, especially if they are related to InnoDB and IO bound issues, send away as a comment and I will try to get it answered for you.

InnoDB and MyISAM Disk Utilization

My fellow MySQLers who I have chatted with recently on IRC, know how much the AUTO-INC table lock in MySQL has been bugging me. Whenever my server gets an increased number of concurrent INSERTs, I start experiencing thrashing.

Yesterday night I spent all night trying to find the bottleneck within my system which happened to be none other than disk bound.

Everyone knows that InnoDB is excellent for its concurrency, thanks to its row level lock granularity. What comes as a surprise, to some, is that if you happen to use an AUTO INCREMENT column, then InnoDB needs to acquire a special, table level, AUTO-INC lock.

Everything was fine until I started finding many threads in InnoDB queue waiting to acquire this lock.

iostat -xnz 5 reported disk busy at 100% most of the times.

iosnoop reported that most of the fsflush activity was being caused by MyISAM (MYD and MYI) files. To check for write activity related to InnoDB files, I used the following command:

[root@db] time /usr/bin/perl iosnoop  | grep fsflush  | grep ib
#  UID   PID D    BLOCK   SIZE       COMM PATHNAME
0     3 W   130208   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   129744   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130240  16384    fsflush /var/tmp/ibXcaq7T
0     3 W   130290   7168    fsflush /var/tmp/ibXcaq7T
dtrace: 11 dynamic variable drops with non-empty dirty list
0     3 W   130208   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   129744   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130240  16384    fsflush /var/tmp/ibXcaq7T
0     3 W   131504   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   129440  49152    fsflush /var/tmp/ibXcaq7T
0     3 W  1736736  16384    fsflush /var/tmp/ibXcaq7T
0     3 W  1739344  32768    fsflush /var/tmp/ibXcaq7T
0     3 W   128928   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130208   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   129744   8192    fsflush /var/tmp/ibXcaq7T
0     3 W  1739440   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   131504   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130240  16384    fsflush /var/tmp/ibXcaq7T
0     3 W  1736736  16384    fsflush /var/tmp/ibXcaq7T
0     3 W  1739344  32768    fsflush /var/tmp/ibXcaq7T
0     3 W   129440  49152    fsflush /var/tmp/ibXcaq7T
dtrace: 5 dynamic variable drops with non-empty dirty list
dtrace: 5 dynamic variable drops with non-empty dirty list
dtrace: 2 dynamic variable drops with non-empty dirty list
0     3 W   129744   8192    fsflush /var/tmp/ibXcaq7T
0     3 W  1736736  16384    fsflush /var/tmp/ibXcaq7T
0     3 W   131504   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130208   8192    fsflush /var/tmp/ibXcaq7T
0     3 W   130240  16384    fsflush /var/tmp/ibXcaq7T
0     3 W   129440  49152    fsflush /var/tmp/ibXcaq7T

For the same duration, much heavier fsflush activity was reported for MyISAM tables.

Since my situation is disk bound I am going to convert those tables to InnoDB to help relieve some pressure from the system.

Some tools for finding out disk utilization by process:

• psio: "This program could assist finding a process that is causing the most I/O. The I/O reported is the total of all types: disk I/O, network I/O, stream I/O, etc.. "
  
• prusage:
• iosnoop:
• iotop:
• lsof:
  

If you know of any other tools that can assist with monitoring disk utilization by process/LWPs on other systems, add them by leaving a comment.

Other resources:

• A Comparison of Memory Allocators in Multiprocessors: On Solaris, switching from malloc to mtalloc can increase performance.

Side notes:

• WSJ has a story on the impact of immigrant innovators and entreprenuers on the US economy.
  

Speaking at the MySQL Conference

Recently, I found out that I'll be speaking at the upcoming MySQL Conference in Santa Clara, California. Last year, it was a lot of fun and the conference was very successful. I can't wait to see everyone there again.

InnoDB UPDATE

The other day I heard that InnoDB UPDATE statements perform DELETE and INSERTs and REPLACE is therefore more efficient. Now I'm trying to confirm it by finding documentation on it.

Increase innodb_log_file_size: the proper way

If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.

The proper way to increase the innodb_log_file_size:

1. shutdown mysql server
   
2. make backup of data and log files
3. remove InnoDB log files
4. set new value for innodb_log_file_size in my.cnf
5. start mysqld
6. check error logs to ensure everything went fine.
   

Also see:

1. Choosing proper innodb_log_file_size
2. innodb_log_file_size (forum post by Jay Pipes)
3. mysqld crashes when innodb_log_file_size is set greater than 4G
4. InnoDB startup options
   

Falcon Storage Engine Update

Jim Starkey's Falcon storage engine for MySQL is now available as open source.

Brian "krow" Aker has more on Falcon.

Why can't MySQL use UPDATE instead of DELETE and INSERT for REPLACE?

"[REPLACE] either inserts, or deletes and inserts."

from "REPLACE Syntax"

My question is why it is done like this? Why not simply UPDATE the row? Wouldn't it be a lot less taxing?