Thursday, January 25, 2007

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.
I will add more as time goes by and would appreciate other resources discussing MySQL vs. PostgreSQL.

Wednesday, January 24, 2007

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?

Saturday, January 20, 2007

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:

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.

Saturday, January 13, 2007

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.

Saturday, January 06, 2007

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:
Side notes:

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.

Friday, January 05, 2007

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.

Wednesday, January 03, 2007

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

Monday, January 01, 2007

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?