help me plz!mysql+top+load average Optimize

suiz

Verified User
Joined
Jun 21, 2008
Messages
5
top - 18:54:01 up 16:30, 1 user, load average: 1.03, 1.87, 2.02
Tasks: 276 total, 20 running, 256 sleeping, 0 stopped, 0 zombie
Cpu(s): 44.5%us, 11.0%sy, 0.0%ni, 44.4%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4048560k total, 3992316k used, 56244k free, 119380k buffers
Swap: 8193108k total, 104k used, 8193004k free, 1987068k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18557 mysql 5 -10 1283m 269m 3740 S 62 6.8 1:39.92 mysqld
19637 apache 16 0 179m 29m 14m S 6 0.7 0:00.48 httpd
19695 apache 15 0 174m 21m 10m S 6 0.5 0:00.35 httpd
19660 apache 16 0 178m 23m 9836 R 5 0.6 0:00.68 httpd
19424 apache 15 0 178m 30m 16m S 5 0.8 0:01.06 httpd
19673 apache 15 0 176m 21m 9492 S 4 0.5 0:00.37 httpd
19675 apache 16 0 178m 20m 6248 S 2 0.5 0:00.07 httpd
19705 apache 15 0 175m 24m 13m S 2 0.6 0:00.34 httpd
19281 apache 15 0 176m 30m 18m S 2 0.8 0:03.04 httpd
19517 apache 15 0 177m 30m 16m S 2 0.8 0:00.57 httpd
19665 apache 15 0 174m 17m 7920 S 2 0.4 0:00.06 httpd
18875 apache 16 0 178m 34m 20m S 1 0.9 0:06.20 httpd
19552 apache 16 0 178m 31m 17m R 1 0.8 0:01.54 httpd
19663 apache 15 0 176m 23m 11m S 1 0.6 0:00.08 httpd
2 root RT 0 0 0 0 S 1 0.0 0:14.47 migration/0
19662 apache 16 0 177m 28m 15m S 1 0.7 0:00.26 httpd
19674 apache 15 0 175m 20m 9912 S 1 0.5 0:00.19 httpd

my.cnf
[mysqld]
local-infile=0
safe-show-database
#old_passwords
back_log = 120
skip-innodb
skip-bdb
skip-locking
max_connections = 1000
thread_cache_size = 384
wait_timeout = 80
connect_timeout = 20
max_heap_table_size = 64M
net_buffer_length = 16384
concurrent_insert = 2
table_lock_wait_timeout = 50
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
key_buffer_size = 1024M
max_allowed_packet = 64M
table_cache = 2048
record_buffer=16M
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 6M
max_connect_errors = 10
thread_concurrency = 4
myisam_sort_buffer_size = 128M
tmp_table_size = 256M

[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=512M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

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

[mysqlhotcopy]
interactive-timeout
Processors 2
Model Intel(R) Core(TM)2 CPU 6300 @ 1.86GHz
CPU Speed 1.86 GHz
Physical Memory 4G

HOWTO: Optimize MySQL 5.x
 
So install the program mytop to see what is happening. Enable logging of slow queries in your my.cnf
 
......

Code:
[root@host mytop]# mytop
Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/mytop line 175
hoho....
 
To start with, this was posted in the wrong forum section. Anyway.. fixing mytop may just be a case of installing a perl module but try pasting that error into Google and seeing what comes up. For a quicker fix take a look at the processes list inside phpmyadmin
 
Back
Top