Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read


End of Support Dates

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

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

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.