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