Slow Mysql user profile and post queries

[jthorpe]

OK, so here is my server config. information.

1. Is this a dedicated or shared server?
Dedicated
2. Server Specs
Single processor Pentium 4, 1.6Ghz generic. 512MB of 266 DDR
80 GB EIDE HDD
Apache/1.3.26 (Unix) mod_gzip/1.3.19.1a PHP/4.3.6
Linux 7.3
3. innodb?
I don't think I'm running any of these tables.
4. Mysql was installed via RPM

5. Top Stats:
1:50pm up 15:05, 1 user, load average: 0.66, 0.57, 0.83
205 processes: 202 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 14.5% user, 2.9% system, 0.0% nice, 82.4% idle
Mem: 481152K av, 477204K used, 3948K free, 48K shrd, 1900K buff
Swap: 1662688K av, 41060K used, 1621628K free 148044K cached

The server is nowhere as busy right now as it would be at night.
6. Mysql config variables:
port = 3306
socket = /tmp/mysql.sock
log-slow-queries
skip-locking
datadir=/var/lib/mysql
max_connections = 500
key_buffer = 96M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 30
connect_timeout = 1200
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
long_query_time = 3
#skip-innodb
[mysqld_safe]
open_files_limit = 8192
err-log=/var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

7. Mysql extended status output:
I'll update this with another one when I can since it's only been running for a short time since my last restart.

Tue Oct 26 14:03:39 EDT 2004


2:03pm up 15:19, 1 user, load average: 0.40, 0.49, 0.63
162 processes: 161 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 481152K av, 420192K used, 60960K free, 48K shrd, 2152K buff
Swap: 1662688K av, 30336K used, 1632352K free 157928K cached



Http processes currently running = 89
Mysql processes currently running = 46

Netstat information summary
2 CLOSE_WAIT
2 FIN_WAIT1
4 FIN_WAIT2
9 LISTEN
17 SYN_RECV
34 ESTABLISHED
86 TIME_WAIT


+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1448 |
| Aborted_connects | 0 |
| Bytes_received | 10733967 |
| Bytes_sent | 187174180 |
| Com_admin_commands | 1569 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 4076 |
| 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 | 396 |
| 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 | 283 |
| Com_insert_select | 7 |
| 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 | 256 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 16888 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| 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 | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 102 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 8843 |
| Connections | 2852 |
| Created_tmp_disk_tables | 2 |
| Created_tmp_tables | 1294 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 1 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 190 |
| Handler_read_first | 2707 |
| Handler_read_key | 454053 |
| Handler_read_next | 3300715 |
| Handler_read_prev | 8749 |
| Handler_read_rnd | 124370 |
| Handler_read_rnd_next | 4610553 |
| Handler_rollback | 0 |
| Handler_update | 74806 |
| Handler_write | 78428 |
| Key_blocks_used | 10444 |
| Key_read_requests | 1333375 |
| Key_reads | 10406 |
| Key_write_requests | 2309 |
| Key_writes | 2225 |
| Max_used_connections | 31 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 502 | 49% of table_cache in use
| Open_files | 972 |
| Open_streams | 0 |
| Opened_tables | 508 |
| Questions | 56446 |
| Qcache_queries_in_cache | 890 |
| Qcache_inserts | 16852 |
| Qcache_hits | 23471 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 55 |
| Qcache_free_memory | 6033624 |
| Qcache_free_blocks | 360 |
| Qcache_total_blocks | 2221 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 1 |
| Select_range | 2374 |
| Select_range_check | 0 |
| Select_scan | 3838 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 5 | (execution time > 3 secs)
| Sort_merge_passes | 0 |
| Sort_range | 2205 |
| Sort_rows | 235461 |
| Sort_scan | 1406 |
| Table_locks_immediate | 38598 |
| Table_locks_waited | 72 |
| Threads_cached | 19 |
| Threads_created | 32 |
| Threads_connected | 13 |
| Threads_running | 1 |
| Uptime | 1807 | 30 mins 7 secs
+---------------------------+-----------------+


Key Reads/Key Read Requests = 0.007804 (Cache hit = 99.992196%)
Key Writes/Key Write Requests = 0.963621
Connections/second = 1.578 (/hour = 5681.904)
KB received/second = 5.801 (/hour = 20882.789)
KB sent/second = 101.155 (/hour = 364157.831)
Temporary Tables Created/second = 0.716 (/hour = 2577.975)
Opened Tables/second = 0.281 (/hour = 1012.064)
Slow Queries/second = 0.003 (/hour = 9.961)
% of slow queries = 0.009%
Queries/second = 31.237 (/hour = 112454.676)
8. VB is not the only thing on the server. I actually have three sites running vb on this machine. 2 of them are VB3, and have almost no traffic. This site running 2.3.3 is by far the busiest site I have on here. I'm also running phpadsnew on two of the sites, including the busy one.

9. My cookie timeout is 5400 seconds, and I average 375. I've had a max of 862. Usually in a 15 minute period, I have about 150 users online.

10. http://www.dieseltruckresource.com/dev/admin/extras/phpinfo.php

11. Apache Variables:
Keepalive = on
MaxKeepAliveRequests 1000000
KeepAliveTimeout 120
MinSpareServers 10
MaxSpareServers 50
StartServers 50

12. VB Version 2.3.3

I hope this helps.

[jthorpe]

More details about my issue can be found in this thread:


http://www.vbulletin.com/forum/showthread.php?t=119221

[jthorpe]

Here is an updated version of mysql information after running for about 7 hours.


Tue Oct 26 20:34:06 EDT 2004


8:34pm up 21:49, 0 users, load average: 1.36, 1.01, 0.81
246 processes: 243 sleeping, 3 running, 0 zombie, 0 stopped
Mem: 481152K av, 472216K used, 8936K free, 36K shrd, 1720K buff
Swap: 1662688K av, 215644K used, 1447044K free 60164K cached



Http processes currently running = 142
Mysql processes currently running = 81

Netstat information summary
1 FIN_WAIT1
3 FIN_WAIT2
9 LISTEN
26 TIME_WAIT
124 ESTABLISHED


+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 22172 |
| Aborted_connects | 0 |
| Bytes_received | 165088756 |
| Bytes_sent | 2743831320 |
| Com_admin_commands | 24898 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 60783 |
| 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 | 6011 |
| 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 | 3903 |
| Com_insert_select | 102 |
| 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 | 4067 |
| Com_replace_select | 2 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 253881 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 9 |
| Com_show_fields | 10 |
| Com_show_grants | 0 |
| Com_show_keys | 3 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 39 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 213 |
| Com_show_variables | 41 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 135102 |
| Connections | 43178 |
| Created_tmp_disk_tables | 41 |
| Created_tmp_tables | 19999 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 1 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5473 |
| Handler_read_first | 42638 |
| Handler_read_key | 6293540 |
| Handler_read_next | 52845307 |
| Handler_read_prev | 253544 |
| Handler_read_rnd | 2766462 |
| Handler_read_rnd_next | 75418816 |
| Handler_rollback | 0 |
| Handler_update | 1357246 |
| Handler_write | 2283647 |
| Key_blocks_used | 26033 |
| Key_read_requests | 18089673 |
| Key_reads | 25316 |
| Key_write_requests | 35714 |
| Key_writes | 33890 |
| Max_used_connections | 137 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 829 | 81% of table_cache in use
| Open_files | 1259 |
| Open_streams | 0 |
| Opened_tables | 835 |
| Questions | 856653 |
| Qcache_queries_in_cache | 1465 |
| Qcache_inserts | 253556 |
| Qcache_hits | 360413 |
| Qcache_lowmem_prunes | 4772 |
| Qcache_not_cached | 590 |
| Qcache_free_memory | 4716960 |
| Qcache_free_blocks | 935 |
| Qcache_total_blocks | 3983 |
| Rpl_status | NULL |
| Select_full_join | 24 |
| Select_full_range_join | 9 |
| Select_range | 34669 |
| Select_range_check | 0 |
| Select_scan | 58920 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 176 | (execution time > 3 secs)
| Sort_merge_passes | 0 |
| Sort_range | 32099 |
| Sort_rows | 3062183 |
| Sort_scan | 22225 |
| Table_locks_immediate | 559098 |
| Table_locks_waited | 1521 |
| Threads_cached | 31 |
| Threads_created | 138 |
| Threads_connected | 37 |
| Threads_running | 1 |
| Uptime | 25236 | 7 hrs 36 secs
+---------------------------+-----------------+


Key Reads/Key Read Requests = 0.001399 (Cache hit = 99.998601%)
Key Writes/Key Write Requests = 0.948928
Connections/second = 1.711 (/hour = 6159.486)
KB received/second = 6.388 (/hour = 22998.431)
KB sent/second = 83.102 (/hour = 299165.621)
Temporary Tables Created/second = 0.792 (/hour = 2852.924)
Opened Tables/second = 0.033 (/hour = 119.116)
Slow Queries/second = 0.007 (/hour = 25.107)
% of slow queries = 0.021%
Queries/second = 33.946 (/hour = 122204.422)

[jthorpe]

More slow queries. This is getting ridiculous. The only thing I can think of is a corrupt database based on the fact that it worked fine until a week or two ago.


# Time: 041027 8:31:59
# User@Host: scripts[scripts] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 10368
SELECT COUNT(*) AS posts FROM post
LEFT JOIN thread ON thread.threadid=post.threadid WHERE post.visible=1 AND
1=1 AND NOT (thread.forumid='86') AND post.userid='18533';
# Time: 041027 8:32:06
# User@Host: scripts[scripts] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 25 Rows_examined: 5234
SELECT
post.postid, thread.visible
FROM
post LEFT JOIN thread ON thread.threadid=post.threadid
WHERE
post.visible=1 AND thread.visible=1 AND 1=1 AND NOT (thread.forumid='
86') AND post.userid='18533'
ORDER BY
post.dateline DESC,post.dateline DESC
LIMIT 0, 25;

[jthorpe]

Hi, Steve asked me to post this, but I never got a response, and I'm still having problems. I've not gotton any more responses in my original thread either. Could you guys take a look and let me know what you think? I would appreciate it.

[BamaStangGuy]

Your phpinfo link gives a forbidden error

[jthorpe]

Your phpinfo link gives a forbidden error

Sorry, I'll turn it back on.

[eva2000]

Hi, Steve asked me to post this, but I never got a response, and I'm still having problems. I've not gotton any more responses in my original thread either. Could you guys take a look and let me know what you think? I would appreciate it.
sorry about that seems your thread slipped through some how :o

As to your problems I'd do the following first in this exact order

1. upgrade MySQL to 4.0.20 or 4.0.22 (skip 4.0.21)
2. upgrade PHP from 4.3.6 to 4.3.9
3. change /etc/my.cnf to below and restart mysql server




[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-slow-queries
skip-locking
datadir=/var/lib/mysql
skip-innodb
max_connections = 650
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 3600
connect_timeout = 10
tmp_table_size = 32M
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


4. install Turck Mmcache http://www.vbulletin.com/forum/showthread.php?t=75878

5. Upgrade to Apache 1.3.31 - you didn't provide the maxclients value in httpd.conf ? can you provide it ?

After all is done test above for 1-7 days to see how it goes, then

6. upgrade to vB 2.3.5 or 3.0.3

[jthorpe]

Thank you. I'll let you know how it goes. :) I appreciate it!

[jthorpe]

Wow, what a difference! The mmcache has dropped my server load a good 50% at least. So far so good. Thanks for the info ! :)

[eva2000]

Wow, what a difference! The mmcache has dropped my server load a good 50% at least. So far so good. Thanks for the info ! :)
yup turck mmcache has been known to do just that :D

[jthorpe]

Wow, over 650 users online and no issues. This was the fix I needed! Thank you so much for the help. It all worked like a charm! Turck is a beautiful product, too :)

[Erwin]

With that many users online you may want to consider going to 1 Gb RAM. :)

[jthorpe]

I'm planning on doing some upgrades. I'm thinking a dual proc Dell 2850 with 2GB RAM would do the trick. This machine needs to go but these changes have made a HUGE difference. My server load has been under 1.00 all evening. Now THAT is awesome. :D

[eva2000]

Wow, over 650 users online and no issues. This was the fix I needed! Thank you so much for the help. It all worked like a charm! Turck is a beautiful product, too :)
Good to hear :) Remember after server upgrade to come back for a tune up :D