dsotmoon
Wed 14th Jan '04, 9:29pm
I am having out of memory problems and my VPS provider suggested optimizing mysql and vbulletin before considering an upgrade, can someone (eva2000) work some majic and help me out?
1. is this on dedicated or shared virual server
a VPS at dinix
2. your server specs.
Intel(R) Xeon(TM) CPU 2.40GHz (256 Mhz+ guaranteed)
4GB RAM (128MB guaranteed)
8GB alloted disk space
linux (rehat I think)
apache (1.3.29 (Unix))
PHP 4.3.2
mysql (4.0.15-standard)
do you have any innodb type databases/tables on your server ?
i have no idea ?
how mysql was compiled/installed
through WHM/CPanel I think
your top stats
8:08pm up 1 day, 13:00, 1 user, load average: 0.99, 0.82, 0.60
74 processes: 72 sleeping, 1 running, 1 zombie, 0 stopped
CPU0 states: 1.0% user, 0.0% system, 0.0% nice, 99.0% idle
CPU1 states: 0.0% user, 1.0% system, 0.0% nice, 98.0% idle
Mem: 4005812K av, 3513368K used, 492444K free, 0K shrd, 151116K buff
Swap: 4194272K av, 3541776K used, 652496K free 1199140K cached
your mysql configuration variables
|
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| 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 | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2510 |
| pid_file | /var/lib/mysql/1n-145.servernode.net.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_cache_limit | 1048576 |
| query_cache_size | 0 |
| query_cache_type | ON |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 4.0.15-standard |
| wait_timeout | 28800 |
your mysql extended-status output
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 244 |
| Aborted_connects | 13 |
| Bytes_received | 145178677 |
| Bytes_sent | 3014646757 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 61052 |
| 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 | 2329 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 4 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 8732 |
| Com_insert_select | 698 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 1587 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 1399 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 394551 |
| Com_set_option | 83 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 83 |
| Com_show_databases | 16 |
| Com_show_fields | 166 |
| Com_show_grants | 2 |
| 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 | 285 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 16 |
| Com_show_variables | 6 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 1587 |
| Com_update | 64297 |
| Connections | 56221 |
| Created_tmp_disk_tables | 345 |
| Created_tmp_tables | 18315 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 8841 |
| Handler_read_first | 60404 |
| Handler_read_key | 15086593 |
| Handler_read_next | 14288657 |
| Handler_read_prev | 521667 |
| Handler_read_rnd | 1388674 |
| Handler_read_rnd_next | 8474886 |
| Handler_rollback | 0 |
| Handler_update | 75638 |
| Handler_write | 771689 |
| Key_blocks_used | 7793 |
| Key_read_requests | 23278586 |
| Key_reads | 35314 |
| Key_write_requests | 49019 |
| Key_writes | 37034 |
| Max_used_connections | 27 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 113 |
| Open_streams | 0 |
| Opened_tables | 2898 |
| Questions | 593146 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 1226 |
| Select_full_range_join | 2988 |
| Select_range | 129280 |
| Select_range_check | 0 |
| Select_scan | 56506 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 48 |
| Sort_merge_passes | 0 |
| Sort_range | 39507 |
| Sort_rows | 1521356 |
| Sort_scan | 19145 |
| Table_locks_immediate | 748841 |
| Table_locks_waited | 495 |
| Threads_cached | 0 |
| Threads_created | 56220 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 85892 |
+--------------------------+------------+
is your vB the only thing on the server?
one other site running a IB with next to no activity
how many average and max concurrent users on your vB forum ?[/]
30-65 average users / 90-100max but growing
[b]create a file named phpinfo.php
http://www.fullsizebronco.com/phpinfo.php
if you run Apache and
dont know where to find this in my VPS root ? ? ?
what version of vB are you running ?
vBulletin 3.0.0 Release Candidate 2
1. is this on dedicated or shared virual server
a VPS at dinix
2. your server specs.
Intel(R) Xeon(TM) CPU 2.40GHz (256 Mhz+ guaranteed)
4GB RAM (128MB guaranteed)
8GB alloted disk space
linux (rehat I think)
apache (1.3.29 (Unix))
PHP 4.3.2
mysql (4.0.15-standard)
do you have any innodb type databases/tables on your server ?
i have no idea ?
how mysql was compiled/installed
through WHM/CPanel I think
your top stats
8:08pm up 1 day, 13:00, 1 user, load average: 0.99, 0.82, 0.60
74 processes: 72 sleeping, 1 running, 1 zombie, 0 stopped
CPU0 states: 1.0% user, 0.0% system, 0.0% nice, 99.0% idle
CPU1 states: 0.0% user, 1.0% system, 0.0% nice, 98.0% idle
Mem: 4005812K av, 3513368K used, 492444K free, 0K shrd, 151116K buff
Swap: 4194272K av, 3541776K used, 652496K free 1199140K cached
your mysql configuration variables
|
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| 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 | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2510 |
| pid_file | /var/lib/mysql/1n-145.servernode.net.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_cache_limit | 1048576 |
| query_cache_size | 0 |
| query_cache_type | ON |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 4.0.15-standard |
| wait_timeout | 28800 |
your mysql extended-status output
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 244 |
| Aborted_connects | 13 |
| Bytes_received | 145178677 |
| Bytes_sent | 3014646757 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 61052 |
| 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 | 2329 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 4 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 8732 |
| Com_insert_select | 698 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 1587 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 1399 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 394551 |
| Com_set_option | 83 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 83 |
| Com_show_databases | 16 |
| Com_show_fields | 166 |
| Com_show_grants | 2 |
| 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 | 285 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 16 |
| Com_show_variables | 6 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 1587 |
| Com_update | 64297 |
| Connections | 56221 |
| Created_tmp_disk_tables | 345 |
| Created_tmp_tables | 18315 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 8841 |
| Handler_read_first | 60404 |
| Handler_read_key | 15086593 |
| Handler_read_next | 14288657 |
| Handler_read_prev | 521667 |
| Handler_read_rnd | 1388674 |
| Handler_read_rnd_next | 8474886 |
| Handler_rollback | 0 |
| Handler_update | 75638 |
| Handler_write | 771689 |
| Key_blocks_used | 7793 |
| Key_read_requests | 23278586 |
| Key_reads | 35314 |
| Key_write_requests | 49019 |
| Key_writes | 37034 |
| Max_used_connections | 27 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 113 |
| Open_streams | 0 |
| Opened_tables | 2898 |
| Questions | 593146 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 1226 |
| Select_full_range_join | 2988 |
| Select_range | 129280 |
| Select_range_check | 0 |
| Select_scan | 56506 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 48 |
| Sort_merge_passes | 0 |
| Sort_range | 39507 |
| Sort_rows | 1521356 |
| Sort_scan | 19145 |
| Table_locks_immediate | 748841 |
| Table_locks_waited | 495 |
| Threads_cached | 0 |
| Threads_created | 56220 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 85892 |
+--------------------------+------------+
is your vB the only thing on the server?
one other site running a IB with next to no activity
how many average and max concurrent users on your vB forum ?[/]
30-65 average users / 90-100max but growing
[b]create a file named phpinfo.php
http://www.fullsizebronco.com/phpinfo.php
if you run Apache and
dont know where to find this in my VPS root ? ? ?
what version of vB are you running ?
vBulletin 3.0.0 Release Candidate 2