Hi, I am trying to setup a new MySQL server where I am working. The old one was on a machine with Apache/PHP4 and worked fine but recently MySQL was placing a big load on this machine so we decided to move it to its own server. I've set it up fine on a machine with Red Hat 7, MySQL 3.23.32 and it all works fine locally. SOME queries work fine from the web server to the new MySQL server and go through instantly, but others seem to be hanging. I've been testing it with the most intensive database we had on the old system. When you execute a large query on the old server ( Red Hat 6.2 / MySQL 3.22.32 ), if you watched the processlist the query was working away for about 10 seconds ( you could see the info field changing working through the query ) and then it would return the results. On the new server however, the queries seem to hang and sit in sleep mode. If you watch the processlist very carefully they are executing one 'line' of the query but then jumping back into sleep mode. Some of these large queries will eventually complete but more often they just time out. Other, simpler queries work fine on both the new and old MySQL servers. The large queries I've been using are just large select type statements, theres a couple of examples below. The queries are nested in the PHP because I need to perform tests/actions on the results from the first level of results before generating the next level. SELECT memcats.MemberNumber FROM memcats,members WHERE memcats.Category='".$category."' AND memcats.MemberNumber=members.MemberNumber ORDER BY members.Plan DESC,members.Name SELECT * FROM members WHERE MemberNumber=".$crow['MemberNumber']." AND ACTIVE=1 ORDER BY Name SELECT State,RegionID,Locality FROM regionpcodes WHERE Postcode=".$row['Postcode'] SELECT Region FROM regions WHERE ID=".$prow['RegionID'] Heres a sample processlist: Id User Host db Command Time State Info 4 root localhost Sleep 5409 34 root www.acenet.com.au ccare Sleep 4 35 root www.acenet.com.au ccare Sleep 0 36 root www.acenet.com.au mysql Query 0 SHOW PROCESSLIST Numbers 34 and 35 are examples of this, every few seconds they will reset their 'time' field value as well, this seems to be when they execute one step of the actual query. Heres the system variables: Variable_name Value ansi_mode OFF back_log 50 basedir / binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir /var/lib/mysql/ 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_innobase NO have_isam YES have_raid NO have_ssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 67104768 language /usr/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_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 1047552 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file /var/lib/mysql/mysql.acenet.com.au.pid port 3306 protocol_version 10 record_buffer 8384512 query_buffer_size 0 safe_show_database OFF server_id 0 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 0 socket /var/lib/mysql/mysql.sock sort_buffer 33554424 table_cache 512 table_type MYISAM thread_cache_size 0 thread_stack 65536 timezone EST tmp_table_size 1048576 tmpdir /tmp/ version 3.23.32 wait_timeout 300 And heres the runtime info: Variable_name Value Aborted_clients 0 Aborted_connects 0 Bytes_received 1170124 Bytes_sent 1701389 Connections 43 Created_tmp_disk_tables 0 Created_tmp_tables 1 Created_tmp_files 0 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_delete 0 Handler_read_first 2 Handler_read_key 9873 Handler_read_next 8967 Handler_read_prev 0 Handler_read_rnd 1501 Handler_read_rnd_next 9361243 Handler_update 0 Handler_write 838 Key_blocks_used 46 Key_read_requests 20693 Key_reads 46 Key_write_requests 0 Key_writes 0 Max_used_connections 4 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables 9 Open_files 16 Open_streams 0 Opened_tables 15 Questions 28688 Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 4587 Slave_running OFF Slave_open_temp_tables 0 Slow_launch_threads 42 Slow_queries 0 Sort_merge_passes 0 Sort_range 1 Sort_rows 1501 Sort_scan 8 Threads_cached 0 Threads_created 42 Threads_connected 4 Threads_running 1 Uptime 6432 All this is happening over TCP/IP, with PHP4 code using mysql_connect(). The code all worked fine on the last server. Oh, I tried downgrading the new MySQL server back to 3.22 but exactly the same problem kept occuring so I brought it back up to 3.23. If anyone can offer any suggestions that would be brilliant. Thanks, Ben Jones.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> <META content="MSHTML 5.50.4207.2601" name=GENERATOR></HEAD> <BODY style="MARGIN-TOP: 2px; FONT: 8pt MS Sans Serif; MARGIN-LEFT: 2px"> <DIV><FONT size=1>Hi,</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>I am trying to setup a new MySQL server where I am working. The old one was on a machine with Apache/PHP4 and worked fine but recently MySQL was placing a big load on this machine so we decided to move it to its own server.</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>I've set it up fine on a machine with Red Hat 7, MySQL 3.23.32 and it all works fine locally. SOME queries work fine from the web server to the new MySQL server and go through instantly, but others seem to be hanging.</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>I've been testing it with the most intensive database we had on the old system. When you execute a large query on the old server ( Red Hat 6.2 / MySQL 3.22.32 ), if you watched the processlist the query was working away for about 10 seconds ( you could see the info field changing working through the query ) and then it would return the results. On the new server however, the queries seem to hang and sit in sleep mode. If you watch the processlist very carefully they are executing one 'line' of the query but then jumping back into sleep mode. Some of these large queries will eventually complete but more often they just time out.</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>Other, simpler queries work fine on both the new and old MySQL servers.</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>The large queries I've been using are just large select type statements, theres a couple of examples below. The queries are nested in the PHP because I need to perform tests/actions on the results from the first level of results before generating the next level.</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>SELECT memcats.MemberNumber FROM memcats,members WHERE memcats.Category='".$category."' AND memcats.MemberNumber=members.MemberNumber ORDER BY members.Plan DESC,members.Name</FONT></DIV> <DIV><FONT size=1></FONT> </DIV> <DIV><FONT size=1>SELECT * FROM members WHERE MemberNumber=".$crow['MemberNumber']." AND ACTIVE=1 ORDER BY Name</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>SELECT State,RegionID,Locality FROM regionpcodes WHERE Postcode=".$row['Postcode']</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>SELECT Region FROM regions WHERE ID=".$prow['RegionID']</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>Heres a sample processlist:</FONT></DIV> <DIV> </DIV> <DIV><FONT size=1>Id User Host db Command Time State Info <BR>4 root localhost Sleep 5409<BR>34 root <A href="http://www.acenet.com.au">www.acenet.com.au</A> ccare Sleep 4<BR>35 root <A href="http://www.acenet.com.au">www.acenet.com.au</A> ccare Sleep 0<BR>36 root <A href="http://www.acenet.com.au">www.acenet.com.au</A> mysql Query 0 SHOW PROCESSLIST </FONT></DIV><FONT size=1> <DIV><BR>Numbers 34 and 35 are examples of this, every few seconds they will reset their 'time' field value as well, this seems to be when they execute one step of the actual query.</DIV> <DIV> </DIV> <DIV>Heres the system variables:</DIV> <DIV> </DIV> <DIV>Variable_name Value <BR>ansi_mode OFF <BR>back_log 50 <BR>basedir / <BR>binlog_cache_size 32768 <BR>character_set latin1 <BR>character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 <BR>concurrent_insert ON <BR>connect_timeout 5 <BR>datadir /var/lib/mysql/ <BR>delay_key_write ON <BR>delayed_insert_limit 100 <BR>delayed_insert_timeout 300 <BR>delayed_queue_size 1000 <BR>flush OFF <BR>flush_time 0 <BR>have_bdb NO <BR>have_gemini NO <BR>have_innobase NO <BR>have_isam YES <BR>have_raid NO <BR>have_ssl NO <BR>init_file <BR>interactive_timeout 28800 <BR>join_buffer_size 131072 <BR>key_buffer_size 67104768 <BR>language /usr/share/mysql/english/ <BR>large_files_support ON <BR>locked_in_memory OFF <BR>log OFF <BR>log_update OFF <BR>log_bin OFF <BR>log_slave_updates OFF <BR>long_query_time 10 <BR>low_priority_updates OFF <BR>lower_case_table_names 0 <BR>max_allowed_packet 1048576 <BR>max_binlog_cache_size 4294967295 <BR>max_connections 100 <BR>max_connect_errors 10 <BR>max_delayed_threads 20 <BR>max_heap_table_size 16777216 <BR>max_join_size 4294967295 <BR>max_sort_length 1024 <BR>max_tmp_tables 32 <BR>max_write_lock_count 4294967295 <BR>myisam_recover_options OFF <BR>myisam_sort_buffer_size 8388608 <BR>net_buffer_length 1047552 <BR>net_read_timeout 30 <BR>net_retry_count 10 <BR>net_write_timeout 60 <BR>open_files_limit 0 <BR>pid_file /var/lib/mysql/mysql.acenet.com.au.pid <BR>port 3306 <BR>protocol_version 10 <BR>record_buffer 8384512 <BR>query_buffer_size 0 <BR>safe_show_database OFF <BR>server_id 0 <BR>skip_locking ON <BR>skip_networking OFF <BR>skip_show_database OFF <BR>slow_launch_time 0 <BR>socket /var/lib/mysql/mysql.sock <BR>sort_buffer 33554424 <BR>table_cache 512 <BR>table_type MYISAM <BR>thread_cache_size 0 <BR>thread_stack 65536 <BR>timezone EST <BR>tmp_table_size 1048576 <BR>tmpdir /tmp/ <BR>version 3.23.32 <BR>wait_timeout 300 </DIV> <DIV><BR>And heres the runtime info:</DIV> <DIV> </DIV> <DIV>Variable_name Value <BR>Aborted_clients 0 <BR>Aborted_connects 0 <BR>Bytes_received 1170124 <BR>Bytes_sent 1701389 <BR>Connections 43 <BR>Created_tmp_disk_tables 0 <BR>Created_tmp_tables 1 <BR>Created_tmp_files 0 <BR>Delayed_insert_threads 0 <BR>Delayed_writes 0 <BR>Delayed_errors 0 <BR>Flush_commands 1 <BR>Handler_delete 0 <BR>Handler_read_first 2 <BR>Handler_read_key 9873 <BR>Handler_read_next 8967 <BR>Handler_read_prev 0 <BR>Handler_read_rnd 1501 <BR>Handler_read_rnd_next 9361243 <BR>Handler_update 0 <BR>Handler_write 838 <BR>Key_blocks_used 46 <BR>Key_read_requests 20693 <BR>Key_reads 46 <BR>Key_write_requests 0 <BR>Key_writes 0 <BR>Max_used_connections 4 <BR>Not_flushed_key_blocks 0 <BR>Not_flushed_delayed_rows 0 <BR>Open_tables 9 <BR>Open_files 16 <BR>Open_streams 0 <BR>Opened_tables 15 <BR>Questions 28688 <BR>Select_full_join 0 <BR>Select_full_range_join 0 <BR>Select_range 0 <BR>Select_range_check 0 <BR>Select_scan 4587 <BR>Slave_running OFF <BR>Slave_open_temp_tables 0 <BR>Slow_launch_threads 42 <BR>Slow_queries 0 <BR>Sort_merge_passes 0 <BR>Sort_range 1 <BR>Sort_rows 1501 <BR>Sort_scan 8 <BR>Threads_cached 0 <BR>Threads_created 42 <BR>Threads_connected 4 <BR>Threads_running 1 <BR>Uptime 6432 </DIV> <DIV><BR>All this is happening over TCP/IP, with PHP4 code using mysql_connect(). The code all worked fine on the last server.</DIV> <DIV> </DIV> <DIV>Oh, I tried downgrading the new MySQL server back to 3.22 but exactly the same problem kept occuring so I brought it back up to 3.23.</DIV> <DIV> </DIV> <DIV>If anyone can offer any suggestions that would be brilliant.</DIV> <DIV> </DIV> <DIV>Thanks,<BR>Ben Jones.</FONT></DIV></BODY></HTML>
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
