UBB.Developers Forums Community Development Programming MySQL and Server Configuration tuning help please

hello,
i help run a very popular motorcycle website that runs UBBThreads 6.1 (currently). we have about 15,000 members with up to 180 users on at the same time.

the dedicated server is a 1ghz athlon with 1.5gig of ram on redhat linux 7.1.2.
we have apache running pretty good.
it is mysql that is killing us.

the POSTS table is 243meg or so. the MESSAGES table is another 11meg, USER table is 27meg+. the index files are 38meg+, 1 meg and 2meg.

here is the software versions:
Server Version: Apache/1.3.27 (Unix) (Red-Hat/Linux) mod_python/2.7.6 Python/1.5.2 mod_ssl/2.8.5 OpenSSL/0.9.6b DAV/1.0.2 PHP/4.1.2 mod_perl/1.24_01 mod_throttle/3.1.2 mysql/3.23.44
this is as far as the "providers" will take it for free.

/etc/my.cnf is set to:
[mysqld]
set-variable = key_buffer=16M
set-variable = key_buffer_size=192M
set-variable = max_allowed_packet=16M
set-variable = sort_buffer=4M
set-variable = max_connections=1024
set-variable = max_connect_errors=9000000
set-variable = table_cache=1200

after 5 days of uptime, here are the stats:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 59 |
| Aborted_connects | 6 |
| Bytes_received | 2335447220 |
| Bytes_sent | 1397482194 |
| Connections | 403370 |
| Created_tmp_disk_tables | 6577 |
| Created_tmp_tables | 360913 |
| Created_tmp_files | 44 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 119192 |
| Handler_read_first | 6904 |
| Handler_read_key | 19357880 |
| Handler_read_next | 4214299655 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 34055208 |
| Handler_read_rnd_next | 68027432 |
| Handler_update | 8347101 |
| Handler_write | 4339292 |
| Key_blocks_used | 148073 |
| Key_read_requests | 244231807 |
| Key_reads | 145220 |
| Key_write_requests | 1411276 |
| Key_writes | 943290 |
| Max_used_connections | 116 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 262 |
| Open_files | 277 |
| Open_streams | 0 |
| Opened_tables | 621 |
| Questions | 5875048 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 22349 |
| Select_range_check | 0 |
| Select_scan | 736760 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 13 |
| Slow_queries | 757 |
| Sort_merge_passes | 7 |
| Sort_range | 1490956 |
| Sort_rows | 58167620 |
| Sort_scan | 708343 |
| Table_locks_immediate | 5280645 |
| Table_locks_waited | 136193 |
| Threads_cached | 0 |
| Threads_created | 403369 |
| Threads_connected | 6 |
| Threads_running | 4 |
| Uptime | 477421 |

i was thinking of adding a few threads_cached and upping the tmp_table_size. however, after 5 days we are now 97meg into swap space and top shows mysqld to be a size of 171M with a rss of 85M.

any suggestions?

Are you able to upgrade the mysql and apache apps? apache and mysql needs updating to apache 1.3.27 and mysql 3.23.54a, lot's of progress has been made since the versions you are running... you could even try mysql 4.0.9, as it's much improved over the 3 series...

Are you able to make changes to the my.cnf?

yes, i can change my.cnf at will that is no problem.

as far as app updates, well, that is a sticky situation. it is unclear from the "providers" if we do an upgrade (say to mysql 3.23.54a or is there a .55 now) if we are now soley responsible for all upgrades from then on or not.

a few more stats (so far for january):

Total Hits 1449280
Total Files 612293
Total Pages 27545
Total Visits 7857
Total KBytes 5458391

Total Unique Sites 7630
Total Unique URLs 2208
Total Unique Referrers 953
Total Unique User Agents 1020

. Avg Max

Hits per Hour 20128 55109
Hits per Day 483093 842994
Files per Day 204097 358922
Pages per Day 9181 15562
Visits per Day 2619 4510
KBytes per Day 1819464 3331864

After re-reading your initial post it seems like apache is already updated You would most likely benefit from a newer mysql tho... one of the perks of running newer software that has to be updated often for bugs

Make a backup of your my.cnf and use this one in its place, restarting mysql after you upload it:



let me know how it goes

allen,
would you care to share why you suggest some of these changes?

why make the sort_buffer smaller?
why make the table_cache smaller?
why would you go from zero to 64 on the thread_cache?
why lower the max_connect_errors so much? (why would it be that big to begin with?)
why not up the key_buffer size?
why lower the key_buffer size? (to prevent swapping?)
does having a key_buffer in both isamck and myisamchk set it differently that what is default?
why set the sort buffer so big in those 2 sections?
why not set a tmp_table_cache bigger (32M default) to prevent tmp tables being created on disk?

yes, i'm just full of questions.
thanks,
steve

ok, gimme a minute for the reasons, I remember reading why, just don't have them at hand

sure thing.
i put the above into effect at 7:30am texas time today.
we'll see what happens with it.

here is the latest results:

| Aborted_clients | 1 |
| Aborted_connects | 21 |
| Bytes_received | 2613151148 |
| Bytes_sent | 2266523295 |
| Connections | 413036 |
| Created_tmp_disk_tables | 7657 |
| Created_tmp_tables | 405199 |
| Created_tmp_files | 32 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 133462 |
| Handler_read_first | 7868 |
| Handler_read_key | 22184759 |
| Handler_read_next | 626299359 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 39839278 |
| Handler_read_rnd_next | 78656291 |
| Handler_update | 9783403 |
| Handler_write | 4815758 |
| Key_blocks_used | 15582 |
| Key_read_requests | 286402767 |
| Key_reads | 22068 |
| Key_write_requests | 1570695 |
| Key_writes | 1048930 |
| Max_used_connections | 107 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 180 |
| Open_files | 197 |
| Open_streams | 0 |
| Opened_tables | 186 |
| Questions | 6540514 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 26186 |
| Select_range_check | 0 |
| Select_scan | 824917 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 918 |
| Sort_merge_passes | 16 |
| Sort_range | 1674266 |
| Sort_rows | 68746002 |
| Sort_scan | 794258 |
| Table_locks_immediate | 5871751 |
| Table_locks_waited | 196010 |
| Threads_cached | 33 |
| Threads_created | 108 |
| Threads_connected | 31 |
| Threads_running | 2 |
| Uptime | 519157 |

i had made a few changes from what you suggested.
i changed max_connections to 256 instead of the 500.
i am concerned about the created_tmp_tables and created_tmp_disk_tables.

at day 4 we hit a little of swap space. 1 meg worth.
at day 5 we got in a little more to 4meg. it is holding at 4meg worth of swap now at day 6. however, mysqld went from 31meg to 38meg last night.

here is what i am planning on going to for the next reboot:

[mysqld]
socket=/var/lib/mysql/mysql.sock
skip-innodb
skip-locking
set-variable = max_connections=128
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=4M
set-variable = table_cache=256
set-variable = thread_cache_size=32
set-variable = thread_stack=128K
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=4M
set-variable = write_buffer=4M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=4M
set-variable = write_buffer=4M

less max_connections because i am not getting over 100.
less table_cache because i'm not getting that many open.
halve the threads because i rarely see more than 30 open.
however, i will double each threads stack size.
double the size of the record,join and sort buffers to maybe not create temp tables.

thoughts?

It looks like you are working toward what works for your setup... good luck and please share your results in a day or so

allen,
does it look like i'm actually headed in the RIGHT direction or just merely in some direction?

your thoughts please.

I found this article about tuning your my.cnf... it has a lot of great advice.

http://www.databasejournal.com/features/mysql/article.php/10897_1402311_2

yes, i read it last week.
that is partly the reason i asked my question about persistent connections.
there, you said it was bad. yet the article says they can be good. are you changing your opinion?

I see that they suggest using persistant connections, but I still have to disagree with that part.

WHY do you disagree?
can you site some examples please?

Persistant connections are great for sites that are not under heavy load. (many users online at once) The persistant connection will be faster as it's not dropped and can be continued to be used by the visitor. I believe the max is 200 ? for persistant connections to MySQL but I could be wrong. If you have 250 users online some will get the error "to many connections" from the database. If you don't use persistant connections then as soon as a user gets their information the connection is dropped and is then available to another user. This is what helps the greatest on busy sites.

here is the data from 9 days of runtime:

| Aborted_clients | 1 |
| Aborted_connects | 0 |
| Bytes_received | 456633659 |
| Bytes_sent | 2910679325 |
| Connections | 753674 |
| Created_tmp_disk_tables | 13091 |
| Created_tmp_tables | 674493 |
| Created_tmp_files | 24 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 219916 |
| Handler_read_first | 14286 |
| Handler_read_key | 37732812 |
| Handler_read_next | 3996857474 |
| Handler_read_prev | 11 |
| Handler_read_rnd | 68586690 |
| Handler_read_rnd_next | 139301674 |
| Handler_update | 17083638 |
| Handler_write | 8468109 |
| Key_blocks_used | 7793 |
| Key_read_requests | 477691831 |
| Key_reads | 203820 |
| Key_write_requests | 2618089 |
| Key_writes | 1778777 |
| Max_used_connections | 110 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 272 |
| Open_streams | 0 |
| Opened_tables | 266 |
| Questions | 11062801 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 41775 |
| Select_range_check | 0 |
| Select_scan | 1379287 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1 |
| Slow_queries | 1667 |
| Sort_merge_passes | 12 |
| Sort_range | 2836750 |
| Sort_rows | 119875080 |
| Sort_scan | 1326145 |
| Table_locks_immediate | 9726194 |
| Table_locks_waited | 484283 |
| Threads_cached | 62 |
| Threads_created | 199 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 775778 |

and the my.cnf that is running:
[mysqld]
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=128
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=4M
set-variable = table_cache=256
set-variable = thread_cache_size=64
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=4M
set-variable = write_buffer=4M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=4M
set-variable = write_buffer=4M

any thoughts/ideas/laughs?

Have you ever thought of logging the slow queries into a textfile?
I assume that they are mostly search-queries over all forums, but logging them would help a lot.

no.
mainly because i have NO idea how or even that you could.
would you enlighten me?

Isn't very complicated.
See this chapter of the mysql documentation.

here is what i changed in my.cnf, then rebooted.

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-slow-queries=/var/log/mysqlslow.log

i have one slow log and it did NOT get put in the new log file. what did i mess up?

You also need to start the mysql daemon with --log-slow-queries for it to work.

so do i need to dupe those 3 lines into
[mysqld]

stanza, correct?

Yeah, that is probably necessary.

i did the following:
[mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-slow-queries=/var/log/mysqlslow.log

the database wouldn't start. ack!

so i changed it to:
[mysqld]
err-log=/var/log/mysqld.log
log-slow-queries=/var/log/mysqlslow.log

still wouldn't start.

so i nuked it all and went back to without it.

what is the correct syntax for my.cnf?

I just added one line in the mysqld section:

log-slow-queries = /var/lib/mysql/slow.query

and it works fine.

pid-file=/var/run/mysqld/mysqld.pid

This is a required line...