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?
