Hi All,

Kindly help me in optimizing the server as it displays a great amount of CPU & MEM being utilised when the mysql process executes.

Below are the stats ---
--------------------------------------------------------------------------
[root@cubot3 root]# top
15:51:57 up 23:22, 5 users, load average: 13.57, 14.98, 15.00
147 processes: 146 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 60.1% user 39.3% system 0.0% nice 0.0% iowait 0.0% idle
CPU1 states: 63.2% user 36.2% system 0.0% nice 0.0% iowait 0.0% idle
CPU2 states: 65.0% user 34.4% system 0.0% nice 0.0% iowait 0.0% idle
CPU3 states: 62.4% user 37.0% system 0.0% nice 0.0% iowait 0.0% idle
Mem: 7997976k av, 7970348k used, 27628k free, 0k shrd, 26924k buff
6002752k actv, 566868k in_d, 469864k in_c
Swap: 2096472k av, 410516k used, 1685956k free 5855740k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1353 mysql 15 0 1558M 1.4G 1952 S 99.9 17.8 1517m 2 mysqld
7480 root 15 0 1536 1536 784 S 1.1 0.0 0:00 0 cubmenuserv
7330 root 15 0 1528 1528 784 S 0.3 0.0 0:00 0 cubmenuserv
1273 root 15 0 480 348 236 S 0.1 0.0 0:00 1 sshd
7238 cubot 15 0 2256 2256 1676 S 0.1 0.0 0:00 1 httpd
7471 root 15 0 1160 1160 856 R 0.1 0.0 0:00 1 top
7481 root 15 0 1300 1300 872 S 0.1 0.0 0:00 1 cubgphserv
1 root 15 0 108 88 56 S 0.0 0.0 0:05 2 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 keventd
7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd_CPU0
8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU1
9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd_CPU2
10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd_CPU3
15 root 15 0 0 0 0 SW 0.0 0.0 0:12 3 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 6:57 0 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 kscand/DMA
13 root 16 0 0 0 0 SW 0.0 0.0 1:35 2 kscand/Normal
14 root 16 0 0 0 0 SW 0.0 0.0 20:10 0 kscand/HighMem
16 root 15 0 0 0 0 SW 0.0 0.0 0:02 2 kupdated
17 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
23 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 aacraid
24 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 scsi_eh_0
27 root 15 0 0 0 0 SW 0.0 0.0 0:01 3 kjournald
89 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
173 root 15 0 0 0 0 SW 0.0 0.0 0:00 2 kjournald
174 root 15 0 0 0 0 SW 0.0 0.0 0:20 1 kjournald
175 root 15 0 0 0 0 SW 0.0 0.0 0:01 0 kjournald
176 root 15 0 0 0 0 SW 0.0 0.0 0:01 2 kjournald
177 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 kjournald
----------------------------------------------------------------------------------------------------------
[root@cubot3 root]# iostat
Linux 2.4.20-8bigmem (cubot3) 06/13/2006

avg-cpu: %user %nice %system %iowait %steal %idle
20.26 0.00 10.17 0.00 0.00 69.57

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 14.62 79.71 325.87 6807870 27833846
sda1 0.00 0.00 0.00 266 54
sda2 0.22 2.32 1.08 198458 92392
sda3 0.91 4.70 10.27 401832 877464
sda5 0.30 0.99 10.76 84594 918840
sda6 0.23 1.32 1.98 112386 169400
sda7 0.07 0.08 0.92 6546 78832
sda8 0.16 0.91 1.96 77738 167208
sda9 12.73 69.38 298.90 5925994 25529656
sdb 256.41 7790.67 864.13 665424476 73808152
sdb1 251.67 7577.22 678.65 647192714 57965592
sdb2 4.74 213.45 185.48 18231754 15842560
[root@cubot3 root]#
----------------------------------------------------------------------------------------------------------
[root@cubot3 root]# vmstat
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
0 0 0 423976 179876 23176 5835140 1 1 104 23 45 14 20 10 69
[root@cubot3 root]#
----------------------------------------------------------------------------------------------------------


The contents of my.cnf are given below ---

----------------------------------------------------------------------------------------------------------
[mysqld]
max_connections=125
max_user_connections=125
#datadir=/cubot/mysql/data
datadir=/data
pid-file=/var/lib/mysql/cubot.pid
socket=/tmp/mysql.sock
tmpdir=/cubot/tmp_mysql/
port=3306

skip-locking
skip-innodb
skip-bdb
skip-external-locking
skip-name-resolve
skip-host-cache

key_buffer_size=2048M
max_allowed_packet=16M
table_cache=256
sort_buffer_size=64M
read_buffer_size=8M
myisam_sort_buffer_size=128M
wait_timeout=600
thread_cache=135
thread_concurrency=8
max_connect_errors=40000
tmp_table_size=1024M
max_tmp_tables=60
max_binlog_cache_size=512M
max_join_size=60000M
join_buffer_size=32M
query_prealloc_size=32768
query_alloc_block_size=32768
read_rnd_buffer_size=16M
transaction_alloc_block_size=32768
transaction_prealloc_size=32768
query_cache_type=1
query_cache_limit=16M
query_cache_size=64M
slave-skip-errors=1053
long_query_time = 30
log_slow_queries = /tmp/regen_slow.log
------------------------------------------------------------------------------------------------------------------

If somebody can suggest me sumthing as whenever a query is being fired, the load on the server goes up and the performance degrades and it takes a very long time to respond. At times, when the no. of users are high and a no. of queries are being fired, in that case we have to kill the mysqld process and then start it again only then it starts working again.

Thanks in advance.....



ATUL

oh,my god.

max_connections=2000
wait_timeout=30
and optimizing your programa