Wanna analyze my SQL report? (overgrow)

[Overgrow]

I was about to post the exact same topic line, so since Turbo seems to have it settled, I'll just tag on to his thread. This is a new mySQL server, never been changed from the default settings. It seems to run well even with 330 people online, but in the past few days it has been hanging once every few hours for a total of a minute or two.. then it comes back without any help. When it first went online the slow queries were non-existant, but they have appeared in the past few days.

Hardware (just mySQL, Apache is separate):

Dual-P/1000, Intel server board
1 gig RAM
20 gig SCSI



| ansi_mode | OFF
| back_log | 50
| basedir | /usr/local/mysql/
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 dec8 etc.... |
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /usr/local/mysql/var/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | NO
| have_gemini | NO
| have_innodb | NO
| have_isam | YES
| have_raid | NO
| have_ssl | NO
| init_file |
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/local/mysql/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_recover_options | OFF
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /usr/local/mysql/var/dank.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /tmp/mysql.sock
| sort_buffer | 2097144
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | PDT
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 3.23.39
| wait_timeout | 28800

| Aborted_clients | 409 |
| Aborted_connects | 3792 |
| Bytes_received | 1502376711 |
| Bytes_sent | 4128859310 |
| Connections | 6253847 |
| Created_tmp_disk_tables | 75699 |
| Created_tmp_tables | 500623 |
| Created_tmp_files | 8782 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 409533 |
| Handler_read_first | 263793 |
| Handler_read_key | 204764902 |
| Handler_read_next | 2840170242 |
| Handler_read_prev | 9278910 |
| Handler_read_rnd | 763554560 |
| Handler_read_rnd_next | 2250632126 |
| Handler_update | 9598333 |
| Handler_write | 282435959 |
| Key_blocks_used | 7793 |
| Key_read_requests | 1339912189 |
| Key_reads | 415854 |
| Key_write_requests | 6532423 |
| Key_writes | 4252007 |
| Max_used_connections | 100 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 104 |
| Open_streams | 0 |
| Opened_tables | 113685 |
| Questions | 113552303 |
| Select_full_join | 1593 |
| Select_full_range_join | 0 |
| Select_range | 2226541 |
| Select_range_check | 0 |
| Select_scan | 29788013 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1521 |
| Slow_queries | 579 |
| Sort_merge_passes | 4391 |
| Sort_range | 4130884 |
| Sort_rows | 2180312505 |
| Sort_scan | 4465196 |
| Table_locks_immediate | 107630188 |
| Table_locks_waited | 898269 |
| Threads_cached | 0 |
| Threads_created | 6253846 |
| Threads_connected | 18 |
| Threads_running | 5 |
| Uptime | 9863344 |


umm where would my.cnf be under redhat7.1?


Thanks!

[Overgrow]

Well its gotten worse... so I took the suggestions made for Turbo and used them on my server. I made a new my.cnf and uploaded it to /etc with my appropriate file variables.

Can you make any suggestions-- is there anything I should change to that above my.cnf considering that I have twice the RAM and 300 people online?

thanks

[eva2000]

for yours you could probably use my settings that i use with a bit of a increase.. and yes /etc/my.cnf is the right location

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=500
set-variable = key_buffer=32M
set-variable = join_buffer=5M
set-variable = record_buffer=5M
set-variable = sort_buffer=8M
set-variable = table_cache=1024
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache_size=256

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=8M
set-variable = write_buffer=8M
restart mysql once this is done

[Overgrow]

thank you thank you thank you

The first my.cnf I used helped greatly. It stopped the hangs.. I just installed the second recommended my.cnf and we'll see how that does.

I can't thank you enough eva!

[eva2000]

Originally posted by Overgrow
thank you thank you thank you

The first my.cnf I used helped greatly. It stopped the hangs.. I just installed the second recommended my.cnf and we'll see how that does.

I can't thank you enough eva! no probs.. just remember extended-status output (http://vbulletin.com/forum/showthread.php?threadid=3477) and 'top' are the best things to monitor :)

[Overgrow]

My searchindex table (17m records) is causing an entire site slowdown. Whenever anyone searches or deletes a post, this table must be copied and the entire dB server hangs for the process. It seems like this table has gotten too big for the current RAM (1 gig). Server stats:

Dual P/1000
1 gig ram
20 gig SCSI Raid

Is there anything I can change to the above settings (using that same my.cnf you suggested eva) that could help? Our load has increased significantly since the last post, average of 500 on at a time now with 650+ at peak.

I've disabled searches for now, is there any setting that could help this problem or do I need to add another gig of RAM to the server? thanks......

[Overgrow]

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 107 |
| Aborted_connects | 20 |
| Bytes_received | 75526690 |
| Bytes_sent | 2941751243 |
| Connections | 1273249 |
| Created_tmp_disk_tables | 45311 |
| Created_tmp_tables | 868865 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 496118 |
| Handler_read_first | 3901869 |
| Handler_read_key | 1170749814 |
| Handler_read_next | 3377890974 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1142419270 |
| Handler_read_rnd_next | 1049074173 |
| Handler_update | 5222936 |
| Handler_write | 385185530 |
| Key_blocks_used | 31168 |
| Key_read_requests | 406774978 |
| Key_reads | 4571666 |
| Key_write_requests | 1979996 |
| Key_writes | 1731482 |
| Max_used_connections | 286 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 |
| Open_files | 17583 |
| Open_streams | 0 |
| Opened_tables | 39286 |
| Questions | 62191454 |
| Select_full_join | 1108 |
| Select_full_range_join | 0 |
| Select_range | 8780244 |
| Select_range_check | 0 |
| Select_scan | 11880475 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 25788 |
| Sort_merge_passes | 0 |
| Sort_range | 9548565 |
| Sort_rows | 2475974877 |
| Sort_scan | 8880907 |
| Table_locks_immediate | 67708991 |
| Table_locks_waited | 1595427 |
| Threads_cached | 219 |
| Threads_created | 287 |
| Threads_connected | 60 |
| Threads_running | 1 |
| Uptime | 953032 |
+--------------------------+------------+

[eva2000]

Originally posted by Overgrow
My searchindex table (17m records) is causing an entire site slowdown. Whenever anyone searches or deletes a post, this table must be copied and the entire dB server hangs for the process. It seems like this table has gotten too big for the current RAM (1 gig). Server stats:

Dual P/1000
1 gig ram
20 gig SCSI Raid

Is there anything I can change to the above settings (using that same my.cnf you suggested eva) that could help? Our load has increased significantly since the last post, average of 500 on at a time now with 650+ at peak.

I've disabled searches for now, is there any setting that could help this problem or do I need to add another gig of RAM to the server? thanks...... looks like your hitting the table locking issue outlined here http://vbulletin.com/forum/showthread.php?s=&threadid=37174 with possible solutions

1. i'd upgrade to 2.2.3b since there have been improvements between 2.2.1 and 2.2.3b regarding the searching function

[Overgrow]

Thanks eva,

>>outlined here threadid=37174 with possible solutions

I have been following that thread for a while. Here is what I summarize as the possible solutions:

-use Chen's hack to disable search under high load

-use neo's hack (http://www.vbulletin.org/forum/showthread.php?threadid=35583) or similar to limit the # of searches.. this isn't quite where it needs to be yet since it doesn't track guest searches nor does it really help when one search shuts things down, not multiple.

-people claim this thread (http://www.vbulletin.com/forum/showthread.php?s=&threadid=39587) will reduce the size of searchindex but it did not for me.. it removed the badwords but did not reduce the table size and I did re-index.

-part of my hangs also come from deleting posts when it removes them from searchindex. I also admin some sites running 2.2.2 so looking at that code, I saw that it does not seem to remove posts from searchindex when deleting.. I posted a question here (http://www.vbulletin.com/forum/showthread.php?threadid=40198) but got no response.

future solutions?

-mySQL 4 row-level locking
-Innobase or some other table solution

real world fixes?

-put another gig of RAM in the box. Since this just started happening I assume that the index is just too large for available memory now. Whenever I check the status of the hung dB server, the active process is "copying to tmp table" with searchindex. With more memory it wouldn't have to use a tmp table, right?

[Overgrow]

I took your suggestion of 2.2.3b code.. I looked over it line by line and compared it with my 2.2.1 search. I moved all of my search customizations (about 10!) into the 2.2.3b code and there was not one difference between the two.. If there are improvements in the search function in 2.2.3b it must be somewhere else (functions? global?)...

You have to realize how much custom code I have though, it would take many many hours for a full upgrade.. I thought I waited long enough in the 2.x line.