IDS 10.00.xC6 performance
A user found inserting 40,000 rows via dbaccess took ~50s on Solaris 10 (IDS 10.00.FC6) versus 15s on Windows and 8s on Linux, all using default demo onconfig and cooked-file chunks, with low CPU and no visible I/O wait. Replies suggested the cooked/journalled filesystem was the likely culprit (UFS logging, double buffering, no direct I/O), checking page size and OS/DB config, and diagnosing with onstat -g ath/-g stk, onstat -p and prtdiag; one poster posted comparable Solaris timings as a baseline. The poster never followed up, so no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, currently I am involded in a Informix IDS performance issue on SUN Solaris 10 platform and I don't known how to identify the bottelneck. The problem : Inserts are slow compared to other platforms, like Linux or Windows. We load a test table with 40000 rows in dbaceess to prof the performance differences. The onconfig files are very simelar on all platforms. I used the default onconfig file created by the demo instance for my tests. All test systems used cooked file chunks. Results : 1.) IDS 10.00.FC6 Solaris 10 : ------------------------------ About 50 sec. During load the CPU ulilization is low, so it seems it is not an CPU issue. Further no IO wait is recognized with "top" unix command. Why is the import so slow on a UNIX server ? 2.) IDS 10.00.TC6 Windows 2003 (CPU: 2 GHz, RAM : 1 GByte) ---------------------------------------------------------- About 15 sec. 3.) IDS 10.00.UC6 Fedora Core 6 Linux (CPU: 2 GHz, RAM : 768 MByte) ------------------------------------------------------------------- About 8 sec !!! Both systems used in 2. and 3. are more or less old desktop systems. There is no special server hardware build in. It supprises me that this easy hardware is better then a UNIX server hardware. How could I identify the root cause for the bad performance on our SUN system ? Every advice is welcome. Best regards ... Joerg
Jörg Rüschenschmidt said: > Hi all, > > currently I am involded in a Informix IDS performance issue on SUN > Solaris 10 platform and I don't known how to identify the bottelneck. > > The problem : Inserts are slow compared to other platforms, like Linux > or Windows. We load a test table with 40000 rows in dbaceess to prof the > performance differences. > > The onconfig files are very simelar on all platforms. I used the default > onconfig file created by the demo instance for my tests. Errrrr.... > All test > systems used cooked file chunks. YUCK! Maybe the Sun is using a journalled file system? > Results : > > 1.) IDS 10.00.FC6 Solaris 10 : > ------------------------------ > About 50 sec. During load the CPU ulilization is low, so it seems it is > not an CPU issue. Further no IO wait is recognized with "top" unix > command. Why is the import so slow on a UNIX server ? > > 2.) IDS 10.00.TC6 Windows 2003 (CPU: 2 GHz, RAM : 1 GByte) > ---------------------------------------------------------- > About 15 sec. > > 3.) IDS 10.00.UC6 Fedora Core 6 Linux (CPU: 2 GHz, RAM : 768 MByte) > ------------------------------------------------------------------- > About 8 sec !!! > > Both systems used in 2. and 3. are more or less old desktop systems. > There is no special server hardware build in. It supprises me that this > easy hardware is better then a UNIX server hardware. > > How could I identify the root cause for the bad performance on our SUN > system ? > > Every advice is welcome. > > Best regards ... > > Joerg > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
On 15 Mrz., 16:12, Jörg Rüschenschmidt <jrueschenschm...@t-online.de> wrote: > Hi all, > > currently I am involded in a Informix IDS performance issue on SUN > Solaris 10 platform and I don't known how to identify the bottelneck. > > The problem : Inserts are slow compared to other platforms, like Linux > or Windows. We load a test table with 40000 rows in dbaceess to prof the > performance differences. > > The onconfig files are very simelar on all platforms. I used the default > onconfig file created by the demo instance for my tests. All test > systems used cooked file chunks. > > Results : > > 1.) IDS 10.00.FC6 Solaris 10 : > ------------------------------ > About 50 sec. During load the CPU ulilization is low, so it seems it is > not an CPU issue. Further no IO wait is recognized with "top" unix > command. Why is the import so slow on a UNIX server ? > > 2.) IDS 10.00.TC6 Windows 2003 (CPU: 2 GHz, RAM : 1 GByte) > ---------------------------------------------------------- > About 15 sec. > > 3.) IDS 10.00.UC6 Fedora Core 6 Linux (CPU: 2 GHz, RAM : 768 MByte) > ------------------------------------------------------------------- > About 8 sec !!! > > Both systems used in 2. and 3. are more or less old desktop systems. > There is no special server hardware build in. It supprises me that this > easy hardware is better then a UNIX server hardware. > > How could I identify the root cause for the bad performance on our SUN > system ? > > Every advice is welcome. > > Best regards ... > > Joerg Hi Joerg, a few weeks ago we migrate from 9.x to 10.0 on SUN Solaris boxes. There is no performance decrease, maybe there is a little bit faster reaction for OLTP with short and easy SQL-Stmts. So I would search for a configuration problem either on OS level or on DB level. Cooked chunks may not the best thing. Is the page size correct ? Regards Stefan
Use 'onstat -g ath' multiple times to see if relevant Ix threads (sqlexec) are doing what they should be - running.
If they aren't (and if they aren't 'ready' frequently), then they're waiting on something; either you're seeing already from this
onstat what this might be and can guess a reason, or you have a closer look at the threads' stacks (onstat -g stk <tid>, also
multiple times).
If they are, your problem most probably is below Ix.
HTH,
Andreas
Jï¿œrg Rï¿œschenschmidt wrote:
> Hi all,
>
> currently I am involded in a Informix IDS performance issue on SUN
> Solaris 10 platform and I don't known how to identify the bottelneck.
>
> The problem : Inserts are slow compared to other platforms, like Linux
> or Windows. We load a test table with 40000 rows in dbaceess to prof the
> performance differences.
>
> The onconfig files are very simelar on all platforms. I used the default
> onconfig file created by the demo instance for my tests. All test
> systems used cooked file chunks.
>
> Results :
>
> 1.) IDS 10.00.FC6 Solaris 10 :
> ------------------------------
> About 50 sec. During load the CPU ulilization is low, so it seems it is
> not an CPU issue. Further no IO wait is recognized with "top" unix
> command. Why is the import so slow on a UNIX server ?
>
> 2.) IDS 10.00.TC6 Windows 2003 (CPU: 2 GHz, RAM : 1 GByte)
> ----------------------------------------------------------
> About 15 sec.
>
> 3.) IDS 10.00.UC6 Fedora Core 6 Linux (CPU: 2 GHz, RAM : 768 MByte)
> -------------------------------------------------------------------
> About 8 sec !!!
>
> Both systems used in 2. and 3. are more or less old desktop systems.
> There is no special server hardware build in. It supprises me that this
> easy hardware is better then a UNIX server hardware.
>
> How could I identify the root cause for the bad performance on our SUN
> system ?
>
> Every advice is welcome.
>
> Best regards ...
>
> Joerg
I just did a load of ~130k rows (5.1 MB unload file size) with
echo "truncate table <tablename>;" | dbaccess <dbname>
onstat -z
echo "load from <tablename>.unl insert into <tablename>;" | time
dbaccess <dbname>
onstat -p
Database selected.
131804 row(s) loaded.
Database closed.
real 0m17.15s
user 0m2.49s
sys 0m0.08s
IBM Informix Dynamic Server Version 11.10.UB4TL -- On-Line -- Up 3 days
13:45:38 -- 705536 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits
%cached
0 0 1071759 100.00 9132 26252 408306
97.76
isamtot open start read write rewrite delete
commit rollbk
370812 51 63 301 123394 124 0
1 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 10.69 0.37 0 0
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits
compress seqscans
1 0 749001 0 0 0 4226
0
ixda-RA idx-RA da-RA RA-pgsused lchwaits
0 0 0 0 0
So dbaccess took 2.5 seconds of CPU time to load the file and send the
insert commands to the server.
The server processes took 11 secs CPU time to do the inserts.
So 17 - 2.5 - 11 = 3.5 s for IO.
What's your result?
How much data are 40000 rows (size of unload file)?
What indexes for the table getting loaded?
What kind of machine do you have (prtdiag -v)?
What's the logging mode of the database?
Solaris 10 does not report any IO wait anymore. Previous Solaris
versions did.
The default filesystem is ufs with logging.
Executing "mount" will show that logging is enabled and probably that
direct IO is not enabled. Data transfers are buffered twice.
Executing "mkfs -m <raw device>" will show the parameters used for file
system creation.
On 15 Mar, 15:12, Jörg Rüschenschmidt <jrueschenschm...@t-online.de> wrote: > Hi all, > > currently I am involded in a Informix IDS performance issue on SUN > Solaris 10 platform and I don't known how to identify the bottelneck. > Which hardware platform? What disk array? Fibre or SCSI? Which version 10 release? /usr/platform/sun4u/*bin/prtdiag -v gives what output? > The problem : Inserts are slow compared to other platforms, like Linux > or Windows. We load a test table with 40000 rows in dbaceess to prof the > performance differences. > > The onconfig files are very simelar on all platforms. I used the default > onconfig file created by the demo instance for my tests. All test > systems used cooked file chunks. > > Results : > > 1.) IDS 10.00.FC6 Solaris 10 : > ------------------------------ > About 50 sec. During load the CPU ulilization is low, so it seems it is > not an CPU issue. Further no IO wait is recognized with "top" unix > command. Why is the import so slow on a UNIX server ? > > 2.) IDS 10.00.TC6 Windows 2003 (CPU: 2 GHz, RAM : 1 GByte) > ---------------------------------------------------------- > About 15 sec. > > 3.) IDS 10.00.UC6 Fedora Core 6 Linux (CPU: 2 GHz, RAM : 768 MByte) > ------------------------------------------------------------------- > About 8 sec !!! > > Both systems used in 2. and 3. are more or less old desktop systems. > There is no special server hardware build in. It supprises me that this > easy hardware is better then a UNIX server hardware. > > How could I identify the root cause for the bad performance on our SUN > system ? > > Every advice is welcome. > > Best regards ... > > Joerg
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g