cancel
Showing results for 
Search instead for 
Did you mean: 

LOAD TABLE slow performance in IQ16SP08PL24 vs. IQ15.4ESD5

Former Member
0 Kudos

Hi

I was testing load table in IQ16SP08PL24 vs. IQ15.4ESD5. Using the exact same cfg file (except IQ16 need -iqlm in the cfg), and use the exact same raw device for dbspace and same file system temp space. Same create table script, same load table script.

Load 110000000 rows table from gzip file

IQ16SP08PL24 took 1384 seconds while IQ15.4ESD5 took 319 seconds.

I used iostat to check io and cpu, IQ16SP08PL24 only 4% cpu usage during the load while IQ15.4 ESD5 has 35% cpu usage. Seems like IQ15.4 ESD5 using parallel loading while IQ16SP08PL24 use sing thread loading.

Tested with gunzip file, same performance, 1314 seconds vs. 313 seconds.

I am not using RLV, so not configuring RLV at all.

Then I downloaded IQ16SP08PL35, did the same LOAD TABLE test with the same script and cfg.file.

The performance was not really improving for gzip file loading, IQ16SP08PL35 took 1294 seconds, yet, it improved quite dramatically when loading gunzip file, it took 261 seconds. And iostat show cpu was up to 36%.

Just a note of it, IQ database was created with all default options for IQ16 and IQ15.4, no option added in the load table script and the DDL was using IQ UNIQUE(255).

Thanks for any tips to resolve this slow performance issue.

Accepted Solutions (0)

Answers (1)

Answers (1)

markmumy
Advisor
Advisor
0 Kudos

Don't use IQ unique in your DDL.  That option could hurt compression and thus load speed.  Best to leave Minimize_Storage ON in IQ 15 and don't set any options in IQ 16.  That way you get the best possible indexes and compression.

How long does it take you to gunzip the file at the OS level?

Mark

Former Member
0 Kudos

Mark

Thanks for the tips. I did have another test without using iq unique(255) in ddl but with minimize_storage='on' in IQ 15.4. For IQ 16, also has no option and without iq unique in ddl.

Here is the test result

IQ16SP08PL24 no iq unique, no option, gzip file, 1337 seconds

IQ154ESD5 no iq unique, minimize_storage='on', gzip file, 320 seconds

then test with gunzip file

IQ16SP08PL24 no iq unique, no option, gunzip file, 1323 seconds

IQ154ESD5 no iq unique, minimize_storage='on', gunzip file, 313 seconds

These are all new build IQ server, not an upgrade.

I did also download IQ16SP08PL35, ran exactly same test using same scripts and cfg.

IQ16SP08PL35 no iq unique, no option, gzip file, 1286 seconds

IQ16SP08PL35 no iq unique, no option, gunzip file, 242 seconds

With gzip file, PL35 improved a little, yet with gunzip file, it improved so much, 242 seconds, must faster than IQ 15.4

I did a test to gunzip the flat file on US, it took 144 seconds.

Here is the cfg file

[sybase@e8e4-dl380-02 demo]$ cat iqdemo.cfg

# iqdemo.cfg

# ------------------------------------------------------------

# Default startup parameters for the ASIQ demo database

# ------------------------------------------------------------

-n  iqdemo

-x  tcpip{port=4638}

# The following parameters are also found in the configuration file

# /optware/sybase/iq16sp08/IQ-16_0/scripts/default.cfg.  Any parameters not specified below

# and not in the start up parameter list, will be added by start_iq

# using default.cfg as a guide.

-c  512m

-gc 20

-gd all

-gl all

-gm 100

-gp 4096

#-iqnumbercpus 12

-iqlm 8192

-iqmc 8192

-iqtc 8192

-zr all

#-zs 2g

#-zn 3

#-zo errorlogfile

For IQ 15.4, -iqlm was commented out.

I did uncomment -iqnumbercpus and ran the test again, not too much of the difference, maybe because this is just a single run of LOAD TABLE.

Any idea of what else to try?

Thanks.

Chow-Yi

Former Member
0 Kudos

Mark

I did a little more test.  I download IQ16SP10PL2 to compare with IQ16SP08PL35. same cfg file, DDL, and load script. Same raw device.

create table has no iq unique syntax, and all option are default, so minimize_storage = 'off'

Interesting discovery.

Here is the result

IQ16SP10PL2 gzip file, with FORMAT BCP, took 1226 seconds

IQ16SP10PL2 gzip file, with FORMAT ASCII, took 1129 seconds

IQ16SP10PL2 gunzip file, with FORMAT BCP, took 253 seconds

IQ16SP10PL2 gunzip file, with FORMAT ASCII, took 131 seconds

vs.

IQ16SP08PL35, gzip file, with FORMAT BCP, took 1277 seconds

IQ16SP08PL35, gzip file, with FORMAT ASCII, took 1248 seconds

IQ16SP08PL35, gunzip file, with FORMAT BCP, took 273 seconds

IQ16SP08PL35, gunzip file, with FORMAT ASCII, took 157 seconds

For gunzip file

Seems like SP10PL2 is faster than SP08PL35 for 10-15 %.

And FORMAT ASCII is faster than FORMAT BCP for 80-90%

For gzip file, all much slower than IQ 15.4, even though SP10PL2 with ASCII did gain some improvement, yet, still a lot worse than IQ 15.4 ESD5.

Don't know what else I can tune to make it comparable to IQ15.4 when using gzip files.

Thanks for your help.

Chow-Yi

markmumy
Advisor
Advisor
0 Kudos

Im not sure how to explain why IQ 15 is faster for GZIP files.  It really doesn't make much sense as I don't think we changed that behavior in IQ 16.  the GZIP api and apps are single threaded.  There's not much that we can do to change that behavior as it is API driven.  There are many tools attempting to thread gzip (pigz among one of the most used) for parallel processing.

Based on your test of unzipping the file, it sounds like it took 144 seconds.  I would not expect IQ to run any faster than that for obvious reasons. 

I am wondering, though, if the gzip file path, internally to IQ is taking a different code path than before.  It almost sounds as if it is not using the full bulk loader interface any more.  Perhaps there was an underlying change in how gzip was handled in IQ 16 based on the changes to the bulk loader for bit indexes to make them fully parallel.

Have you tried using gunzip to a named pipe and have the LOAD TABLE read from the named pipe?

Mark

markmumy
Advisor
Advisor
0 Kudos

Just to make sure...  these are all server side loads, right?  LOAD TABLE ... FROM 'filename'...?  Or do you have USING CLIENT FILE in your syntax?

Mark

Former Member
0 Kudos

It is all server side, load table from 'a.dat.gz'

markmumy
Advisor
Advisor
0 Kudos

OK.  Can you also confirm if you have any LF indexes on the tables?  IN IQ 16 we do not want to use LF indexes any more and use HG indexes instead.

Mark

Former Member
0 Kudos

Yes. I do have LF. These are the same LF indexes from IQ 15.4 PROD server. I am just trying to do some test before we migrate to IQ 16.

markmumy
Advisor
Advisor
0 Kudos

OK, one other option would be to remove the LF indexes and make them HG indexes.  Our guidance since IQ 16 SP08 PL20 (Nov 2014) was to no longer use LF indexes and to use HG indexes instead.

Mark

Former Member
0 Kudos

Thanks for the notes.

May I ask how to gunzip to a named pipe and then feed into LOAD TABLE script?

markmumy
Advisor
Advisor
0 Kudos

You would do something like this.

-- Create the pipe using mknod (mknod p /tmp/my_pipe_name)

-- gunzip -c my_zip_file.zip > /tmp/my_pipe_name

From another connection:

-- use LOAD TABLE using FROM '/tmp/my_pipe_name' with all other options

Mark

Former Member
0 Kudos

I tried all suggested.

1. mknod  /tmp/file.pipe  p

2. zcat file.gz > /tmp/file.pipe

3. load table from /tmp/file.pipe

same elapsed as loading gzip file.

drop table

create table with no iq unique syntax (all option default, so minimize_storage='off')

I did not create any index,

load table from gzip file, same timing as all LF, HG. HNG indexes created

drop table

create table with no iq unique syntax

only create HG and HNG indexes, did not create any LF index

load table from gzip file, same timing.

Seems like LF index has nothing to do with this load table slowness
.

I observed the top duing the run,

when load gzip file, the cpu stay at 4%. and only 1 kondemand was running, only very few times it has 3 kondemand processes, but very short period of time

when load gunzip file with FORMAT BCP, cpu stay at 33%, about 3 kondemand processes always, sometimes it has up to 6 kondemands

when load gunzip file with FORMAT ACII, cpu stay at 98%, about 6-7 kondemand processes always, sometimes it has 13 kondemand processes, and it finish like 141 seconds

somehow, when load gzip, it is single thread, when loading gunzip, it became multi-thread.

don't know what else to try.

markmumy
Advisor
Advisor
0 Kudos

At this point, I would suggest opening a case with support.  Clearly there is something going on behind the scenes that we aren't seeing.  I am wondering if somehow the GZIP loader is not leveraging the parallel bulk loader as it was in previous versions.  Only support and their interface into engineering will be able to provide insight.

Mark

Former Member
0 Kudos

I used IQ16SP08PL35 ran twice again, one on gzip and the other on gunzip. Also, turn on query plan.

The major difference for these 2 query plans is single thread vs. multi-threads

Threads used for executing local invariant predicates 136291656

Max. Possible Parallel Arms 24

Max. Active parallel threads 1

Threads used for executing local invariant predicates 4104253896

Max. possible parallel arms 24

Max Active parallel threads 24

I already opened a case with SAP weeks ago, case ID is 663483/2015