Re: Partitioned table causes open_files errror

[Bill Willits]

Thanks for the feedback.

We usually have 10-20 connections, but in heavy times and/or slow queries are run, the connections will jump up.

So far, the most connections used is 113.

localhost:(none)> show status like 'max_used_connections' ;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 113 |
+----------------------+-------+

I checked how many instances of each file are open. The results are very interesting. Most of the MYD files are open once only (some more but all under 10). But, the partitioned table MYD files are open 120 times each as shown below (I shortened the list for readability).

bwillits@db02:~$ sudo lsof -u mysql -F n |sort|uniq -c | grep -v "1 "
...
120 n/var/lib/mysql/stun_development/Subscribers#P#p0.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p10.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p11.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p12.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p13.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p14.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p15.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p16.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p17.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p18.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p19.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p1.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p20.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p21.MYD
120 n/var/lib/mysql/stun_development/Subscribers#P#p22.MYD
...

All of the partitioned table MYD files are open the same number of times: 120
Any idea why they are open so many times?
Do I need to limit the number of concurrent connections (currently 250).

Thanks,
Bill