Thanks for the reply. It helped alot, since I did not know where to look.
I read the documentation regarding the issue and some more pages google
turned up for me.
I did increase my table cache to 16k and my open files are at 30k. I do
run debian etch 32-bit. How would I determine what reasonable means for
open files are on my system? I have 4GB Ram and a 2.6 Ghz DualCore.
What also interests me is my key buffer. I have it set to 768MB:
mysql> show status like "key%";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 661370 |
| Key_blocks_used | 40169 |
| Key_read_requests | 22935142 |
| Key_reads | 65152 |
| Key_write_requests | 1941899 |
| Key_writes | 162770 |
+------------------------+----------+
7 rows in set (0.00 sec)
This would mean the following:
65152 Key_reads / 22935142 Key_read_requests * 100 = 0.29 %
I think this would be an reasonable value. Am I right?
Regards,
Samy
Brent Baisley schrieb:
> The problem is your table cache setting. MySQL will only keep a
> certain amount of tables open and ready for access. Once that limit is
> hit, if a table that is not already open needs to be accessed, it
> needs to close a table to make room for opening the "new" table.
> This doesn't mean you can just set the table cache size really high.
> The operating system has limits as to how many file handles it can
> have open at one time.
> In mysql type this:
> show status like "open%"
>
> Your Opened_tables number is probably huge. That's how many times
> mysql had to open a table for access. Meaning the table wasn't opened
> already. The Open_tables is how many tables MySQL will keep open
> (table cache). You need to increase this number using the table_cache
> variable.
> set global table_cache=#;
>
> The Open_files number from the show status command is important, make
> sure that stays within limits of your operating system.
>
> On Nov 12, 2007 5:09 PM, Samuel Vogel <samy-delux@stripped> wrote:
>
>> Hey guys,
>>
>> I do run MySQL on a high traffic Server with approximately 10k
>> databases. Since some time MySQL is has become very sluggish.
>> When I look at my processlist it shows more than 25 processes (sometimes
>> of the same & table) with status "Opening tables". Some processes also
>> show "closing tables".
>> Since I am running I shared hosting environment, I can not examine the
>> situation to such extent, where I could see if table locks are
>> involved. But it does not seem to be the case, since it does not appear
>> for just some users, but for everybody. Also when I run a simple select
>> query it takes more than 2 sec.
>>
>> I guess MySQL is trying to tell me, that my hard drive is too slow. How
>> could I ease this situation ?
>> My key_buffer is set to 1Gb of my 4Gb system memory. Other values are
>> tweaked as well, but I do not think the matter in that case.
>> Would partitioning the disk as Raid 0 ease the situation?
>>
>> What other places do I have to look at, to further narrow down the problem?
>>
>> Regards,
>> Samy
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
