IDS Forum

Re: Performance - Sun SPARC T2

Posted By: Richard Kofler
Date: Friday, 6 November 2009, at 1:50 p.m.

In Response To: Performance - Sun SPARC T2 (Cesar Inacio Martins)

Hi,

maybe I can contribute a bit.

A nasty decision this is, when you must go on with this.
Good DBAs can do some miracles, but we cannot heal every
wrong decision.

This type of HW combination is nice for a lab dealing with 500
records, or if is runs some tomcat containers (not too many!).

It is a perfect misfit for IDS (same goes for Oracle - search for
ORA whitepapers, how to cope with a challenge like this: they have
a few ideas, how to survive - somewhat at least)

More text below

Dic_k

Cesar Inacio Martins schrieb:
> Hi,
>
> I already read here some discussions about performance issues with Sun
> Machines, UltraSPARC T1 and T2.
> Unfortunately this is my destiny , soon I will migrate from HP-UX to a Sun
> T5120 SPARC T2 with
> 4 Cores and 32 Threads (32 GB RAM).
>
> I already have the machine available and ready for tests...
>
> My initial objective is discovery the best way to work with this processor
> SPARC T2 and yours threads.
>
> For this test I load an old copy of our production database (via HPL), loading
> only the tables without indexes.
>
> To assess the overall performance of the machine I choose execute "create
> index" in two "big" tables.
> I consider this a good test because need to do a lot of I/O to read/write all
> data , memory usage and CPU in parallel to sort the indexes... if somebody
> disagree , please tell me why ...
> Art ???? :)
>
> So, after my tests I got better performance when I use more threads and not
> only the "cores".
> If there something I forgot to set or consider, please , tell me.
>
> During the process, monitoring the machine, I have
> - For certain indexes, more than 150 threads in the same session...
> - I/O reads up to 200 MB/sec , writes up to 80 MB/sec
> - When configured with only 4 CPU threads , the prstat show 100% of CPU
> utilization and with vmstat I get some little values on the CPU queue (4 - 8 )
> - When configured with the 32 Threads, rarely I get 90% of CPU utilization .

What I/O waits do you see (IDS wait reason stats (wts utility) / sar -d / iostat)?

> The reason for this , on my opinion , is because I don't used the 32 threads
> (affinity)...
>
> What I don't understand is why the instance appear work better with AIO VPs
> and not KAIO ... any comments??

yes.
SOL10 will use every single Byte of memory left over as cache, but see below.

And *do not* ask ME, what I think about SOL10's page coloring algo .....

Try to convince your sysdmin to set IDS processes to real time prio, this
is the best substitute I was able to find in 4 yrs for what I was used to
gain from NOAGE.

>
> ============================================
> Here is the environment:
>
> Machine: Sun T5120 UltraSPARC T2
> 4 Cores / 32 Threads - 1.2Ghz
> 32 GB RAM
> Solaris 10 - installed in the internal disks
> Database storage: is connected by SAN to IBM DS8300 Storage (raid 5) , 16
> devices/LUNs of 50GB each.
>
> IDS 11.50 FC4TL Enterprise TimeLimited (for evaluate)
> Here the more important items on the ONCONFIG :
>
> * = parameters changed for each test
> ---------------------------------------------
> *VPCLASS cpu,num=16,aff=(10-26),noage
> *VPCLASS aio,num=10,aff=(27-31),noage
> AUTO_AIOVPS 1

The best you can do with AUTO_* is to avoid it.
When you understand and see what is going on, and
When you 'like' what you see (or better when you think 'I can live with this'),
then you can try if AUTO_* comes near that.
I always fail in step 2.

> SHMVIRTSIZE 10240000

what is your SHMADD?
what does onstat -g seg show?
how much % of memory do you use when creating the big index
(onstat - has the value on the right side)?
do you see sorts on disk (onstat -D show pg writes on tempdbspaces)?

> SHMTOTAL 30000000
> AUTO_CKPTS 1
> DS_MAX_QUERIES 1
> DS_TOTAL_MEMORY 9216000
> DS_MAX_SCANS 4

too low, make it equal to your no of LUNs * 2,
but *after* upping your no of LUNs

> DS_NONPDQ_QUERY_MEM 150000

too high for me -> 50000 is high already, as
you won't use this memory in your test.

> BUFFERPOOL
> default,buffers=10000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.500000
> BUFFERPOOL
> size=2K,buffers=50000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.000000
> AUTO_LRU_TUNING 1
> BUFFERPOOL
> size=4K,buffers=1250000,lrus=16,lru_min_dirty=50.000000,lru_max_dirty=60.500000
> BUFFERPOOL
> size=8K,buffers=625000,lrus=16,lru_min_dirty=50.000000,lru_max_dirty=60.500000
> BUFFERPOOL
> size=16K,buffers=187000,lrus=8,lru_min_dirty=50.000000,lru_max_dirty=60.500000
> ---------------------------------------------
>
> The dbspaces of the instance are spread over the 16 LUNs on
> the DS8300 accessing directly the device (RAW - /dev/rdsk)
> Have dbspaces with page size of 4k,8k and 16k.
>
> The 2 Tables used for tests:
> ================================
> TABLE_A - A historical table
> ================================
> *data* size after load with HPL = 61.6 GB
> Partitioned in 6 fragments by expression (date field filtering by semester)
> Each fragment is in differ and exclusive dbspace (inhered from the old
> structure)
> dbspaces Page size = 16k
>
> Have 3 indexes , with the same expression partition , 6 fragments.
> Each index have your own and exclusive dbspace.
> dbspaces Page size = 8k
> index Tab_A_ix_A - size 2.4 GB when created
> index Tab_A_ix_B - size 2.4 GB when created
> index Tab_A_ix_C - size 2.4 GB when created
>
> ================================
> TABLE_B - Other historical table
> ================================
> *data* size after load with HPL = 81.3 GB
> Partitioned in 21 fragments by expression (date field filtering by semester)
> Each fragment is in differ and exclusive dbspace (inhered from the old
> structure)
> dbspaces Page size = 16k
>
> Have 3 indexes , with the same expression partition , so 21 fragments.
> Each index have your own and exclusive dbspace.
> dbspaces Page size = 8k
> index Tab_B_ix_A - size 7.3 GB when created
> index Tab_B_ix_B - size 7.6 GB when created
> index Tab_B_ix_C - size 13.7 GB when created
>
> Summary of Informix configuration:
> BufferPool - 2k=100MB , 4k=5Gb , 8k=5GB , 16k=2.9GB

--> 8KBbufferpool size 5GB, largest idx in this pool 13.7 GB

if you increase the 8KBbufferpool such, that you can cache more % of your index
and if you tune PDQ memory just so small, that you *do not* sort on disk (== no pg writes
seen on temp dbspace(es)) you will no longer see the cacheing effects in the
excess (== 'outside' IDS) memory --> AIO will no longer be of advantage.
If AIO is faster than KIO - even when you can use mountoptions like
forcedirectio - more often than not this indicates that bufferpool is sized
too small.

What I have to remark here with *sark on*
Even on a machine, which definely has a low memory thruput, cacheing
I/O instead of waiting *yawn* for I/O to a DS8300 is a good thing.
*sark off*

[sadly there is no OFF switch for a certain amount of backgroud sarkasm here]

16 LUNs into a DS8300 is not good, I recommend 64 LUNs at least, so you
can queue up more unsatisfied I/Os in parallel.
The microconfiguration on a DS8300 IS very important: You will want to
have long thin type striping, and *of course* NO RAID 5 or 6!

Who wanted to save 3 bucks on this cheap type of unit by reducing 'slack

disk space'?

Try to get the one responsible to be fired or at least

work for the competition with full salary from your company.

But as your timing values show, you are going down the nasty road.

On the laptop I write this, I can create a index of 20GB and
35 Bytes entry size off a 100GB table with max rsize 410 and avg rsize 375
in less than 7 mins.
Now this laptop IS a good machine (8GB, 256 GB SSD, 300 GB 7200 rpm disk)
but everyone and her sister can mail order it from alienware.com

> SharedMemory - 10GB
> DS_TOTAL_MEMORY - 9GB
>
> ===============================================
> ===============================================
> ===============================================
> To execute this tests, I execute the create index sequentially,
> with a checkpoint between them using PDQ = 100 and measuring the time
> with the command "time" on the dbaccess execution
> (Hardware Threads ID = 0 to 31)
>
> There is the tests results:
>
> - TEST 1
> Machine with 32 Threads ONLINE
> 8 CPU VPs without configuring AFFINITY
> Using KAIO - 4 AIO VPs
>
> TABLE_A - Tab_A_ix_A - 12m5s
> TABLE_A - Tab_A_ix_B - 11m40s
> TABLE_A - Tab_A_ix_C - 11m46s
> TABLE_B - Tab_B_ix_A - 50m33s
> TABLE_B - Tab_B_ix_B - 50m11s
> TABLE_B - Tab_B_ix_C - 90m24s

How long does it take to scan table B?
Try to find out where you lose the time.
Even your HW should be able to read a table
of 80GB in approx 20 minutes (I have seen more than 4GB per min
on a DS8300). It takes some time to write 7.3GB (the 2 smaller idxes)
but it should not be slower than writing 2GB / minute.
Where do you spend the rest? I have no explanation for 16 mins:
read table: 20 mins or better
write idx: 14 mins or better
sort: I can see no way to make it so slow, that it consumes 16 mins.
NO, a T5120 is not *that* slow ;)
But your total time is 50 mins. IFF you do not see excessive IO-waits.
If you see high IO waits (more than 20%) then your sysadmins must start to test
using bonnie++ or somesuch and reconfigure the no of LUNs, striping and
cache params of the DS8300. Or find the misconfig on the switch you might
have between the T5120 and the DS8300, or stop that other testing
using your bandwith on the way to DS8300, or stop doing test with backing
up the DS8300 to tape while you crate your indexes.

>
> - TEST 2
> Machine with 32 Threads ONLINE
> 3 CPU VPs with AFFINITY 10,16,24
> Using KAIO - 4 AIO VPs
>
> TABLE_A - Tab_A_ix_A - 13m5s
> TABLE_A - Tab_A_ix_B - 13m56s
> TABLE_A - Tab_A_ix_C - 16m12s
> TABLE_B - Tab_B_ix_A - 100m12s
> TABLE_B - Tab_B_ix_B - 96m17s
> TABLE_B - Tab_B_ix_C - 153m37s
>
> - TEST 3
> Machine with 32 Threads ONLINE
> 20 CPU VPs with AFFINITY 10-30
> Using KAIO - 4 AIO VPs
>
> TABLE_A - Tab_A_ix_A - 12m10s
> TABLE_A - Tab_A_ix_B - 11m38s
> TABLE_A - Tab_A_ix_C - 11m56s
> TABLE_B - Tab_B_ix_A - 46m47s
> TABLE_B - Tab_B_ix_B - 47m3s
> TABLE_B - Tab_B_ix_C - 90m51s
>
> - TEST 4
> Machine with 4 Threads ONLINE (0,8,16,24) - All other threads are hard
> disabled on the ILOM (console)
> 4 CPU VPs with AFFINITY 0,8,16,24
> Using KAIO - 4 AIO VPs
>
> TABLE_A - Tab_A_ix_A - 13m2s
> TABLE_A - Tab_A_ix_B - 14m11s
> TABLE_A - Tab_A_ix_C - 15m17s
> TABLE_B - Tab_B_ix_A - 79m51s
> TABLE_B - Tab_B_ix_B - 77m27s
> TABLE_B - Tab_B_ix_C - 129m24s
>
> - TEST 5
> Machine with 4 Threads ONLINE (0,8,16,24) - All other threads are hard
> disabled on the ILOM (console)
> Threads 16 and 24 configured to no-intr (no interruptable, don't do I/O),
> psradm used to do this.
> 4 CPU VPs without AFFINITY
> Using KAIO - 4 AIO VPs
>
> TABLE_A - Tab_A_ix_A - 15m2s
> TABLE_A - Tab_A_ix_B - 12m57s
> TABLE_A - Tab_A_ix_C - 13m18s
> TABLE_B - Tab_B_ix_A - 81m4s
> TABLE_B - Tab_B_ix_B - 76m55s
> TABLE_B - Tab_B_ix_C - 123m2s
>
> - TEST 6
> Machine with 32 Threads ONLINE
> 11 CPU VPs with AFFINITY10-20
> NOT Using KAIO (KAIOOFF=1)- 10 AIO VPs with AFFINITY21-30
>
> TABLE_A - Tab_A_ix_A - 11m19s
> TABLE_A - Tab_A_ix_B - 11m10s
> TABLE_A - Tab_A_ix_C - 11m47s
> TABLE_B - Tab_B_ix_A - 50m38s
> TABLE_B - Tab_B_ix_B - 49m55s
> TABLE_B - Tab_B_ix_C - 91m53s
>
> - TEST 7
> Machine with 32 Threads ONLINE
> Threads between 12 - 20 configured to no-intr (I changed this after oninit has
> been initialized to keep the affinity)
> 11 CPU VPs with AFFINITY10-20
> NOT Using KAIO - 10 AIO VPs with AFFINITY 21-30
>
> TABLE_A - Tab_A_ix_A - 11m26s
> TABLE_A - Tab_A_ix_B - 11m27s
> TABLE_A - Tab_A_ix_C - 11m17s
> TABLE_B - Tab_B_ix_A - 49m49s
> TABLE_B - Tab_B_ix_B - 51m6s
> TABLE_B - Tab_B_ix_C - 91m37s
>
> - TEST 8
> Machine with 32 Threads ONLINE
> Threads between 12 - 25 configured to no-intr (I changed this after oninit has
> been initialized to keep the affinity)
> 16 CPU VPs with AFFINITY 10-26
> NOT Using KAIO - 10 AIO VPs with AFFINITY 27-31 (2 AIO VPs per CPU)
>
> TABLE_A - Tab_A_ix_A - 10m56s
> TABLE_A - Tab_A_ix_B - 11m10s
> TABLE_A - Tab_A_ix_C - 11m
> TABLE_B - Tab_B_ix_A - 46m35s
> TABLE_B - Tab_B_ix_B - 41m41s
> TABLE_B - Tab_B_ix_C - 87m59s
>
> - TEST 9
> Machine with 32 Threads ONLINE
> 16 CPU VPs with AFFINITY 10-26
> NOT Using KAIO - 10 AIO VPs with AFFINITY 27-31 (2 AIO VPs per CPU)
>
> TABLE_A - Tab_A_ix_A - 10m27s
> TABLE_A - Tab_A_ix_B - 11m17s
> TABLE_A - Tab_A_ix_C - 10m48s
> TABLE_B - Tab_B_ix_A - 46m32s
> TABLE_B - Tab_B_ix_B - 47m5s
> TABLE_B - Tab_B_ix_C - 90m4s
>
>
> ____________________________________________________________________________________
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbuscados.yahoo.com
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe

Messages In This Thread

• Performance - Sun SPARC T2
  Cesar Inacio Martins -- Friday, 6 November 2009, at 12:42 p.m.
  • Re: Performance - Sun SPARC T2
    Richard Kofler -- Friday, 6 November 2009, at 1:50 p.m.
    • Re: Performance - Sun SPARC T2
      MIKE MAGIE -- Friday, 6 November 2009, at 1:57 p.m.
    • Re: Performance - Sun SPARC T2
      Cesar Inacio Martins -- Friday, 6 November 2009, at 3:00 p.m.
      • Re: Performance - Sun SPARC T2
        Richard Kofler -- Friday, 6 November 2009, at 4:06 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.