It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row [...]
Slow DROP TABLE
June 16, 2009 By 21 Comments
It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it turns out, it can sometimes have a much more severe impact on the MySQL performance that it would seem. When [...]
The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance. It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are [...]
Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ? I generally prefer to see Innodb as the main storage engine because it makes life much [...]
Living with backups
November 6, 2008 By 28 Comments
Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right [...]
Rendundant Array of Inexpensive Servers
August 21, 2008 By 15 Comments
So you need to design highly available MySQL powered system… how do you approach that ? Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience – with quality commodity hardware (Dell,HP,IBM [...]
MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I’ve got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:
The tool I’ve been waiting for years
March 18, 2008 By 10 Comments
I’ve just been pointed to the nice tool which I was waiting for years to see. It is fincore – little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.
Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:
Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
Really often in customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state”, “gender”, “status”, “weapon_type”, etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I’d like to present [...]
