MySQL Forums
Forum List  »  Performance

Re: very poor performance of opening tables
Posted by: Jay Pipes
Date: September 23, 2005 08:59AM

J G wrote:
> Our tables are MyISAM without indexes.

I really hope this is a typo and you meant *with* indexes...

> My questions are:
>
> [1] Is this MySQL limitation? Is there any way to
> make MySQL handles such number of tables smoothly
> without big table_cache>2millions? What to do
> with MySQL to make it able to open many tables in
> the same time very fast?

This is not necessarily going to be a limit of MySQL, but rather with the OS's ability to deal with massive numbers of file descriptors. With MyISAM, each opened table will be attached to a file descriptor. If you have tens of thousands of tables, even if you up the table_cache, the OS may not be (and clearly isn't) able to deal with that many file descriptors at once. The number of file descriptors and th speed at which file descriptors are handled is very OS-dependent. I honestly don't know how many file descriptors FreeBSD 5.4 with linux_threads is able to handle, but millions is certainly not an option.

> [2] Is this OS releated limitation? Maybe Linux
> will be able to opening&closing tables fast
> without having table_cache? What to do with OS to
> make MySQL able to open many tables fast in the
> same time?

You can adjust the file descriptors by modifying the /etc/sysctl.conf file and changing the fs.file-max variable to a greater number. The exact number of file descriptors supported will depend on RAM and CPU, and you will have to test it to see your limit. You may also need to set the mysql user's file descriptor limit manually in the /etc/security/limits.conf file. Again, however, I highly doubt you'll be able to support anywhere near millions of file descriptors.

> [3] What server strategy for us do you recommend
> in the future? Our website is growing fast. Should
> we go into big 10-20 disk RAID 0 arrays or use
> lots of low end servers and a small number of
> tables on each?

It sounds like your hardware is really not the issue, and that your current RAID setup is fine. However, due to the reasons outlined above, changing to a setup where there are fewer number of tables per database/server is *definitely* going to help your situation.

HTH,

> Thank your for any hints. Please let me know if
> you would to see any of my configs or
> status/variables.
>
> Regards.
>
> I forgotten to add. Our MySQL version is 4.0.25
> with BUILD_STATIC
>
>
>
> Edited 1 times. Last edit at 09/23/05 08:13AM by J
> G.

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
19811
J G
September 23, 2005 06:11AM
Re: very poor performance of opening tables
7950
September 23, 2005 08:59AM
4852
J G
September 23, 2005 10:40AM
4237
J G
September 23, 2005 10:51AM
4508
J G
September 25, 2005 09:45AM
3968
September 25, 2005 10:34AM
5387
J G
September 25, 2005 11:16AM
3812
September 26, 2005 06:02AM
3587
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.