Per B
Thu 12th Apr '01, 11:24am
Yeah, I know this topic has been up millions of times. I've searched for an answer but didn't find anything that could apply to my problem.
I have two dual-P3/933 with 1 GB mem each. One of them is running Apache 1.3.17 and vBulletin 2.0beta5, and the other is running MySQL 3.23.36 (MyISAM is used).
Now and then we come to a point where the forums hangs and I have to restart MySQL (even though 'mysqladmin flush-host' + 'mysqladmin flush-tables' have worked some times.
I've been lucky to watch 'top' and the forum when this happens: the forums where really fast, and the DB server had an idle time of between 20 and 60 % (didn't check load average). From 'mysqladmin extended-status' I can see that although I have aprox 150 connections, only a few threads are actually running. 200+ users online, even though we have seen number around 700.
The all of a sudden, everything locks up. After a while I get the result from 'mysqladmin extended-status' .....
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16081 |
| Aborted_connects | 6 |
| Bytes_received | 258903132 |
| Bytes_sent | 39173956 |
| Connections | 16850 |
| Created_tmp_disk_tables | 91 |
| Created_tmp_tables | 26392 |
| Created_tmp_files | 2 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 53 |
| Handler_delete | 11311 |
| Handler_read_first | 31822 |
| Handler_read_key | 170282450 |
| Handler_read_next | 1082522595 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 25858495 |
| Handler_read_rnd_next | 1298557884 |
| Handler_update | 126755 |
| Handler_write | 5012656 |
| Key_blocks_used | 62341 |
| Key_read_requests | 569542951 |
| Key_reads | 305273 |
| Key_write_requests | 54397 |
| Key_writes | 50454 |
| Max_used_connections | 151 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 262 |
| Open_files | 203 |
| Open_streams | 0 |
| Opened_tables | 6735 |
| Questions | 1560806 |
| Select_full_join | 156 |
| Select_full_range_join | 0 |
| Select_range | 369487 |
| Select_range_check | 0 |
| Select_scan | 213171 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 350 |
| Slow_queries | 1402 |
| Sort_merge_passes | 1 |
| Sort_range | 458526 |
| Sort_rows | 27483351 |
| Sort_scan | 84082 |
| Table_locks_immediate | 1777224 |
| Table_locks_waited | 16790 |
| Threads_cached | 0 |
| Threads_created | 16849 |
| Threads_connected | 147 |
| Threads_running | 134 |
| Uptime | 63323 |
+--------------------------+------------+
..... and from 'top'
4:02pm up 42 days, 23:04, 1 user, load average: 78.95, 68.39, 47.51 196 processes: 159 sleeping, 37 running, 0 zombie, 0 stopped
CPU0 states: 78.1% user, 21.12% system, 0.0% nice, 0.0% idle
CPU1 states: 75.6% user, 24.6% system, 0.2% nice, 0.0% idle
Mem: 906424K av, 863748K used, 42676K free, 39904K shrd, 114940K buff
Swap: 1052216K av, 4232K used, 1047984K free 562780K cached
So it seems that something happens that consumes a lot of CPU, leading to a lot of MySQL queries queing up and causing a complete lock. What could it be causing this ........ ?
This is the variables I set in /etc/my.cnf
set-variable = key_buffer=64M
set-variable = sort_buffer=1M
set-variable = max_allowed_packet=1M
set-variable = table_cache=768
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = thread_concurrency=4
set-variable = max_connections=400
set-variable = wait_timeout=60
I have experimented with different values on most of this without any success.
A little more data:
around 4 000 posts/day
around 200 000 pageviews/day
vBulletin DB is more the 250 MB on disk
around 240 000 posts in total
I haven' done any optimization of the tables.... should I do that.
Originally I imported a lot of posts from our old forum using the 'Phorum' system. This was done be a script I wrote my self.
Any clue??? Any help much appreciated!!!!!!!
/Per, MGON
Our forums are at MGOForum (http://www.mgoforum.com)
I have two dual-P3/933 with 1 GB mem each. One of them is running Apache 1.3.17 and vBulletin 2.0beta5, and the other is running MySQL 3.23.36 (MyISAM is used).
Now and then we come to a point where the forums hangs and I have to restart MySQL (even though 'mysqladmin flush-host' + 'mysqladmin flush-tables' have worked some times.
I've been lucky to watch 'top' and the forum when this happens: the forums where really fast, and the DB server had an idle time of between 20 and 60 % (didn't check load average). From 'mysqladmin extended-status' I can see that although I have aprox 150 connections, only a few threads are actually running. 200+ users online, even though we have seen number around 700.
The all of a sudden, everything locks up. After a while I get the result from 'mysqladmin extended-status' .....
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16081 |
| Aborted_connects | 6 |
| Bytes_received | 258903132 |
| Bytes_sent | 39173956 |
| Connections | 16850 |
| Created_tmp_disk_tables | 91 |
| Created_tmp_tables | 26392 |
| Created_tmp_files | 2 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 53 |
| Handler_delete | 11311 |
| Handler_read_first | 31822 |
| Handler_read_key | 170282450 |
| Handler_read_next | 1082522595 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 25858495 |
| Handler_read_rnd_next | 1298557884 |
| Handler_update | 126755 |
| Handler_write | 5012656 |
| Key_blocks_used | 62341 |
| Key_read_requests | 569542951 |
| Key_reads | 305273 |
| Key_write_requests | 54397 |
| Key_writes | 50454 |
| Max_used_connections | 151 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 262 |
| Open_files | 203 |
| Open_streams | 0 |
| Opened_tables | 6735 |
| Questions | 1560806 |
| Select_full_join | 156 |
| Select_full_range_join | 0 |
| Select_range | 369487 |
| Select_range_check | 0 |
| Select_scan | 213171 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 350 |
| Slow_queries | 1402 |
| Sort_merge_passes | 1 |
| Sort_range | 458526 |
| Sort_rows | 27483351 |
| Sort_scan | 84082 |
| Table_locks_immediate | 1777224 |
| Table_locks_waited | 16790 |
| Threads_cached | 0 |
| Threads_created | 16849 |
| Threads_connected | 147 |
| Threads_running | 134 |
| Uptime | 63323 |
+--------------------------+------------+
..... and from 'top'
4:02pm up 42 days, 23:04, 1 user, load average: 78.95, 68.39, 47.51 196 processes: 159 sleeping, 37 running, 0 zombie, 0 stopped
CPU0 states: 78.1% user, 21.12% system, 0.0% nice, 0.0% idle
CPU1 states: 75.6% user, 24.6% system, 0.2% nice, 0.0% idle
Mem: 906424K av, 863748K used, 42676K free, 39904K shrd, 114940K buff
Swap: 1052216K av, 4232K used, 1047984K free 562780K cached
So it seems that something happens that consumes a lot of CPU, leading to a lot of MySQL queries queing up and causing a complete lock. What could it be causing this ........ ?
This is the variables I set in /etc/my.cnf
set-variable = key_buffer=64M
set-variable = sort_buffer=1M
set-variable = max_allowed_packet=1M
set-variable = table_cache=768
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = thread_concurrency=4
set-variable = max_connections=400
set-variable = wait_timeout=60
I have experimented with different values on most of this without any success.
A little more data:
around 4 000 posts/day
around 200 000 pageviews/day
vBulletin DB is more the 250 MB on disk
around 240 000 posts in total
I haven' done any optimization of the tables.... should I do that.
Originally I imported a lot of posts from our old forum using the 'Phorum' system. This was done be a script I wrote my self.
Any clue??? Any help much appreciated!!!!!!!
/Per, MGON
Our forums are at MGOForum (http://www.mgoforum.com)