topic Re: High CPU & Mem utilization in Operating System - Linux

High CPU & Mem utilization

Atul Gautam — Fri, 23 Jun 2006 00:46:29 GMT

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 :o
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

Re: High CPU & Mem utilization

Steven E. Protter — Fri, 23 Jun 2006 06:02:09 GMT

Shalom Atul,

mysqld is definitely eating your system.

mysqld is extremely well written, meaning that if you are having this problem its due to bad sql code or application design.

You need to have your developers review their code and find the ugly code thats overloading your system.

There are a number of commands to look into it at it at the admin level as well, but its going to lead back to code, database design or application issues.

http://www.mysql.org

SEP

Re: High CPU & Mem utilization

Atul Gautam — Fri, 23 Jun 2006 23:51:39 GMT

Thanks Steve,

But if you can kindly tell me that what if we can try to configure "mysql" again and also if some operating system tuning can be done or not and if possible then what parameters we can try to change...???

ATUL

Re: High CPU & Mem utilization

Vitaly Karasik_1 — Sun, 25 Jun 2006 01:01:42 GMT

- log_slow_queries may help you to find problematic queries
- your system uses ~400MB swap, so make sence to look for memory-intensive processes. Top knows to sort processes by memory usage - just press Shift/M.

Re: High CPU & Mem utilization

Steven E. Protter — Sun, 25 Jun 2006 12:57:21 GMT

Let me clarify,

Based on what I see, I don't think the mysql configuration is the problem. I think its bad sql code.

But if you want to tune mysql, have at it.

Here is a link on how.

mysql with a high volume website(content in mysql
)
http://www-css.fnal.gov/dsg/external/freeware/mysqlTuning.html

Tunable parameters:
http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html
Server tuning parameters:
http://www.mysql.com/doc/S/e/Server_parameters.html

I'd also take a look at the mysql logs in /var/logs/mysql and see what if anything its complaining about.

Normally the cause of this issue is either bad developer code or too much work being given to mysql to do. If its the later, tuning will help and I hope my links provide you a solution.

SEP

Re: High CPU & Mem utilization

Stuart Browne — Sun, 25 Jun 2006 20:59:12 GMT

I'm with SEP on this.

You've got log_slow_queries already, so look through that code (/tmp/regen_slow.log).

From there, use mysql's "explain" command on each of the queries, and find on which are really badly written (i.e. not using indexes etc), and create appropraite indexes to fix that up.

The 400MB of swap is irrelavant. The nearly 6GB of Cache prooves this.

The only questionable value I can see in the MySQL configuration there is the max_join_size. 60000M ? On my system, it's using the default value (max_join_size = 18446744073709551615).

You might also want to review your *_buffer_size values.

If you're doing lots of queries, you might want to increase the query_cache_* values to cache more query results internally.

But in any case, you've not said how big your data set is, nor given examples of the SQL queries that are being run on it. So that makes it hard to reccomend values.

If the database is doing millions of little queries, the tuning is different than it would be if you were doing only thousands of big queries.

It also depends on what table types are in use. Use the 'show engine ...' commands to see what each is doing when it's like this. It could be you're starting lots of innodb transactions and not closing them, or.. or.. or..

Re: High CPU & Mem utilization

rmueller58 — Tue, 27 Jun 2006 08:36:51 GMT

Any poorly written SQL script can take a system to a near halt. mysql, Oracle, Informix all are prone to SQL developer syndrome.

SDS is a syndrome that defined by a statement made by SQL developers.. "It can't be my code" is the common phrase used when SDS is active.

:D

Re: High CPU & Mem utilization

Marek Podmaka — Wed, 28 Jun 2006 05:29:11 GMT

Hi!

Look at this site http://hackmysql.com/
First at all, run the mysqlreport program and analyse its output to see if you have set-up all mysql buffers/caches corretly.

Then go optimizing your code, where the other programs from this website can help you determine which queries take most time to finish and which are executed most often.

Re: High CPU & Mem utilization

Bill Thorsteinson — Tue, 04 Jul 2006 12:36:03 GMT

Looks like table scans of large tables.

This may be a data maintenance issue.

If your large table(s) contain data which is
no longer relevant (outdated), then
consider archiving and purging the
outdated data. Rebuild the purged table(s).

If this is the case, then the process will
need to become an ongoing process.

Re: High CPU & Mem utilization

Atul Gautam — Sun, 23 Jul 2006 08:02:19 GMT

Here are few more statistics ----

[root@cubot3 root]# top
17:07:49 up 2:23, 8 users, load average: 46.18, 46.17, 43.75
267 processes: 263 sleeping, 4 running, 0 zombie, 0 stopped
CPU0 states: 29.0% user 70.1% system 0.0% nice 0.0% iowait 0.1% idle
CPU1 states: 24.0% user 75.2% system 0.0% nice 0.0% iowait 0.0% idle
CPU2 states: 27.0% user 72.2% system 0.0% nice 0.0% iowait 0.0% idle
CPU3 states: 27.2% user 72.0% system 0.0% nice 0.0% iowait 0.0% idle
Mem: 7997976k av, 6875176k used, 1122800k free, 0k shrd, 63044k buff
5188980k actv, 190900k in_d, 1352100k in_c
Swap: 9920084k av, 0k used, 9920084k free 5865108k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1383 mysql 15 0 677M 677M 3064 S 99.9 8.6 328:10 0 mysqld
3480 root 15 0 1248 1248 856 R 1.0 0.0 0:00 3 top
1958 root 16 0 1260 1260 692 S 0.3 0.0 0:02 0 cubrxlogin
1 root 15 0 472 472 420 S 0.0 0.0 0:05 1 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 3 keventd
7 root 34 19 0 0 0 RWN 0.0 0.0 0:02 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 16 0 0 0 0 SW 0.0 0.0 0:00 3 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 0:00 2 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kscand/DMA
13 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 kscand/Normal
14 root 16 0 0 0 0 SW 0.0 0.0 1:07 0 kscand/HighMem
16 root 15 0 0 0 0 SW 0.0 0.0 0:01 2 kupdated
17 root 24 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:06 1 kjournald
89 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
176 root 21 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
177 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
178 root 15 0 0 0 0 SW 0.0 0.0 0:00 2 kjournald
179 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 kjournald
186 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 kjournald
196 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 kjournald
205 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 kjournald
206 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
1172 root 15 0 576 576 500 S 0.0 0.0 0:00 1 syslogd
[root@cubot3 root]#
--------------------------------------------
[root@cubot3 root]# vmstat 5 5
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
3 6 1 0 1122664 63112 5865412 0 0 240 115 62 207 28 30 42
3 8 1 0 1122624 63136 5865436 0 0 5 19 139 1352 44 56 0
1 12 1 0 1122624 63152 5865436 0 0 0 7 132 776 26 74 0
1 13 1 0 1122616 63168 5865436 0 0 0 7 131 1088 53 47 0
1 6 0 0 1122116 63200 5865604 0 0 3 133 185 1432 53 47 0
[root@cubot3 root]#
--------------------------------------------
[root@cubot3 root]# iostat -d 2 4
Linux 2.4.20-8bigmem (cubot3) 07/11/2006

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 8.90 13.76 846.20 120522 7414462
sda1 0.00 0.02 0.00 190 22
sda2 0.52 7.69 2.58 67354 22576
sda3 0.00 0.02 0.00 168 0
sda5 0.63 2.01 25.91 17618 227048
sda6 0.42 1.25 4.71 10986 41304
sda7 0.08 0.04 1.03 338 9040
sda8 6.57 2.55 782.83 22346 6859200
sda9 0.67 0.08 29.13 698 255272
sdb 39.83 1290.08 60.49 11303756 529976
sdb1 39.82 1289.49 60.45 11298578 529656
sdb2 0.01 0.56 0.04 4930 320
sdc 4.64 582.52 0.00 5104080 10
sdc1 4.64 582.47 0.00 5103664 6

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 3.37 0.00 138.86 0 536
sda1 0.00 0.00 0.00 0 0
sda2 0.52 0.00 8.29 0 32
sda3 0.00 0.00 0.00 0 0
sda5 0.78 0.00 29.02 0 112
sda6 0.00 0.00 0.00 0 0
sda7 0.00 0.00 0.00 0 0
sda8 0.52 0.00 12.44 0 48
sda9 1.55 0.00 89.12 0 344
sdb 12.44 0.00 169.95 0 656
sdb1 12.44 0.00 169.95 0 656
sdb2 0.00 0.00 0.00 0 0
sdc 0.00 0.00 0.00 0 0
sdc1 0.00 0.00 0.00 0 0

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 0.00 0.00 0.00 0 0
sda5 0.00 0.00 0.00 0 0
sda6 0.00 0.00 0.00 0 0
sda7 0.00 0.00 0.00 0 0
sda8 0.00 0.00 0.00 0 0
sda9 0.00 0.00 0.00 0 0
sdb 0.00 0.00 0.00 0 0
sdb1 0.00 0.00 0.00 0 0
sdb2 0.00 0.00 0.00 0 0
sdc 0.00 0.00 0.00 0 0
sdc1 0.00 0.00 0.00 0 0

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 0.00 0.00 0.00 0 0
sda1 0.00 0.00 0.00 0 0
sda2 0.00 0.00 0.00 0 0
sda3 0.00 0.00 0.00 0 0
sda5 0.00 0.00 0.00 0 0
sda6 0.00 0.00 0.00 0 0
sda7 0.00 0.00 0.00 0 0
sda8 0.00 0.00 0.00 0 0
sda9 0.00 0.00 0.00 0 0
sdb 1.00 12.00 0.00 24 0
sdb1 1.00 12.00 0.00 24 0
sdb2 0.00 0.00 0.00 0 0
sdc 0.00 0.00 0.00 0 0
sdc1 0.00 0.00 0.00 0 0
[root@cubot3 root]#
--------------------------------------------
[root@cubot3 root]# mpstat -P ALL 2 5
Linux 2.4.20-8bigmem (cubot3) 07/11/2006

05:12:55 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
05:13:11 PM all 41.48 0.00 58.51 0.00 0.00 0.00 0.00 0.02 162.09
05:13:11 PM 0 41.23 0.00 58.77 0.00 0.00 0.00 0.00 0.00 162.09
05:13:11 PM 1 41.79 0.00 58.21 0.00 0.00 0.00 0.00 0.00 0.00
05:13:11 PM 2 40.54 0.00 59.46 0.00 0.00 0.00 0.00 0.00 0.00
05:13:11 PM 3 42.36 0.00 57.58 0.00 0.00 0.00 0.00 0.06 0.00

05:13:11 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
05:13:13 PM all 44.76 0.00 55.00 0.00 0.00 0.00 0.00 0.24 143.33
05:13:13 PM 0 35.24 0.00 64.76 0.00 0.00 0.00 0.00 0.00 142.86
05:13:13 PM 1 45.24 0.00 54.76 0.00 0.00 0.00 0.00 0.00 0.00
05:13:13 PM 2 49.52 0.00 50.00 0.00 0.00 0.00 0.00 0.48 0.00
05:13:13 PM 3 49.05 0.00 50.48 0.00 0.00 0.00 0.00 0.48 0.00

05:13:13 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
05:13:15 PM all 36.48 0.00 63.39 0.00 0.00 0.00 0.00 0.13 195.92
05:13:15 PM 0 38.78 0.00 61.22 0.00 0.00 0.00 0.00 0.00 195.92
05:13:15 PM 1 34.69 0.00 64.80 0.00 0.00 0.00 0.00 0.51 0.00
05:13:15 PM 2 36.73 0.00 63.27 0.00 0.00 0.00 0.00 0.00 0.00
05:13:15 PM 3 35.71 0.00 64.29 0.00 0.00 0.00 0.00 0.00 0.00

05:13:15 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
05:13:17 PM all 36.00 0.00 64.00 0.00 0.00 0.00 0.00 0.00 161.50
05:13:17 PM 0 36.50 0.00 63.50 0.00 0.00 0.00 0.00 0.00 161.50
05:13:17 PM 1 33.00 0.00 67.00 0.00 0.00 0.00 0.00 0.00 0.00
05:13:17 PM 2 39.00 0.00 61.00 0.00 0.00 0.00 0.00 0.00 0.00
05:13:17 PM 3 35.50 0.00 64.50 0.00 0.00 0.00 0.00 0.00 0.00

05:13:17 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
05:13:19 PM all 50.12 0.00 49.88 0.00 0.00 0.00 0.00 0.00 203.00
05:13:19 PM 0 51.00 0.00 49.00 0.00 0.00 0.00 0.00 0.00 203.00
05:13:19 PM 1 45.50 0.00 54.50 0.00 0.00 0.00 0.00 0.00 0.00
05:13:19 PM 2 52.00 0.00 48.00 0.00 0.00 0.00 0.00 0.00 0.00
05:13:19 PM 3 52.00 0.00 48.00 0.00 0.00 0.00 0.00 0.00 0.00

Average: CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
Average: all 41.62 0.00 58.34 0.00 0.00 0.00 0.00 0.04 166.57
Average: 0 40.92 0.00 59.08 0.00 0.00 0.00 0.00 0.00 166.53
Average: 1 41.09 0.00 58.87 0.00 0.00 0.00 0.00 0.04 0.00
Average: 2 41.84 0.00 58.12 0.00 0.00 0.00 0.00 0.04 0.00
Average: 3 42.63 0.00 57.29 0.00 0.00 0.00 0.00 0.08 0.00
[root@cubot3 root]#
--------------------------------------------

Beside this, I have got Dell PowerEdge750 having 2 Intel(R) Xeon(TM) CPU 3.20GHz processors with 8GB RAM and almost 10GB SWAP.

The database size is 8GB.

Size of few of my main tables are given below ---

mst_loc -- 1.6G
mst_polst -- 1.4G
mst_rm2 -- 230k
mst_rm -- 230k
mst_veh -- 443M
t_ins -- 2.5G

--

Atul

Re: High CPU & Mem utilization

Stuart Browne — Sun, 23 Jul 2006 17:44:42 GMT

Did you go through the suggestions SEP or I made? You've certainly not addressed any of the points covered in your latest post.

Re: High CPU & Mem utilization

Atul Gautam — Sun, 23 Jul 2006 23:33:15 GMT

Hi Stuart,

I have changed the values. If you take a look at the buffer sizes, then there is a difference in the value.

But I didn't change the max_join_size.

If I change that value, will it make any effect.....????

--
Atul

Re: High CPU & Mem utilization

Stuart Browne — Mon, 24 Jul 2006 21:11:51 GMT

Given you've not shown what sort of queries you're doing, nor what's in the slow log, I can't really say.

Re: High CPU & Mem utilization

Atul Gautam — Mon, 24 Jul 2006 23:52:05 GMT

Stuart,

Here are few queries that we generate and which take very long time to execute---

-------------------QUERY 1------------------
# Time: 060724 13:28:16
# User@Host: root[root] @ localhost []
# Query_time: 981 Lock_time: 0 Rows_sent: 12 Rows_examined: 11690719
SET timestamp=1153727896;
select 5824657, finmth(pol_rep_dt), (sum(pr_prem)+sum(pr_disc)) from t_ins, mst_polst where finyr(pol_rep_dt) = '2005-06' and mst_polst.pol_type_flag="P" and rec_type="P" and mst_polst.pol_status in("A","E","C","R")and t_ins.pol_apprv_st="A" and pol_rep_dt"" and t_ins.pol_id=mst_polst.pol_id and pol_rep_dt between '2005-4-01' and '2006-3-31' group by finmth(pol_rep_dt);

-------------------QUERY 2------------------
# Time: 060724 13:31:30
# User@Host: root[root] @ localhost []
# Query_time: 1040 Lock_time: 0 Rows_sent: 12 Rows_examined: 11690719
SET timestamp=1153728090;
select 5824683, month(pol_rep_dt), (sum(pr_prem)+sum(pr_disc)) from t_ins, mst_polst where year(pol_rep_dt) = '2005' and mst_polst.pol_type_flag="P" and rec_type="P" and mst_polst.pol_status in("A","E","C","R")and t_ins.pol_apprv_st="A" and pol_rep_dt"" and t_ins.pol_id=mst_polst.pol_id and pol_rep_dt between '2005-1-01' and '2005-12-31' group by month(pol_rep_dt);

-------------------QUERY 3------------------
# Time: 060724 13:32:31
# User@Host: root[root] @ localhost []
# Query_time: 933 Lock_time: 0 Rows_sent: 4 Rows_examined: 2898895
SET timestamp=1153728151;
select 5824713, month(t_ins.cvr_pol_date), (sum(pr_prem)+sum(pr_disc)) from t_ins, mst_polst where finyr(t_ins.cvr_pol_date) = '2006-07' and mst_polst.pol_type_flag="P" and rec_type="P" and mst_polst.pol_status in("A","E","C","R")and t_ins.pol_apprv_st="A" and t_ins.cvr_pol_date
-------------------QUERY 4------------------
# Time: 060724 13:33:24
# User@Host: root[root] @ localhost []
# Query_time: 1033 Lock_time: 0 Rows_sent: 12 Rows_examined: 11690719
SET timestamp=1153728204;
select 5824712, finmth(t_ins.cvr_pol_date), (sum(pr_prem)+sum(pr_disc)) from t_ins, mst_polst where finyr(t_ins.cvr_pol_date) = '2005-06' and t_ins.divn_cd in ('BA','RETAIL') and mst_polst.pol_type_flag="P" and rec_type="P" and mst_polst.pol_status in("A","E","C","R")and t_ins.pol_apprv_st="A" and t_ins.cvr_pol_date------------------------------------------

Here i'm attaching a "gz" file which is the log for the slow queries.

--
Atul

Re: High CPU & Mem utilization

Atul Gautam — Tue, 08 Aug 2006 09:28:18 GMT

Hi Xperts,

I used the same parameters and changed the network parameters like changing the mode and speed of the network card.

Changed the speed to 1000Mbps and to Full Duplex.

Problem seems to have been resolved.

Anywayz....thanks for all your support.

--
Atul