SERVER OPTIMIZE REQUEST- Please help

[thang422]

Please help me with my server. the loads keeps on jumping up and down. It can go from .6 to 44 in just a matter of minutes. Also I have a question about purchasing servers.

is it better to have a different server to host the mysql database which has lower server spec or host the mysql and http on the same server with higher specs.

Thank you for your help!


1.dedicated
2.Core2 6550/2GBRAM/400HD-NON-RAID/CENTOS/cpanel V. 11.18.6-stable

-mysql 5.0.45-community

-php 5.2.5

-apache 2.2.8


3. 3.68 PL2

6.

top - 23:57:08 up 11 days, 31 min, 1 user, load average: 8.14, 5.67, 3.43
Tasks: 173 total, 1 running, 170 sleeping, 1 stopped, 1 zombie
Cpu(s): 68.8%us, 34.0%sy, 0.0%ni, 62.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 2067376k total, 1935632k used, 131744k free, 125068k buffers
Swap: 2031608k total, 96k used, 2031512k free, 1029412k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2674 mysql 10 -5 219m 189m 3604 S 52 9.4 8362:09 mysqld
3659 nobody 15 0 95960 19m 8792 S 20 1.0 0:00.95 httpd
3718 nobody 15 0 95772 18m 8012 S 2 0.9 0:00.07 httpd
3344 nobody 15 0 99544 30m 15m S 1 1.5 0:01.42 httpd
3022 nobody 15 0 97492 31m 18m S 0 1.5 0:03.12 httpd
1 root 15 0 2060 580 500 S 0 0.0 0:03.86 init
2 root RT 0 0 0 0 S 0 0.0 0:00.10 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 4:50.89 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.07 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.24 events/0
9 root 10 -5 0 0 0 S 0 0.0 0:00.23 events/1
10 root 10 -5 0 0 0 S 0 0.0 0:00.00 khelper
11 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread
15 root 18 -5 0 0 0 S 0 0.0 0:00.29 kblockd/0



7.

[mysqld]
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 256
wait_timeout = 90
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
collation-server=latin1_general_ci

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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


8.
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.45-community uptime 11 0:46:58 Mon Jun 30 00:11:19 2008

__ Key __________________________________________________ _______________
Buffer used 56.63M of 64.00M %Used: 88.49
Current 63.61M %Usage: 99.40
Write hit 59.15%
Read hit 100.00%

__ Questions __________________________________________________ _________
Total 76.95M 80.7/s
DMS 56.37M 59.1/s %Total: 73.26
QC Hits 16.23M 17.0/s 21.10
Com_ 2.36M 2.5/s 3.06
COM_QUIT 1.99M 2.1/s 2.58
+Unknown 1.33k 0.0/s 0.00
Slow 10 s 51 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 56.37M 59.1/s 73.26
UPDATE 35.01M 36.7/s 45.50 62.11
SELECT 17.45M 18.3/s 22.68 30.96
INSERT 1.82M 1.9/s 2.36 3.22
DELETE 1.20M 1.3/s 1.55 2.12
REPLACE 894.54k 0.9/s 1.16 1.59
Com_ 2.36M 2.5/s 3.06
change_db 2.30M 2.4/s 2.99
unlock_tabl 11.03k 0.0/s 0.01
lock_tables 11.03k 0.0/s 0.01

__ SELECT and Sort __________________________________________________ ___
Scan 4.89M 5.1/s %SELECT: 27.99
Range 1.98M 2.1/s 11.37
Full join 3.00k 0.0/s 0.02
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2.37M 2.5/s
Sort range 1.61M 1.7/s
Sort mrg pass 314.57k 0.3/s

__ Query Cache __________________________________________________ _______
Memory usage 37.02M of 64.00M %Used: 57.84
Block Fragmnt 19.63%
Hits 16.23M 17.0/s
Inserts 17.09M 17.9/s
Insrt:Prune 19.31:1 17.0/s
Hit:Insert 0.95:1

__ Table Locks __________________________________________________ _______
Waited 362.96k 0.4/s %Total: 0.55
Immediate 65.05M 68.2/s

__ Tables __________________________________________________ ____________
Open 1152 of 4000 %Cache: 28.80
Opened 6.15k 0.0/s

__ Connections __________________________________________________ _______
Max used 66 of 500 %Max: 13.20
Total 1.99M 2.1/s

__ Created Temp __________________________________________________ ______
Disk table 64.65k 0.1/s
Table 764.52k 0.8/s Size: 64.0M
File 629.13k 0.7/s

__ Threads __________________________________________________ ___________
Running 2 of 5
Cached 61 of 256 %Hit: 100
Created 66 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 8.07k 0.0/s
Connects 829 0.0/s

__ Bytes __________________________________________________ _____________
Sent 118.97G 124.8k/s
Received 4.19G 4.4k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.45-community uptime 11 0:47:8 Mon Jun 30 00:11:29 2008

__ Key __________________________________________________ _______________
Buffer used 56.63M of 64.00M %Used: 88.49
Current 63.62M %Usage: 99.41
Write hit 20.95%
Read hit 100.00%

__ Questions __________________________________________________ _________
Total 911 91.1/s
DMS 485 48.5/s %Total: 53.24
QC Hits 316 31.6/s 34.69
Com_ 62 6.2/s 6.81
COM_QUIT 48 4.8/s 5.27
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 485 48.5/s 53.24
SELECT 294 29.4/s 32.27 60.62
UPDATE 101 10.1/s 11.09 20.82
INSERT 63 6.3/s 6.92 12.99
DELETE 16 1.6/s 1.76 3.30
REPLACE 11 1.1/s 1.21 2.27
Com_ 62 6.2/s 6.81
change_db 54 5.4/s 5.93
set_option 1 0.1/s 0.11
show_variab 1 0.1/s 0.11

__ SELECT and Sort __________________________________________________ ___
Scan 149 14.9/s %SELECT: 50.68
Range 19 1.9/s 6.46
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 53 5.3/s
Sort range 10 1.0/s
Sort mrg pass 1 0.1/s

__ Query Cache __________________________________________________ _______
Memory usage 33.21M of 64.00M %Used: 51.88
Block Fragmnt 26.62%
Hits 316 31.6/s
Inserts 242 24.2/s
Insrt:Prune 242:1 24.1/s
Hit:Insert 1.31:1

__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 700 70.0/s

__ Tables __________________________________________________ ____________
Open 1151 of 4000 %Cache: 28.77
Opened 3 0.3/s

__ Connections __________________________________________________ _______
Max used 66 of 500 %Max: 13.20
Total 50 5.0/s

__ Created Temp __________________________________________________ ______
Disk table 0 0/s
Table 16 1.6/s Size: 64.0M
File 2 0.2/s

__ Threads __________________________________________________ ___________
Running 2 of 7
Cached 59 of 256 %Hit: 100
Created 0 0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s

__ Bytes __________________________________________________ _____________
Sent 14.79M 1.5M/s
Received 184.64k 18.5k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s




9.Vbulletin and other simple html pages.

10.I dont know...

11.phpinfo (http://www.vbulletin.com/forum/vietfiles.org/phpinfo.php)

12.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 1
<IfModule mpm_prefork_module>
StartServers 15
MinSpareServers 15
MaxSpareServers 20
ServerLimit 256
MaxClients 150
MaxRequestsPerChild 1000

13.none

14.
Linux server.vinashare.com 2.6.18-53.1.14.el5 #1 SMP Wed Mar 5 11:36:49 EST 2008 i686 i686 i386 GNU/Linux

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 32637
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32637
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited



processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Duo CPU E6550 @ 2.33GHz
stepping : 11
cpu MHz : 1998.000
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monitor ds_cpl vmx smx est tm2 cx16 xtpr lahf_lm
bogomips : 3725.49

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Duo CPU E6550 @ 2.33GHz
stepping : 11
cpu MHz : 2331.000
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monitor ds_cpl vmx smx est tm2 cx16 xtpr lahf_lm
bogomips : 3723.29

[eva2000]

can't see anything out of the ordinary there, mysql stats look pretty okay to me despite the cpu load.

When you can cpu loads > 10.0 can you repost output for these 2 commands

top

mysqladmin -u root -p var ext ver proc > stats.txt

open the stats.txt file to copy and paste it's contents.

As well as for mysqlreport output if possible (mysqlreport question #8 at http://www.vbulletin.com/forum/showthread.php?t=70117)

[thang422]

Thanks for your reply. Please help me

TOP
root@server [~]# top
top - 09:20:21 up 19 days, 9:54, 1 user, load average: 10.54, 6.76, 4.52
Tasks: 160 total, 3 running, 155 sleeping, 1 stopped, 1 zombie
Cpu(s): 50.9%us, 48.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 2067376k total, 1814528k used, 252848k free, 179176k buffers
Swap: 2031608k total, 136448k used, 1895160k free, 1060104k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9644 mysql 10 -5 224m 122m 3348 S 178 6.1 215:21.82 mysqld
19701 nobody 15 0 97820 19m 8432 S 7 1.0 0:00.31 httpd
19619 nobody 15 0 95924 23m 14m S 4 1.2 0:01.73 httpd
19628 nobody 15 0 97092 23m 12m S 3 1.2 0:03.30 httpd
19703 nobody 15 0 95080 16m 8032 S 3 0.8 0:00.10 httpd
19704 nobody 15 0 93592 14m 7408 S 3 0.7 0:00.08 httpd
19592 nobody 15 0 98808 24m 11m R 1 1.2 0:03.97 httpd
4058 vietfile 15 0 56940 44m 1160 S 0 2.2 3062:04 xbt_tracker
19702 root 15 0 2188 1044 796 R 0 0.1 0:00.03 top
1 root 15 0 2060 484 456 S 0 0.0 0:04.92 init
2 root RT 0 0 0 0 S 0 0.0 0:00.15 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.07 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 8:20.54 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.09 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.47 events/0



STATS.TXT

+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_general_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
| completion_type | 0 |
| concurrent_insert | 2 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | YES |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_ssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | server.vinashare.com |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 134217728 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 75496448 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 67108864 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 16 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 9510 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/server.vinashare.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 65536 |
| query_cache_limit | 5242880 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 262144 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 782336 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 4194296 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | MST |
| table_cache | 4500 |
| table_lock_wait_timeout | 30 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 67108864 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.45-community |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 90 |
+---------------------------------+-----------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 40 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 383125244 |
| Bytes_sent | 955224944 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 75073 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 32 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 30564 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 32 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 47210 |
| Com_insert_select | 300 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 178 |
| Com_optimize | 30 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 32974 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 665702 |
| Com_set_option | 70 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 226 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 94 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 258 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 32 |
| Com_unlock_tables | 178 |
| Com_update | 1139137 |
| Com_update_multi | 39 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 64283 |
| Created_tmp_disk_tables | 2882 |
| Created_tmp_files | 11552 |
| Created_tmp_tables | 25242 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 30842 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 69028 |
| Handler_read_key | 69389744 |
| Handler_read_next | 888265177 |
| Handler_read_prev | 240706 |
| Handler_read_rnd | 5568609 |
| Handler_read_rnd_next | 2456453924 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 34258416 |
| Handler_write | 4196601 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 90762 |
| Key_blocks_used | 25218 |
| Key_read_requests | 174405443 |
| Key_reads | 25467 |
| Key_write_requests | 206835 |
| Key_writes | 116665 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 20 |
| Ndb_cluster_node_id | 0 |
| Ndb_config_from_host | |
| Ndb_config_from_port | 0 |
| Ndb_number_of_data_nodes | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 608 |
| Open_streams | 0 |
| Open_tables | 358 |
| Opened_tables | 518 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 8650 |
| Qcache_free_memory | 29320488 |
| Qcache_hits | 580117 |
| Qcache_inserts | 660207 |
| Qcache_lowmem_prunes | 27527 |
| Qcache_not_cached | 5596 |
| Qcache_queries_in_cache | 14900 |
| Qcache_total_blocks | 39275 |
| Questions | 2636256 |
| Rpl_status | NULL |
| Select_full_join | 69 |
| Select_full_range_join | 0 |
| Select_range | 57723 |
| Select_range_check | 0 |
| Select_scan | 146053 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 5776 |
| Sort_range | 49293 |
| Sort_rows | 47094018 |
| Sort_scan | 73863 |
| Table_locks_immediate | 2186943 |
| Table_locks_waited | 11946 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 17 |
| Threads_connected | 3 |
| Threads_created | 20 |
| Threads_running | 1 |
| Uptime | 28276 |
| Uptime_since_flush_status | 28276 |
+-----------------------------------+------------+
mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.45-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 7 hours 51 min 16 sec

Threads: 3 Questions: 2636257 Slow queries: 0 Opens: 518 Flush tables: 1 Open tables: 358 Queries per second avg: 93.233
+-------+---------------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------------+-----------+----+---------+------+-------+------------------+
| 64282 | vietnova_user | localhost | | Query | 0 | | show processlist |
+-------+---------------+-----------+----+---------+------+-------+------------------+



I have also ask my host admin to look at my mysql server and this is what that have done and reply. The server load had increase alot more since their suggestion. Can you please help.

Hello

We have optimized the mysql in the server. We have changed the sort_buffer_size from 2M to 4M, because it will help to solve the queries which used to find and sort the data.

We could see that for simple queries, data packets will be small and less amount of memory is needed. So we have decreased the max_allowed_packet to 16M from 34M. Also we can see that number of data access is high, to handle more number of queries more threads want to be ready to run, so we have increased the thread_concurrency from 4M to 6M. Because of accessing the same data with the same type of query we have increased the query_cache_limit from 4M to 5M, so that the data that is fetched from the database will be there in the query cache and if similar type of query comes the data is fetched from the cache than from the database.

Thanks,

[thang422]

mysqlreport
#
MySQL 5.0.45-community uptime 0 8:5:36 Tue Jul 8 09:36:15 2008

__ Key __________________________________________________ _______________
Buffer used 24.91M of 128.00M %Used: 19.46
Current 39.65M %Usage: 30.97
Write hit 43.38%
Read hit 99.99%

__ Questions __________________________________________________ _________
Total 2.69M 92.4/s
DMS 1.95M 66.8/s %Total: 72.34
QC Hits 599.11k 20.6/s 22.26
Com_ 78.96k 2.7/s 2.93
COM_QUIT 66.60k 2.3/s 2.47
-Unknown 270 0.0/s 0.01
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.95M 66.8/s 72.34
UPDATE 1.14M 39.2/s 42.44 58.66
SELECT 690.89k 23.7/s 25.67 35.48
INSERT 48.67k 1.7/s 1.81 2.50
REPLACE 33.95k 1.2/s 1.26 1.74
DELETE 31.44k 1.1/s 1.17 1.61
Com_ 78.96k 2.7/s 2.93
change_db 77.77k 2.7/s 2.89
show_status 269 0.0/s 0.01
show_fields 240 0.0/s 0.01

__ SELECT and Sort __________________________________________________ ___
Scan 150.99k 5.2/s %SELECT: 21.85
Range 59.89k 2.1/s 8.67
Full join 70 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 76.94k 2.6/s
Sort range 50.98k 1.7/s
Sort mrg pass 6.00k 0.2/s

__ Query Cache __________________________________________________ _______
Memory usage 37.47M of 64.00M %Used: 58.55
Block Fragmnt 20.07%
Hits 599.11k 20.6/s
Inserts 685.20k 23.5/s
Insrt:Prune 24.15:1 22.5/s
Hit:Insert 0.87:1

__ Table Locks __________________________________________________ _______
Waited 12.34k 0.4/s %Total: 0.55
Immediate 2.23M 76.5/s

__ Tables __________________________________________________ ____________
Open 367 of 4500 %Cache: 8.16
Opened 527 0.0/s

__ Connections __________________________________________________ _______
Max used 20 of 500 %Max: 4.00
Total 66.62k 2.3/s

__ Created Temp __________________________________________________ ______
Disk table 2.98k 0.1/s
Table 26.27k 0.9/s Size: 64.0M
File 11.99k 0.4/s

__ Threads __________________________________________________ ___________
Running 1 of 6
Cached 14 of 256 %Hit: 99.97
Created 20 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 40 0.0/s
Connects 8 0.0/s

__ Bytes __________________________________________________ _____________
Sent 1.89G 64.7k/s
Received 394.02M 13.5k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s

[eva2000]

those top stats say you're swapping to disk by a fair amount so using more memory than you have available

you also running a torrent tracer xbt_tracker ?? tried turning that off and see how loads are ?