Hi, I just did a mysql benchmark to check if it were any use to switch from EXT3 to a different filesystem for performance, etc. We have a 18GB db so I keep trying different stuff. BTW the server was totally new with empty partions. I'm not posting this to claim anything because I hardly see any point of doing that, but I just want to share this info for some soul searching the net for a similar comparison for whom this may be a little helpful.

Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/hda3     ext3     73G  3.0G   66G   5% /
/dev/hda1     ext3     97M   14M   79M  15% /boot
none         tmpfs   1013M     0 1013M   0% /dev/shm
/dev/hdc1      xfs     38G  272K   38G   1% /xfs
/dev/hdc2 reiserfs     38G   33M   38G   1% /reiserfs

The test were done using MySQL super-smack.

#XFS
	#update-select
		Query Barrel Report for client smacker
		connect: max=735ms  min=0ms avg= 54ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    300000		4		0		3402.03
		update_index    300000		3		0		3402.03

	#select-key
		Query Barrel Report for client smacker1
		connect: max=8661ms  min=0ms avg= 2737ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    600000		41		0		13428.82

#ReiserFS
	#update-select
		Query Barrel Report for client smacker
		connect: max=121ms  min=0ms avg= 11ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    300000		5		0		3447.33
		update_index    300000		105		0		3447.33
	#select-key
		Query Barrel Report for client smacker1
		connect: max=8500ms  min=0ms avg= 1428ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    600000		7		0		13487.12

#EXT3
	#update-select
		Query Barrel Report for client smacker
		connect: max=344ms  min=0ms avg= 15ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    300000		5		0		3406.42
		update_index    300000		5		0		3406.42
	#select-key
		Query Barrel Report for client smacker1
		connect: max=1720ms  min=0ms avg= 83ms from 30 clients
		Query_type      num_queries     max_time        min_time        q_per_s
		select_index    600000		4		0		13462.31

XFS did the best but i'm still gonna go with ext3 because I've been using it for years. Again, this post is because I wanted to share my test results not because I'm saying one is better than other. Regards, San.

As you can see, the difference is minimal - as I would expect it to be.

Probably your MySQL server is mostly being held back by the IO throughput.

Generally speaking, more RAM is the thing which makes database servers faster, not anything else.

MySQL opens various files, then keeps them open forever - it doesn't do lots of complicated filesystem stuff.

Of course a non-journalled filesystem would be slightly more performant.

Out of interest, did you try any of those above with data journalling?

Mark

Yep, I agree that it isn't much of a difference but now I'm satisfied I'm not loosing out on any performance. I didn't use data journaling.

I'd rather be asking this in a MySQL list but since its a little related to the FS, if anybody knows, how many databases do you think I can create inside a MySQL installation? Specifically the reason to try all this stuff is because our problem is that we have an average of 100K records per user and with over 10,000 users in our db, thus it kills the server when doing updates/deletions (as MyISAM uses table locking). So I was hoping to create a separate database per member to avoid locking all together. Now, the thing is MySQL creates a directory for each db and what I don't know how many sub-directories you can have inside a directory until the performance starts to degrade. I think this would depend on the FS? Anyway, if anyone has any thoughts on it I'd love to hear them.

Regards,
San.

I think having a huge number of tables (or databases) is a really bad idea, beacuse using MyISAM you would end up exhausting the max number of tables open at once easily.

Then it would have to keep opening and closing tables, which is going to lag things.

If it's table-level locking that's really the killer, perhaps InnoDB is the way to go?

Obviously this has the disadvantage that selects will be slower.

But not all of your tables need to use the same table handler.

Mark