Tuning MySQL

[=69.GIAP=TUSHKA]

Our Kiev Liberation campaign (69.GIAP vs K9 Sqdns) has become so popular that our server is getting overloaded after each mission when the pilots attempt to look at the statistics for that mission.

Task Manager shows that the load goes to 100% mysqld-nt.exe for a minute or so for each mission stats page that is requested, which seems excessive.

This is on XP with core2duo and 2 GB ram.

We can reduce the load to 50% by forcing it to run single-threaded, but that seems like a sub-optimal configuration.

Does anyone have any advice on tuning MySQL so it doesn't bog down the server with such requests?

Is there some way to get MySQL to cache its results so a second request for the same data doesn't need to look up the data a second time? (I thought the "query_cache_size" variable would do the trick... but it doesn't seem to help much if at all.)

Is there some way to get MP to create static pages rather than refreshing them on each access?

I've only been playing with this for a few hours, so I've most likely overlooked something trivial. I'm no MySQL or SEOW guru.

Thanks for any practical advice you can give! Especially numbers that work for all the fiddly settings.

[22GCT_Gross]

Hello Tushka,

this is my configuration (but don't ask me further onfos, I'm not expert:) )

Code: Select all

[mysqld]
...
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
net_buffer_length = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

lower_case_table_names=2 

[mysqldump]
quick
max_allowed_packet = 64M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[IV/JG7_4Shades]

Hi Tushka,

You need to check your query caching setting on your MySQL server. The bigger the better. Gross's example above is 32 MB. I'd suggest doubling that or more.

It is also a good idea to Check and Optimize all tables in your DB occasionally. This often improves performance.

There is no static version of the Statistics pages, but I am sure someone could write a little script to do that at the end of each mission.

Cheers,
4Shades

[=69.GIAP=TUSHKA]

Thank you Gross and 4Shades. Setting query_cache_size=64M did the trick. The first page now loads quickly with little load on the server, and the second call to that page loads even more quickly with even less load on the server. The original setting of query_cache_size was "0"! I had set it to "8" thinking it was number of queries, rather than a memory allocation. Clearly inadequate!

[22GCT_Gross]

Shades,
I'm trying to allocate IL2 process on the core 2

Code: Select all

[rts]
ProcessAffinityMask=2

and MySql process on the core 1. Which is the right value?
Now it's

Code: Select all

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

[=69.GIAP=TUSHKA]

S! Gross,

I think that number is the maximum number of simultaneous threads rather than a directive to use one processor or another. The default is 8, but we are using 4 (for a dual core processor). I've set it to 1 in the past, and that does force it to use a single thread, but with an adequate query cache the load is so limited that it is not a problem for a running IL-2 mission.

[22GCT_Gross]

Many thanks, Richard