Database really slooooow

Posted by gus bjorklund on 28-Mar-2017 12:16

what does the activity summary screen in promon R&D show?

note that lots of buffer share locks is not necessarily bad, especially with 800 users.

what does the blocked clients screen in promon show?

Posted by George Potemkin on 28-Mar-2017 12:23

> LOT of BKSH

Can you post "Status: Buffer Lock Queue"?

promon/R&D/debghb/6/15

and "Performance Indicators":

promon/R&D/3/1

Posted by ctoman on 28-Mar-2017 12:25

User DBKEY Status Type Usect

0 189384576 LOCKED TIO 1

0 190068096 LOCKED TIO 1

0 57073536 LOCKED TIO 1

0 11475712 LOCKED TIO 1

0 109552896 LOCKED TIO 1

0 376225 LOCKED SHARE 1

0 140684544 LOCKED TIO 1

0 120578304 LOCKED TIO 1

0 213102464 LOCKED TIO 1

0 515214848 LOCKED TIO 1

0 213237120 LOCKED TIO 1

0 152068992 LOCKED TIO 1

0 3776128 LOCKED TIO 1

0 193431168 LOCKED TIO 1

0 24801024 LOCKED TIO 1

Posted by ctoman on 28-Mar-2017 12:26

Usr Name Type Wait Trans id Login time

1629 0001sem1 REMC REC 92530769 1044608720 03/28/17 08:08

1724 0001kcv REMC REC 92530769 1044522601 03/28/17 07:46

1901 0001dst REMC REC 92530769 1044382314 03/28/17 11:09

1968 0001kmb REMC REC 92530769 1044420192 03/28/17 11:11

2004 0001jlk2 REMC REC 92530769 1044599421 03/28/17 11:20

2005 0001wpc REMC REC 92530769 1044477174 03/28/17 11:14

2024 0001mms2 REMC REC 92530769 1044526840 03/28/17 07:04

2262 0001jtn REMC REC 92530769 1044534270 03/28/17 06:46

2374 0001smk REMC REC 92530769 1044600183 03/28/17 11:25

Posted by George Potemkin on 28-Mar-2017 12:27

Usect = 1 and no waits for locked buffers => the buffers are not a bottleneck.

Posted by ctoman on 28-Mar-2017 12:29

11:28:23 03/28/17 06:40 to 03/28/17 11:26 (4 hrs 45 min)

Total Per Min Per Sec Per Tx

Commits 2008095 7036 117.27 1.00

Undos 1721 6 0.10 0.00

Index operations 167096K 599530 9992.17 85.21

Record operations 171786K 616358 10272.64 87.60

Total o/s i/o 43586513 152721 2545.35 21.71

Total o/s reads 42612114 149307 2488.44 21.22

Total o/s writes 974399 3414 56.90 0.49

Background o/s writes 913893 3202 53.37 0.46

Partial log writes 159286 558 9.30 0.08

Database extends 0 0 0.00 0.00

Total waits 2471155 8659 144.31 1.23

Lock waits 716 3 0.04 0.00

Resource waits 2470439 8656 144.27 1.23

Latch timeouts 464119 1626 27.10 0.23

Posted by ctoman on 28-Mar-2017 12:31

03/28/17 Activity: Summary

11:31:41 03/28/17 11:31 to 03/28/17 11:31 (10 sec)

Event Total Per Sec |Event Total Per Sec

Commits 1189 118.9 |DB Reads 47682 4768.2

Undos 1 0.1 |DB Writes 417 41.7

Record Reads 101153 10115.3 |BI Reads 0 0.0

Record Updates 403 40.3 |BI Writes 31 3.1

Record Creates 115 11.5 |AI Writes 18 1.8

Record Deletes 48 4.8 |Checkpoints 0 0.0

Record Locks 1961 196.1 |Flushed at chkpt 0 0.0

Record Waits 0 0.0

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %

Writes by APW 101 % Writes by BIW 45 % Writes by AIW 83 %

DB Size: 378 GB BI Size: 2499 MB AI Size: 47 MB

Empty blocks:7501567 Free blocks: 2344 RM chain: 1178481

Buffer Hits 85 % Active trans: 174

76 Servers, 596 Users (0 Local, 596 Remote, 0 Batch), 6 Apws

Iteration 4 of 9999, pause for 10 seconds ...

Posted by cjbrandt on 28-Mar-2017 12:35

Your record reads vs db reads are very low. Are you running Type I storage areas ?

Would be interesting to know if your disks are not able to keep up with the read requests.

Would be interesting to know what a table analysis of your heavily read tables show.

Posted by George Potemkin on 28-Mar-2017 12:35

> 4 hrs 45 min

Can you use a shorter sample interval?

Or you can run dbmon script:

ftp.progress-tech.ru/.../dbmon.sh

It will collect all information that needs to find out the answer.

Posted by ctoman on 28-Mar-2017 12:37

Inherited this DB from merger - I know type 1 and 32-bit and OE 10 all not good MGMT is aware - they did not want to improve database - we are migrating all user to our ERP which is HP-UX 11.4 64-bit (TREND).

Posted by George Potemkin on 28-Mar-2017 12:40

> DB Reads 4768.2 (per sec)

If db blocksize is 8K then you read 37 MB/sec from disk. Probably it's close to the max disk IO.

Posted by ctoman on 28-Mar-2017 13:46

I shutdown the DB and one of the db area was running into variable - so I added some more extents - so far ok.

Posted by cjbrandt on 28-Mar-2017 14:01

Because of Type I storage areas and the few records read / db read, the database tables probably have high scatter factor and fragmentation. Since the db is 32 bit, you can't allocate a large number of buffers to help that issue.

A full D&L if you have time.

Another option would be to create a new Storage Area for the smaller tables with high read rates. Then move those tables to the new Storage Area.

Posted by gus bjorklund on 28-Mar-2017 20:40

I see that:

0) all the blocked clients are waiting for a record lock and all for the same record: rowid 92530769

1) db reads per seocnd is quite high

2) ratio of record reads to db reads is very low

3) buffer pool hit ratio of 85% is quite low.

Posted by frank G on 28-Mar-2017 22:29

Could you attach this scripts how to query the locked buffer?

and join the buffer name.

Thanks

Posted by ctoman on 29-Mar-2017 07:46

George dbmon script

check this out - all database files are on hdisk11

dbmon.sh Release 3.1.3, Jul 06, 2016

Command: iostat 4 5

Date: Wed Mar 29 06:41:04 2017

Host: AIX bmhc-pos2 3 5 00F66B1D4C00

Uptime: 06:41AM up 1213 days, 19:59, 2 users, load average: 2.05, 2.06, 2.03

.

2017/03/29 06:41:08

2017/03/29 06:41:08 System configuration: lcpu=4 drives=6 ent=1.00 paths=12 vdisks=2

2017/03/29 06:41:08

2017/03/29 06:41:08 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:08 0.0 211.0 28.6 41.8 8.1 21.4 0.9 90.4

2017/03/29 06:41:08

2017/03/29 06:41:08 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:08 hdisk6 6.0 23.0 5.8 32 60

2017/03/29 06:41:08 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:08 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk10 0.2 84.0 5.2 0 336

2017/03/29 06:41:08 hdisk11 100.0 3724.0 453.5 14560 336

2017/03/29 06:41:12

2017/03/29 06:41:12 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:12 0.0 94.7 11.0 19.6 50.6 18.8 0.4 40.5

2017/03/29 06:41:12

2017/03/29 06:41:12 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:12 hdisk6 11.1 32.2 0.3 0 128

2017/03/29 06:41:12 hdisk7 0.0 1.0 2.0 2 2

2017/03/29 06:41:12 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk10 0.3 209.0 13.1 0 832

2017/03/29 06:41:12 hdisk11 100.5 3661.3 446.5 13612 960

2017/03/29 06:41:16

2017/03/29 06:41:16 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:16 0.0 87.3 7.6 14.1 59.2 19.1 0.3 29.1

2017/03/29 06:41:16

2017/03/29 06:41:16 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:16 hdisk6 0.3 1.0 0.3 4 0

2017/03/29 06:41:16 hdisk7 0.3 1.0 2.3 2 2

2017/03/29 06:41:16 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk10 0.8 224.1 14.0 0 896

2017/03/29 06:41:16 hdisk11 100.1 4352.7 531.8 16504 896

2017/03/29 06:41:20

2017/03/29 06:41:20 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:20 0.0 96.6 5.9 12.6 58.1 23.4 0.2 24.4

2017/03/29 06:41:20

2017/03/29 06:41:20 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:20 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:20 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk10 0.2 43.7 2.7 0 176

2017/03/29 06:41:20 hdisk11 99.4 3996.0 486.2 15300 784

2017/03/29 06:41:24

2017/03/29 06:41:24 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:24 0.0 88.4 5.4 10.0 65.0 19.5 0.2 20.8

2017/03/29 06:41:24

2017/03/29 06:41:24 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:24 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk7 0.2 1.0 2.2 2 2

2017/03/29 06:41:24 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk10 0.5 40.0 2.5 0 160

2017/03/29 06:41:24 hdisk11 99.9 3543.6 429.5 13648 544

Posted by ctoman on 29-Mar-2017 07:46

George dbmon script

check this out - all database files are on hdisk11

dbmon.sh Release 3.1.3, Jul 06, 2016

Command: iostat 4 5

Date: Wed Mar 29 06:41:04 2017

Host: AIX bmhc-pos2 3 5 00F66B1D4C00

Uptime: 06:41AM up 1213 days, 19:59, 2 users, load average: 2.05, 2.06, 2.03

.

2017/03/29 06:41:08

2017/03/29 06:41:08 System configuration: lcpu=4 drives=6 ent=1.00 paths=12 vdisks=2

2017/03/29 06:41:08

2017/03/29 06:41:08 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:08 0.0 211.0 28.6 41.8 8.1 21.4 0.9 90.4

2017/03/29 06:41:08

2017/03/29 06:41:08 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:08 hdisk6 6.0 23.0 5.8 32 60

2017/03/29 06:41:08 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:08 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk10 0.2 84.0 5.2 0 336

2017/03/29 06:41:08 hdisk11 100.0 3724.0 453.5 14560 336

2017/03/29 06:41:12

2017/03/29 06:41:12 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:12 0.0 94.7 11.0 19.6 50.6 18.8 0.4 40.5

2017/03/29 06:41:12

2017/03/29 06:41:12 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:12 hdisk6 11.1 32.2 0.3 0 128

2017/03/29 06:41:12 hdisk7 0.0 1.0 2.0 2 2

2017/03/29 06:41:12 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk10 0.3 209.0 13.1 0 832

2017/03/29 06:41:12 hdisk11 100.5 3661.3 446.5 13612 960

2017/03/29 06:41:16

2017/03/29 06:41:16 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:16 0.0 87.3 7.6 14.1 59.2 19.1 0.3 29.1

2017/03/29 06:41:16

2017/03/29 06:41:16 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:16 hdisk6 0.3 1.0 0.3 4 0

2017/03/29 06:41:16 hdisk7 0.3 1.0 2.3 2 2

2017/03/29 06:41:16 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk10 0.8 224.1 14.0 0 896

2017/03/29 06:41:16 hdisk11 100.1 4352.7 531.8 16504 896

2017/03/29 06:41:20

2017/03/29 06:41:20 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:20 0.0 96.6 5.9 12.6 58.1 23.4 0.2 24.4

2017/03/29 06:41:20

2017/03/29 06:41:20 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:20 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:20 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk10 0.2 43.7 2.7 0 176

2017/03/29 06:41:20 hdisk11 99.4 3996.0 486.2 15300 784

2017/03/29 06:41:24

2017/03/29 06:41:24 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:24 0.0 88.4 5.4 10.0 65.0 19.5 0.2 20.8

2017/03/29 06:41:24

2017/03/29 06:41:24 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:24 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk7 0.2 1.0 2.2 2 2

2017/03/29 06:41:24 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk10 0.5 40.0 2.5 0 160

2017/03/29 06:41:24 hdisk11 99.9 3543.6 429.5 13648 544

Posted by ctoman on 29-Mar-2017 07:56

George dbmon script

check this out - all database files are on hdisk11

dbmon.sh Release 3.1.3, Jul 06, 2016

Command: iostat 4 5

Date: Wed Mar 29 06:41:04 2017

Host: AIX bmhc-pos2 3 5 00F66B1D4C00

Uptime: 06:41AM up 1213 days, 19:59, 2 users, load average: 2.05, 2.06, 2.03

.

2017/03/29 06:41:08

2017/03/29 06:41:08 System configuration: lcpu=4 drives=6 ent=1.00 paths=12 vdisks=2

2017/03/29 06:41:08

2017/03/29 06:41:08 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:08 0.0 211.0 28.6 41.8 8.1 21.4 0.9 90.4

2017/03/29 06:41:08

2017/03/29 06:41:08 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:08 hdisk6 6.0 23.0 5.8 32 60

2017/03/29 06:41:08 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:08 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:08 hdisk10 0.2 84.0 5.2 0 336

2017/03/29 06:41:08 hdisk11 100.0 3724.0 453.5 14560 336

2017/03/29 06:41:12

2017/03/29 06:41:12 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:12 0.0 94.7 11.0 19.6 50.6 18.8 0.4 40.5

2017/03/29 06:41:12

2017/03/29 06:41:12 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:12 hdisk6 11.1 32.2 0.3 0 128

2017/03/29 06:41:12 hdisk7 0.0 1.0 2.0 2 2

2017/03/29 06:41:12 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:12 hdisk10 0.3 209.0 13.1 0 832

2017/03/29 06:41:12 hdisk11 100.5 3661.3 446.5 13612 960

2017/03/29 06:41:16

2017/03/29 06:41:16 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:16 0.0 87.3 7.6 14.1 59.2 19.1 0.3 29.1

2017/03/29 06:41:16

2017/03/29 06:41:16 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:16 hdisk6 0.3 1.0 0.3 4 0

2017/03/29 06:41:16 hdisk7 0.3 1.0 2.3 2 2

2017/03/29 06:41:16 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:16 hdisk10 0.8 224.1 14.0 0 896

2017/03/29 06:41:16 hdisk11 100.1 4352.7 531.8 16504 896

2017/03/29 06:41:20

2017/03/29 06:41:20 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:20 0.0 96.6 5.9 12.6 58.1 23.4 0.2 24.4

2017/03/29 06:41:20

2017/03/29 06:41:20 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:20 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk7 0.0 1.0 2.2 2 2

2017/03/29 06:41:20 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:20 hdisk10 0.2 43.7 2.7 0 176

2017/03/29 06:41:20 hdisk11 99.4 3996.0 486.2 15300 784

2017/03/29 06:41:24

2017/03/29 06:41:24 tty: tin tout avg-cpu: % user % sys % idle % iowait physc % entc

2017/03/29 06:41:24 0.0 88.4 5.4 10.0 65.0 19.5 0.2 20.8

2017/03/29 06:41:24

2017/03/29 06:41:24 Disks: % tm_act Kbps tps Kb_read Kb_wrtn

2017/03/29 06:41:24 hdisk6 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk7 0.2 1.0 2.2 2 2

2017/03/29 06:41:24 hdisk9 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk8 0.0 0.0 0.0 0 0

2017/03/29 06:41:24 hdisk10 0.5 40.0 2.5 0 160

2017/03/29 06:41:24 hdisk11 99.9 3543.6 429.5 13648 544

Posted by ctoman on 29-Mar-2017 07:57

my bad the /bi /ai /aisave are on hdisk10

so why is hdisk11 getting hit like that

ctoman:># lsvg -l datavg

datavg:

LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT

datalog jfs2log 2 2 1 open/syncd N/A

datalv1 jfs2 800 800 1 open/syncd /db1

bmhc-pos2::/work/home/ctoman:>

ctoman:># lsvg -l dblogvg

dblogvg:

LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT

aibilog jfs2log 1 1 1 open/syncd N/A

ailv jfs2 8 8 1 open/syncd /ai

bilv jfs2 56 56 1 open/syncd /bi

aisvlv jfs2 280 280 1 open/syncd /aisave

bmhc-pos2::/work/home/ctoman:>

Posted by gus bjorklund on 29-Mar-2017 07:59

hdisk11 is completely maxed out and can’t go any faster. the others are doing nothing in comparison.

if you can spread the read load across multiple drives, that would help a lot.

Posted by ChUIMonster on 29-Mar-2017 08:05

So you know that 32 but 10.2B is a problem -- specifically it is preventing you from having a reasonable -B that would go a long ways towards mitigating the IO issue.

Spreading the IO is going to involve quite a lot of work and ultimately isn't going to accomplish all that much in terms of speeding things up.

Whereas simply acquiring 64 bit Progress would allow you to make some seriously meaningful changes.

So what is stopping you from getting 64 bit Progress running?

Posted by ctoman on 29-Mar-2017 08:21

I'm well aware 32-bit 10.0B and all the other downfalls of the database and servers - MGMT just wants to limp along, until all users are migrated over to our legacy ERP (11.4, type II, 64-bit)

I could change the database structure file and spread the load that way.

Posted by ChUIMonster on 29-Mar-2017 09:41

So they want you to make it go faster with both hands tied behind you back just because that makes it more exciting to watch?

Yeesh.

In that case then your best bet is probably to do as you say and use the .st file to spread stuff around. Good luck.

Posted by ctoman on 29-Mar-2017 09:45

i agree with you - 1 GB of memory - it is maxing out hdisk11 users are complaining left and right.

the O/S is 64-bit and has 24 GB of memory

Posted by gus bjorklund on 29-Mar-2017 15:30

> On Mar 29, 2017, at 9:07 AM, ChUIMonster wrote:

>

> Spreading the IO is going to involve quite a lot of work and ultimately isn't going to accomplish all that much in terms of speeding things up.

disagree.

disk 11 is 100 % busy on reads. ai and bi have hardly any traffic.

going 64 bit might help a little, but you also need more memory to allow the 64 bit executables to “breathe”. also, if you are using 32-bit shared libraries in the application, you’ll have to replace those with 64-bit ones.

your only choice that does not involve buying something is to move extents around to spread the load. it isn’t that hard but does require downtime.

Posted by ChUIMonster on 29-Mar-2017 16:02

It's no fun if we always agree about everything. And I'll never learn anything.

He's only using 1GB out of 24GB available -- a relatively modest increase in RAM usage (8GB for instance) would almost certainly greatly decrease the amount of disk IO. Which IMHO would be much more beneficial than spreading it around.

Assuming that maintenance is current going from 32 bits to 64 bits doesn't cost anything from Progress.

I suppose 3rd party libraries /might/ cost something *if* they are in the mix but that seems kind of unlikely to me.

Posted by Thomas Mercer-Hursh on 29-Mar-2017 16:16

Yeah, but if they actually made the application run properly, that might remove the justification for moving to the other system ... couldn't have that!

Posted by ctoman on 30-Mar-2017 13:28

The seeks are in the Trillions on the OS. Database is stuck in the mud.

03/30/17 Activity: Performance Indicators

09:16:56 03/30/17 01:19 to 03/30/17 09:16 (7 hrs 57 min)

Total Per Min Per Sec Per Tx

Commits 5780127 12118 201.96 1.00

Undos 1328 3 0.05 0.00

Index operations 363992K 781401 13023.35 64.48

Record operations 365505K 784649 13077.48 64.75

Total o/s i/o 39118266 82009 1366.82 6.77

Total o/s reads 38425583 80557 1342.61 6.65

Total o/s writes 692683 1452 24.20 0.12

Background o/s writes 661377 1387 23.11 0.11

Partial log writes 130309 273 4.55 0.02

Database extends 0 0 0.00 0.00

Total waits 3801361 7969 132.82 0.66

Lock waits 1740590 3649 60.82 0.30

Resource waits 2060771 4320 72.00 0.36

Latch timeouts 783780 1643 27.39 0.14

Buffer pool hit rate: 95 %

03/30/17 Checkpoints

09:16:56

Ckpt ------ Database Writes ------

No. Time Len Dirty CPT Q Scan APW Q Flushes

257 09:16:38 0 2921 1084 12 0 0 36 18 1093

256 09:15:58 40 3008 2982 33 19 0 100 37 3005

255 09:14:55 63 1561 736 62 732 19 100 63 1520

254 09:14:10 45 1684 643 175 1530 41 100 45 1639

253 09:12:55 75 1648 532 188 1579 0 100 69 1642

252 09:12:01 54 1914 978 66 1304 0 100 50 1911

251 09:11:05 56 2312 1077 93 1193 0 100 55 2309

250 09:10:28 37 2333 1752 38 556 0 100 36 2330

03/30/17 Activity: Resource Queues

09:16:56 03/30/17 01:19 to 03/30/17 09:16 (7 hrs 57 min)

Queue - Requests - ------- Waits ------- --- Wait usec ---

Total /Sec Total /Sec Pct /Req /Wait

Shared Memory 0 0 0 0 0.00 0 0

Record Lock 54839828 1916 1740590 61 0.03 0 0

Schema Lock 0 0 0 0 0.00 0 0

Trans Commit 548941 19 0 0 0.00 0 0

DB Buf I Lock 4994617 175 9 0 0.00 0 0

Record Get 325100000 11359 3 0 0.00 0 0

DB Buf Read 38415876 1342 0 0 0.00 0 0

DB Buf Write 497898 17 0 0 0.00 0 0

DB Buf Backup 0 0 0 0 0.00 0 0

DB Buf S Lock 951151168 33234 2044399 71 0.00 0 0

DB Buf X Lock 5415413 189 1532 0 0.00 0 0

DB Buf Avail 0 0 0 0 0.00 0 0

Lock 12 0 0 0 0 0.00 0 0

BI Buf Read 7083 0 0 0 0.00 0 0

BI Buf Write 123407 4 2427 0 0.02 0 0

AI Buf Read 1194 0 0 0 0.00 0 0

AI Buf Write 71353 2 50 0 0.00 0 0

Lock 17 0 0 0 0 0.00 0 0

TXE Share Lock 5143269 180 0 0 0.00 0 0

TXE Update Lock 1290896 45 3188 0 0.00 0 0

TXE Commit Lock 269174 9 9163 0 0.03 0 0

TXE Excl Lock 0 0 0 0 0.00 0 0

Lock 22 0 0 0 0 0.00 0 0

03/30/17 Activity: TXE Lock Activity

09:16:56 03/30/17 01:19 to 03/30/17 09:16 (7 hrs 57 min)

--- Requests ---- --------- Waits ---------

Total /Sec Total /Sec Pct

Record Create 661634 23 0 0 0.00

Record Modify 1803081 63 47 0 0.00

Record Delete 544334 19 1206 0 0.22

Key Add 1898452 66 0 0 0.00

Key Delete 1413880 49 1896 0 0.13

Sequence Update 0 0 0 0 0.00

Other 112784 3 39 0 0.03

Share Locks 5143269 179 0 0 0.00

Update Locks 1290896 45 3188 0 0.25

Commit Locks 269174 9 9163 0 3.40

Excl Locks 0 0 0 0 0.00

Total 6703339 234 12351 0 0.18

Upgrades Request: 197621 Rate: 6 Pct: 3.84

Current Share: 1 Update: 0 Commit: 0 Excl: 0

Current Wait: 0 Wait: 0 Wait: 0 Wait: 0

03/30/17 Activity: Latch Counts

09:16:56 03/30/17 01:19 to 03/30/17 09:16 (7 hrs 57 min)

--- Locks --- --- Busy --- Naps ------ Spins -------

Owner Total /Sec /Sec Pct /Sec /Sec /Lock /Busy

MTX -- 21147005 738 0 0.0 2 0 0 0

USR -- 2783603 97 0 0.0 0 0 0 0

OM -- 470309825 16432 0 0.0 1 0 0 0

BIB -- 11081439 387 0 0.0 0 0 0 0

SCH -- 43005398 1502 0 0.0 0 0 0 0

LKP -- 1211865 42 0 0.0 0 0 0 0

GST -- 180361 6 0 0.0 0 0 0 0

TXT -- 18053957 630 0 0.0 0 0 0 0

LKT -- 0 0 0 0.0 2 0 0 0

SEQ -- 4 0 0 0.0 0 0 0 0

AIB -- 11066532 386 0 0.0 0 0 0 0

TXQ -- 13214361 461 0 0.0 0 0 0 0

BIW -- 0 0 0 0.0 0 0 0 0

LKF -- 746975573 26099 0 0.0 1 0 0 0

BFP -- 4 0 0 0.0 0 0 0 0

BHT -- 1002465439 35026 0 0.0 6 0 0 0

PWQ -- 498637 17 0 0.0 0 0 0 0

AIW -- 0 0 0 0.0 0 0 0 0

CPQ -- 1991368 69 0 0.0 0 0 0 0

LRU -- 962031243 33613 0 0.0 8 0 0 0

LRU -- 0 0 0 0.0 0 0 0 0

BUF -- 0 0 0 0.0 1 0 0 0

L28 -- 0 0 0 0.0 0 0 0 0

L29 -- 0 0 0 0.0 0 0 0 0

L30 -- 0 0 0 0.0 0 0 0 0

L31 -- 0 0 0 0.0 0 0 0 0

Posted by ChUIMonster on 30-Mar-2017 13:45

Your checkpoints are pretty close together -- increasing the bi cluster size would be a good idea.

You have waits on the OM latch. Increase -omsize to be at least as large as the number of tables & indexes in your db.

There are a lot of LRU latch waits. You could implement -lruskips 100.

ProTop is your friend. http://dbappraise.com

If you seed it with an appropriate dbanalys/dbname.dba file then it might also tell you some interesting things about where the table and index hot spots are (-tablerangesize and -indexrangesie need to be large enough too)

If that shows that you have some very small tables that are extremely active (which is surprisingly common) then you could potentially move those tables into -B2 and stop them from hitting disk all the time (this, of course, assumes that they are in type 2 storage -- even if they are not, if they are small then the effort to move them isn't hard and could be even more beneficial)

Posted by ChUIMonster on 30-Mar-2017 13:50

I don't see any AIW or BIW latch activity and there are a lot of buffers being flushed at checkpoint -- did something happen and you don't have the AIW/BIW/APW helper processes running?

Hit ratio of 95% is dreadful -- and supports my earlier contention that your biggest problem is having 32 bit cement shoes.

Posted by cjbrandt on 30-Mar-2017 13:55

I don't think -B2 and lruskips are available in OE 10.0.b

Posted by ChUIMonster on 30-Mar-2017 14:17

Oops. I somehow misread that to be 10.2b

Ok, no -B2 and no -lruskips :(

More reasons to upgrade ;)

But if there are very small tables and extremely active in type 1 areas it could pay pretty big dividends to pull them out into dedicated type 2 areas.

Posted by ctoman on 30-Mar-2017 14:46

the BG processes are running. I know.

84 APW 03/30/17 01:20 2031804

85 AIW 03/30/17 01:20 1966110

86 BIW 03/30/17 01:20 1917176

87 WDOG 03/30/17 05:53 635092

88 APW 03/30/17 11:37 1998890

Posted by ctoman on 30-Mar-2017 15:15

thanks,

is lruskips in version 10?

the bicluster size is 4MB

Posted by Rob Fitzpatrick on 30-Mar-2017 15:18

lruskips/lru2skips was added in 10.2B06

Posted by jmls on 30-Mar-2017 15:18

I believe that it was introduced in 10.2B06 ...

Ask Mike Furgul how much I appreciated this option :)

Posted by jmls on 30-Mar-2017 15:19

I believe that it was introduced in 10.2B06 ...

Ask Mike Furgal how much I appreciated this option :)

This thread is closed

All Replies