Server Tweak Help: Trying to stop swap usage [Archive]

kontrabass

Thu 23rd Oct '03, 6:05pm

Hello!

I'm trying to figure out why I my server inevitably dips into using swap after a day or so of uptime. My Mysqld processes seem to grow and grow and grow in memory usage, and I have to reboot the server every 2 days at least to keep the swap usage down, otherwise things totally lock up (the server load only goes up to around 8 or 9, but the board is totally unresponsive)

Is this the result of a memory leak? I was running mysql 3.23, and just upgraded to 4.0.16 today - no change.

Do I just need more RAM? Are my my.cnf memory settings too generous? I'd really like to stop the swap usage - things run soooo well after a fresh reboot (0 swap used)

Thanks!

Note: the following TOP output is relatively soon after a reboot, so swap usage isn't that drasitic... yet.

1. is this on dedicated or shared virual server
-dedicated

2. Server specs:
Dual Athlon 1800+
1 Gig RAM
2x 18G SCSI
Redhat 7.2
Mysql 4.0.16
PHP 4.1.2
Apache 1.3.27
MMcache installed

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
NO
4. if possible how mysql was compiled/installed
installed via pre-compiled binaries

5. your top stats

2:45pm up 18:25, 1 user, load average: 1.42, 1.39, 1.20
281 processes: 279 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 20.9% user, 15.26% system, 0.0% nice, 63.26% idle
CPU1 states: 25.20% user, 16.1% system, 0.0% nice, 58.8% idle
Mem: 1028488K av, 1017728K used, 10760K free, 12060K shrd, 6708K buff
Swap: 530104K av, 3844K used, 526260K free 626412K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1164 mysql 9 0 94484 92M 2180 S 8.0 9.1 0:25 mysqld
5668 mysql 9 0 92528 90M 2180 S 7.7 8.9 2:30 mysqld
1166 mysql 9 0 94484 92M 2180 S 6.5 9.1 0:23 mysqld
5663 mysql 14 0 92528 90M 2180 R 4.5 8.9 2:29 mysqld
5557 mysql 9 0 92528 90M 2180 S 4.3 8.9 2:13 mysqld
5604 mysql 9 0 92528 90M 2180 S 2.5 8.9 2:55 mysqld
6135 mysql 9 0 94484 92M 2180 S 2.4 9.1 2:53 mysqld
6504 paul 14 0 1276 1276 856 R 2.0 0.1 0:17 top
5958 nobody 9 0 6968 6780 5392 S 1.4 0.6 0:04 httpd
6971 nobody 9 0 5596 5404 4312 S 1.3 0.5 0:01 httpd
27145 nobody 9 0 8504 7996 6560 S 1.2 0.7 0:38 httpd
7058 nobody 9 0 5848 5656 4436 S 1.2 0.5 0:00 httpd
6875 nobody 9 0 6088 5900 4672 S 1.1 0.5 0:00 httpd
1170 mysql 9 0 94484 92M 2180 S 1.0 9.1 0:33 mysqld
5519 mysql 9 0 92528 90M 2180 S 0.8 8.9 3:23 mysqld
5661 mysql 9 0 92528 90M 2180 S 0.8 8.9 2:22 mysqld
7019 nobody 9 0 5388 5200 4552 S 0.8 0.5 0:00 httpd
5817 nobody 9 0 7404 7216 5836 S 0.7 0.7 0:04 httpd
6969 nobody 9 0 5760 5568 4472 S 0.7 0.5 0:00 httpd

6. your mysql configuration variables located at /etc/my.cnf
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de 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 | 10 |
| convert_character_set | |
| datadir | /mysql/data/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 16777216 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | OFF |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 400 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2458 |
| pid_file | /mysql/data/argus.talkbass.com.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 1024 |
| table_type | MYISAM |
| thread_cache_size | 128 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | MDT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.16-standard |
| wait_timeout | 1800 |

7. your mysql extended-status output

http://www.talkbass.com/mysqlinfo.php

8. oh and is your vB the only thing on the server? or other scripts? sites?
phpNuke, phpAdsNew delivers ads (php-mysql ad delivery)

9. how many average and max concurrent users on your vB forum ?
average = 129
max = 240

10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://www.talkbass.com/phpinfo.php

11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

12. what version of vB are you running ?

2.2.3

Erwin

Thu 23rd Oct '03, 8:58pm

Hi talkbass admin - I'm a member there. ;)

For talkbass, you do need more than 1 Gb Ram - it's a busy forum. We are looking at 2 Gb at least, and if possible, 4 Gb.

Change KeepAlive to Off and see if that helps.

I would upgrade your vB to 2.3.2 - anything below 2.2.9 has documented security loopholes that can be hacked.

eva2000

Fri 24th Oct '03, 12:35am

talkbass 3796K into swap is nothing to worry about even 10 - 25MB into swap is nothing to worry about

Erwin

Fri 24th Oct '03, 11:05am

That's true - I have 4 Gb RAM and I still swap up to 3 Mb. :)

kontrabass

Fri 24th Oct '03, 11:47am

That's true - I have 4 Gb RAM and I still swap up to 3 Mb. :)
Wow. Ok, thanks for the input guys. Looks like more ram is the way to go. Actually if I don't reboot the server every 2 days the swap goes much higher (will go up to 30-40MB). But I guess it's safe to say it's not a mem leak. Time to get the funds scraped together...

I'm honored to have Dr. Erwin Loh as a TalkBasser! :D

kontrabass

Fri 24th Oct '03, 11:21pm

Oh boy, this is really sucking. I only have 100 users online and the site takes 20 seconds to load a page if it loads at all. Users are complaining left and right. Here's some current output:

Extended status:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 9 |
| Aborted_connects | 0 |
| Bytes_received | 201000413 |
| Bytes_sent | 3217662212 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 218041 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 12026 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 5962 |
| Com_insert_select | 77 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 391 |
| Com_replace_select | 1 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 300835 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 200760 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 29 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 230527 |
| Connections | 17268 |
| Created_tmp_disk_tables | 9 |
| Created_tmp_tables | 13396 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5688 |
| Handler_read_first | 13324 |
| Handler_read_key | 9245163 |
| Handler_read_next | 201518881 |
| Handler_read_prev | 361244 |
| Handler_read_rnd | 1273472 |
| Handler_read_rnd_next | 571580354 |
| Handler_rollback | 0 |
| Handler_update | 320590 |
| Handler_write | 4667284 |
| Key_blocks_used | 15586 |
| Key_read_requests | 48912502 |
| Key_reads | 216161 |
| Key_write_requests | 24197 |
| Key_writes | 15697 |
| Max_used_connections | 171 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 807 |
| Open_files | 906 |
| Open_streams | 0 |
| Opened_tables | 813 |
| Questions | 1398807 |
| Qcache_queries_in_cache | 7684 |
| Qcache_inserts | 299970 |
| Qcache_hits | 612200 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 848 |
| Qcache_free_memory | 20288136 |
| Qcache_free_blocks | 3051 |
| Qcache_total_blocks | 18608 |
| Rpl_status | NULL |
| Select_full_join | 6 |
| Select_full_range_join | 0 |
| Select_range | 38583 |
| Select_range_check | 0 |
| Select_scan | 45317 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 95 |
| Sort_merge_passes | 0 |
| Sort_range | 34825 |
| Sort_rows | 2724518 |
| Sort_scan | 16990 |
| Table_locks_immediate | 674000 |
| Table_locks_waited | 9589 |
| Threads_cached | 0 |
| Threads_created | 172 |
| Threads_connected | 172 |
| Threads_running | 129 |
| Uptime | 19752 |

8:17pm up 22:04, 1 user, load average: 5.21, 3.61, 3.07
372 processes: 370 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 36.9% user, 33.13% system, 0.0% nice, 30.7% idle
CPU1 states: 34.14% user, 37.17% system, 0.0% nice, 27.29% idle
Mem: 1028488K av, 1004208K used, 24280K free, 48K shrd, 4556K buff
Swap: 530104K av, 1572K used, 528532K free 486540K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
6847 mysql 9 0 92780 90M 1416 S 10.7 9.0 0:06 mysqld
1753 mysql 9 0 94652 92M 1416 S 7.7 9.2 0:30 mysqld
8619 mysql 9 0 92096 89M 1416 S 7.6 8.9 0:04 mysqld
28674 mysql 9 0 94524 92M 1416 S 7.5 9.1 0:36 mysqld
28986 mysql 9 0 94652 92M 1416 S 7.4 9.2 0:51 mysqld
28624 mysql 9 0 94524 92M 1416 S 6.5 9.1 0:48 mysqld
6845 mysql 9 0 92096 89M 1416 S 5.9 8.9 0:12 mysqld
8564 mysql 9 0 92096 89M 1416 S 5.6 8.9 0:01 mysqld
28640 mysql 9 0 94524 92M 1416 S 4.9 9.1 0:55 mysqld
28706 mysql 9 0 94652 92M 1416 S 3.9 9.2 1:06 mysqld
28705 mysql 9 0 94652 92M 1416 S 3.7 9.2 0:59 mysqld
28633 mysql 9 0 94524 92M 1416 S 3.5 9.1 0:45 mysqld
28649 mysql 9 0 94524 92M 1416 S 3.1 9.1 1:02 mysqld
8644 paul 18 0 1332 1332 856 R 3.0 0.1 0:18 top
28987 mysql 9 0 94652 92M 1416 S 2.7 9.2 0:50 mysqld
28597 mysql 9 0 94524 92M 1416 S 2.5 9.1 1:06 mysqld
1786 mysql 9 0 94652 92M 1416 S 2.5 9.2 0:19 mysqld
1764 mysql 17 0 94652 92M 1416 R 2.1 9.2 0:20 mysqld
30042 nobody 9 0 6468 6468 3244 S 1.9 0.6 1:14 httpd

MYSQL PROCESSES (some): from phpMyAdmin "Show Processes":

Kill 2050 paul localhost talkbassforum Sleep 4 NULL
Kill 2052 paul localhost talkbassforum Sleep 4 NULL
Kill 2054 paul localhost talkbassforum Query 2 Locked SELECT thread.title,thread.thread....
Kill 2055 paul localhost talkbassforum Query 1 Locked SELECT forum.forumid,styleid,st....
Kill 2056 paul localhost talkbassforum Query 0 Locked SELECT * FROM thread WHERE threadid=68119
Kill 2057 paul localhost talkbassforum Query 1 Locked SELECT * FROM thread WHERE threadid=11726
Kill 2061 paul localhost talkbassforum Sleep 1 NULL
Kill 2062 paul localhost talkbassforum Sleep 3 NULL
Kill 2063 paul localhost talkbassforum Sleep 0 NULL
Kill 2064 paul localhost talkbassforum Query 1 Locked SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE
Kill 2065 paul localhost talkbassforum Sleep 4 NULL
Kill 2066 paul localhost talkbassforum Query 1 Locked SELECT forum.forumid,styleid,styleoverri...
Kill 2067 paul localhost talkbassforum Query 1 Locked SELECT
Kill 2068 paul localhost talkbassforum Sleep 4 NULL
Kill 2069 paul localhost talkbassforum Sleep 0 NULL
Kill 2073 paul localhost talkbassforum Sleep 1 NULL
Kill 2076 paul localhost talkbassforum Sleep 0 NULL
Kill 2080 paul localhost talkbassforum Query 2 Locked SELECT
Kill 2081 paul localhost talkbassforum Sleep 0 NULL
Kill 2083 paul localhost talkbassforum Query 1 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103747'
Kill 2084 paul localhost talkbassforum Sleep 1 NULL
Kill 2105 paul localhost talkbassforum Sleep 3 NULL
Kill 2119 paul localhost talkbassforum Query 3 Locked SELECT icon.title as icontitle,icon.iconpath...
Kill 2140 paul localhost talkbassforum Query 1 Locked SELECT forum.forumid,styleid,styleoverr...
Kill 4332 paul localhost talkbassforum Sleep 0 NULL
Kill 10198 paul localhost talkbassforum Query 3 Locked UPDATE thread SET lastpost='1067049093',replyc....thread
Kill 10437 paul localhost talkbassforum Sleep 3 NULL
Kill 11848 paul localhost talkbassforum Sleep 109 NULL
Kill 12729 paul localhost talkbassforum Sleep 4 NULL
Kill 14517 paul localhost talkbassforum Query 1 Locked SELECT threadid FROM thread WHERE threadid=103605
Kill 14518 paul localhost talkbassforum Sleep 5 NULL
Kill 15955 paul localhost talkbassforum Query 1 Locked SELECT
Kill 15993 paul localhost talkbassforum Query 0 Locked UPDATE user SET lastactivity=1067049092 WHERE userid='29249'
Kill 16224 paul localhost talkbassforum Query 2 Locked UPDATE user SET lastactivity=1067049001 WHERE userid='31374'
Kill 16241 paul localhost talkbassforum Query 1 Locked SELECT * FROM thread WHERE threadid=102368
Kill 16876 paul localhost talkbassforum Query 1 Locked SELECT * FROM thread WHERE threadid=102553
Kill 16953 paul localhost talkbassforum Sleep 4 NULL
Kill 16954 paul localhost talkbassforum Sleep 0 NULL
Kill 18989 paul localhost talkbassforum Sleep 2 NULL
Kill 18998 paul localhost talkbassforum Query 3 Locked SELECT * FROM thread WHERE threadid=103348
Kill 19003 paul localhost talkbassforum Sleep 4 NULL
Kill 19027 paul localhost talkbassforum Sleep 0 NULL
Kill 19327 paul localhost talkbassforum Sleep 4 NULL
....

EDIT: Load just shot up to 12, over 550 processes. Just a fraction of my regular users online. Somethings up - thanks for any suggestions!

kontrabass

Fri 24th Oct '03, 11:45pm

Something is causing mysql processes to lock up it looks like. Whenever the load shoots up and site becomes responsive, I have this kind of mysql processes:

post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as ic
Kill 2076 paul localhost talkbassforum Query 48 Locked SELECT count(*) AS posts FROM post WHERE dateline > '1067011923'
Kill 2080 paul localhost talkbassforum Sleep 55 NULL
Kill 2081 paul localhost talkbassforum Query 58 Locked SELECT count(*) AS posts FROM post WHERE dateline > '1067049807'
Kill 2083 paul localhost talkbassforum Query 25 Locked SELECT postid FROM post WHERE postid=1183615
Kill 2084 paul localhost talkbassforum Query 21 Locked SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.u
Kill 2105 paul localhost talkbassforum Query 51 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='103779' AND post.visible=1
Kill 2119 paul localhost talkbassforum Query 27 Locked UPDATE user SET lastactivity=1067049838 WHERE userid='5124'
Kill 2140 paul localhost talkbassforum Query 20 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='103704' AND post.visible=1
Kill 4332 paul localhost talkbassforum Query 2 Locked SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.u
Kill 10198 paul localhost talkbassforum Query 24 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='103451' AND post.visible=1
Kill 10437 paul localhost talkbassforum Query 44 Locked SELECT postid FROM post WHERE postid=1134155
Kill 11848 paul localhost talkbassforum Query 6 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='93339' AND post.visible=1
Kill 12729 paul localhost talkbassforum Query 31 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='103789' AND post.visible=1
Kill 15955 paul localhost talkbassforum Query 54 Locked SELECT COUNT(*) AS posts FROM post WHERE post.threadid='103760' AND post.visible=1
Kill 15993 paul localhost talkbassforum Query 26 Locked SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.u
Kill 16241 paul localhost talkbassforum Query 63 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='101736'
Kill 16953 paul localhost talkbassforum Query 40 Locked SELECT DISTINCT post.userid, icon.title as icontitle,icon.iconpath,
thread.threadid,thread.title,l
Kill 16954 paul localhost talkbassforum Query 36 Locked SELECT COUNT(*) AS total FROM post WHERE threadid=71266 AND visible=1
Kill 20537 paul localhost talkbassforum Query 72 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='101736'
Kill 21236 paul localhost talkbassforum Query 52 Locked SELECT postid FROM post WHERE postid=1183679
Kill 23206 paul localhost talkbassforum Query 52 Locked SELECT thread.title,thread.threadid,thread.forumid,postid ,post.dateline FROM post,thread WHERE threa
Kill 23241 paul localhost talkbassforum Query 50 Locked SELECT

eva2000

Sat 25th Oct '03, 1:48am

when you view them processlist look at the very first 10 queries (the oldest on the list) that are locked.. what are they ?

kontrabass

Sat 25th Oct '03, 2:37am

To keep this thread up to date, I made the following changes (out of desperation mainly):
upgraded php 4.1.2 to 4.3.3
brought mysql back down to 3.23.49, from 4.0.16
removed MMCache

I also found most people have mysql.max_persistent set to unlimited in php.ini, so I changed mine from 100 to unlimited.

Traffic is down for the night and things are smooth, but will post the top 10 locked processes as soon as the next lockup occurs.

kontrabass

Sat 25th Oct '03, 2:37pm

when you view them processlist look at the very first 10 queries (the oldest on the list) that are locked.. what are they ?
Here they be:

6984 paul localhost talkbassforum Query 25 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103183'
Kill 6986 paul localhost talkbassforum Query 28 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103704'
Kill 6990 paul localhost talkbassforum Query 28 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='102889'
Kill 6991 paul localhost talkbassforum Query 16 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103834'
Kill 6992 paul localhost talkbassforum Query 30 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='59742'
Kill 10721 paul localhost talkbassforum Query 34 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103647'
Kill 10726 paul localhost talkbassforum Query 28 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='96739'
Kill 11093 paul localhost talkbassforum Query 34 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103703'
Kill 11859 paul localhost talkbassforum Query 23 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103757'
Kill 11863 paul localhost talkbassforum Query 27 Locked UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='103503'