2

I have a MySQL database that contains millions of rows per table and there are 9 tables in total. The database is fully populated, and all I am doing is reads i.e., there are no INSERTs or UPDATEs. Data is stored in MyISAM tables.

Given this scenario, which linux file system would work best? Currently, I have xfs. But, I read somewhere that xfs has horrible read performance. Is that true? Should I shift the database to an ext3 file system?

Thanks

1
  • I really doubt that XFS is your bottleneck here. You should re-question this for improving cache hit ratio (like adding more RAM).
    – pauska
    May 31, 2010 at 12:42

4 Answers 4

2

In general the two decent Linux FS' out these days are XFS and EXT4, both extent based filesystems.

I've not seen any serious differences between the two, but both are notably better then EXT3.

Possibly the best things you can do are:

  • Disable atime (notatime mount option)
  • Up the MySQL buffers to ~2/3 of physical RAM (leaving the rest for disk cache)
  • Verify your indexing with EXPLAIN SELECT ...
  • Dump the FS and restore the files fully written, this slightly reduces fragmentation
1
  • Speaking of fragmentation, xfs has the xfs_fsr program which "reorganizes" (defragments in a way) the filesystem. Sep 5, 2010 at 3:11
1

If you are serious about the performance of your MySQL database, I strongly suggest to do your own benchmarks in a test environment. Filesystem performance can vary significantly from one kernel version to the other, and choice may depend considerably on your exact workload.

For a simple comparison, just set up a test server with the same kernel version as your production system, and use sysbench to benchmark xfs, ext3 and ext4.

To have a better view, you would restore a recent backup of your database on your test server and create some scripts that generate load similar to your workload.

Also, every time you plan to upgrade the kernel on your production machine, you should rerun the benchmarks, to see if there are regressions. Since you can't easily switch file systems at that point (well, that depends on your setup), you should at least make sure that performance is not degraded after the upgrade.

Edit:

BTW, you can find a lot of good advice on the MySQL Performance Blog.

1
  • Thanks for recommending the blog. I am already reading their book
    – gmemon
    Apr 25, 2010 at 2:35
1

Try MySQLTuner just to get an idea of where your problem lies. I would imagine it is bad index performance or a really bad cache hit ratio. Probably way too small for your dataset without knowing more information.

wget mysqltuner.pl (polish domain name with redirect to actual script.)

And like other posters I highly doubt your disk performance or choice of filesystem is the bottleneck.

0

How big is the actual database? You mentioned millions of rows, but that doesn't say how big each row is. Where I'm going with this, is that if you do not do any updates and this is just a read only database, consider loading it into memory (assuming it fits and assuming you have enough RAM).

2
  • Thanks for your reply. The two tables that I am mostly dealing with are 14 GB and 28 GB in size. Each row in both the tables is almost 3 KB. The total physical memory on my server is 16 GB. I have realized that for each table there are 500 groups, and all my queries involve a group by clause, and perform analysis on those. I am thinking that I should create 500 partitions instead, and issue 500 queries on 500 separate connections. I believe This would provide a 500x speedup. What do you think?
    – gmemon
    Apr 25, 2010 at 2:43
  • not sure re x500, especially if you already have index on the column that you group by. there will be however some speed up, i found that on average group by adds 10-20% in terms of processing time. but this was measured on relatively small data set, so you might have a better performance increase.
    – rytis
    Apr 25, 2010 at 7:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .