[dev] shares performance hit (was Re: disable shares)

[Didi Rieder]

Quoting duck <duck at obala.net>:

> On Tue, 2008-06-10 at 14:23 +0200, Didi Rieder wrote:
>> Well now (2 PM) there are about 1,000 active user sessions, mysql is
>> running with 10-20 threads. mytop shows that almost all the vislible
>> queries shares queries. I the moment when the machine produced the
>> slow queries hundreds of threads were running. I also noticed that I
>> have to increase the max number of allowed open files.
>
> With this amount of data and traffic you should not increase the number
> of open files. As this is a symptom of missconfig. First enlarge
> the tmp_table_size. Myql server creates internal temporary tables while
> processing queries. If an in-memory temporary table exceeds the limit,
> MySQL automatically converts it to an on-disk MyISAM table. This must be
> avoided as mush as possible. With all your ram this should not be a
> problem. By default is 32MB. Bring it up to 200M.Then force mysql to
> have more threads by default (let say thread_cache_size = 50). So in
> average you always have a thread fee waiting for a new connection.

Here is my mySQL configuration:

------8<-----
skip-external-locking
skip-thread-priority
key_buffer = 64M
max_connections = 1024
max_connect_errors = 1000
max_allowed_packet = 8M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
tmp_table_size= 256M
thread_concurrency = 12

wait_timeout = 30
interactive_timeout = 30

log_slow_queries
------8<-----

So the only thing left would be the "thread_cache_size" which is  
smaller then you are suggesing. I'll increase it and see if it helps.

Didi

-- 
-------------------------
Didi Rieder
adrieder at sbox.tugraz.at
PGPKey ID: 3431D0B0
-------------------------