MySQL Forums
Forum List  »  Performance

very poor performance of opening tables
Posted by: J G
Date: September 23, 2005 06:11AM


We have problem with one of our site. It has about 1 milion hits a day. It's heavy MySQL loaded website. Our hardware is brand Dell dual Xeon 2.8 (1MB cache each), 2GB RAM, Seagate U320 73GB scsi drives. We have 20 000 databases on this server, each has about 100 tables in it. All has about 15GB capacity. SQL scripting in php files is self-optimized, It is as good as it can be, we cannot optimize it better. We also cannot minimize the number of tables.

Our operating system is very well optimized FreeBSD 5.4 with linux_threads.

All the time server works good, pages loads really quickly. We have only problem in the rush hours when there is more than a few hundreds of concurrent Apache connections. In the rush hours our MySQL server is flooded because can't handle such number of queries. Apache performance is good all the time and it's able to handle much more traffic than in the rush hours.

When looking at "show processlist" in these hours we have about 100-500 MySQL processes with "opening tables" state, a few with "closing tables" state and one or two other states.
Thre is allways 50-80% of idle CPU. Our hard drives are not overloaded, in systat each disk has only 0,5 - 1,5 MB/s of busy. Mose of MySQL child processess has "pause" status in the OS when looking at util "top".

First we had all databases on one dedicated SCSI drives. I had thought that our disk performance is too slow. Then we balanced all these databases into 4 SCSI drives (via symbolic links). Now we have about 5000 databases on each disk but problem still occurs. Disks are really bored and can handle much more. We have additional disk for system, logs and php files.

After these experiences I disocoverd that it must be software limitation or bug. I started to investigate more my.cnf settings.

I've tried to increase table_cache. The maximum number I can set is about 4000-8000 of table_cache. Our FreeBSD can't handle such many open files and it hangs when it has about ~100 000 open files. For stability reasons We can't set table_caches at more than about 10.000 which is to small to handle all tables in cache.

I use mytop to monitor mysql queries. We can't never reach more than about 500 queries/s. We had such number on one hard drive, I was sure than we will be able to reach about 2000 queries on 4 hard drives but I was false. (there is also 100-200q/s from query_cache).

I can paste here any of my configs, variables, status but I think is unnecessary here. Something is limit our opening table speed in unnatural way. I'm sure than when opening table speed will faster, we will be able to handle much more queries/sek, maybe a few thousands.

Our tables are MyISAM. We have about 60% of selects, 15% of inserts+updates. We don't use commands like flush tables, lock tables, alter table. There is nothing what can lock these tables. Partitions are UFS2+softupdates with noatime.

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?

[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?

[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?

Thank your for any hints. Please let me know if you would to see any of my configs or status/variables.


I forgotten to add. Our MySQL version is 4.0.25 with BUILD_STATIC

Edited 2 time(s). Last edit at 09/23/2005 08:52AM by J G.

Options: ReplyQuote

Written By
very poor performance of opening tables
September 23, 2005 06:11AM
September 23, 2005 08:59AM
September 23, 2005 10:40AM
September 23, 2005 10:51AM
September 25, 2005 09:45AM
September 25, 2005 10:34AM
September 25, 2005 11:16AM
September 26, 2005 06:02AM
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.