MySQL Forums
Forum List  »  Performance

Re: very poor performance of opening tables
Posted by: Jay Pipes
Date: September 26, 2005 06:02AM

J G wrote:
> As I said
> I am expecting about 1600q/s on this hardware...
> So now I'm sure my problem is not releated to
> emulation of linux_threads.

Why 1600/s? Is this number based on previous performance numbers before you started experiencing issues?

> I think I will try to install Gentoo (it's like
> *BSD :), but I need to have same second server for
> tests. Maybe next week.

Well, I'm a big Gentoo fan. I run my servers on that metadistro myself. However, I don't know of any specific reason why Gentoo would perform any better in this scenario than FreeBSD. The only thing I can think of that may give you a performance benefit on Gentoo is doing a stage 1 install with NPTL (Native Posix Thread Library) compiled into the kernel (use the USE flags: nptl nptlonly and remember you'll have to unmask for the most recent MySQL version when you emerge MySQL...). Even with Gentoo Stage 1 w/ NPTL though, I still don't know whether you'd see any significant change in performance. I still think the best idea is to split the databases onto several smaller servers... cont'd below.

> What do you recommend, RAID0 with 4 disks or my
> present solution: MySQL datadir in the ramdisk,
> and there a lots of symlinks to 20.000 databases
> located on other four hard drives without raid ?

Well, if you go RAID0 you're not getting any redundancy, so make sure you've got a good backup plan! And, in your situation, I really don't believe you're going to get any benefit from the RAID0 setup at all. The reason is this: RAID0 helps if you've got large file sizes, where the RAID controller can split the large file across the various disk stripes, allowing all the disk heads to read/write at once, gaining some performance benefit. In your case, the bottleneck is *not* the hardware, because you've got tons of small files being thrown around. The problem is the sheer number of file descriptors used by MySQL and the operating system in managing 20K databases having 100+ tables. The only way I can see to get your performance back in line is to address this fundamental issue. You've either got to reduce the number of databases or tables on this server (by spreading the databases out across many servers, using InnoDB w/tablespace, or manually consolidating the databases/tables) or you may have to investigate a different filesystem (perhaps ReiserFS) which is more suited to many small files. I have had good performance with ReiserFS 3.6 on Gentoo, and ReiserFS 4 on Gentoo seems to be stabilizing (see the gentoo wiki for more info on that), but again, you should test your specific installation thoroughly.

Hope this helps a bit...

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
19810
J G
September 23, 2005 06:11AM
7950
September 23, 2005 08:59AM
4851
J G
September 23, 2005 10:40AM
4236
J G
September 23, 2005 10:51AM
4507
J G
September 25, 2005 09:45AM
3967
September 25, 2005 10:34AM
5386
J G
September 25, 2005 11:16AM
Re: very poor performance of opening tables
3811
September 26, 2005 06:02AM
3586
J G
October 16, 2005 11:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.