Insert into an indexed table is really SLOW!
A user loading 6 million rows into an Informix 11.50 table found loads took ~3.5 hours with three indexes present versus 10-20 minutes without them, via both dbload and HPL deluxe mode. Respondents blamed the near-default demo ONCONFIG and hardware rather than indexing itself: only 1000 buffers in the bufferpool, data and index I/O competing on one spindle, and tiny 16KB extent/next sizes causing huge extent counts. Suggestions included greatly increasing the bufferpool, LRUs and cleaners, sizing extents for the ~600,000-page table, using raw/non-journaled chunks and DIRECT_IO, raising the btscanner threshold so it doesn't run during the load, and dropping/rebuilding indexes or staging into a fragmented table and merging with PDQ. No follow-up from the poster confirming results is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am running into performance problems inserting into an indexed table, using
Informix 11.50. The following timings for loading into
compressed/uncompressed, indexed/non-indexed tables using dbload/HPL-Deluxe
more illustrate the problem. The number of rows inserted is 6 million.
compressed/indexed/DBLoad or HPL/h:mm:ss.d
Y/Y/DBLoad/3:47:39.9
Y/N/DBLoad/0:10:58.1
N/Y/DBLoad/3:31:13.4
N/N/DBLoad/0:22:46.6
Y/Y/HPL/3:26:17.6
Y/N/HPL/0:08:31.7
N/Y/HPL/3:28:16.1
N/N/HPL/0:18:45.7
Why are the inserts into the indexed tables so much slower? I would expect
some performance hit of course, but not this much. I want the indexes left
intact during the load, hence my use of HPL in Deluxe mode rather than Express
mode.
I'm using pretty much "out of the box" configuration parameters:
------------------------------------------------------------------------
onstat -c:
IBM Informix Dynamic Server Version 11.50.UC4 -- On-Line -- Up 4 days 17:59:
49 -- 38212 Kbytes
Configuration File: /opt/informix/informix.11.50_UC4/etc/onconfig.bcslinuxprodROOTNAME rootdbs
ROOTOFFSET 0MIRROR 0
MIRRORPATH /opt/IBM/informix/tmp/demo_on.root_mirror
MIRROROFFSET 0
PHYSFILE 200000
PLOG_OVERFLOW_PATH /opt/IBM/informix/tmp
PHYSBUFF 128
LOGFILES 49
LOGSIZE 10000
DYNAMIC_LOGS 2
LOGBUFF 64
LTXHWM 70
LTXEHWM 80
CONSOLE /work1/stat/bcslinuxprod_console.log
TBLTBLFIRST 0
TBLTBLNEXT 0
TBLSPACE_STATS 1
DBSPACETEMP temp1db
SBSPACETEMP temp1sb
SBSPACENAME temp1sb
SYSSBSPACENAME temp1sbifmx
ONDBSPACEDOWN 2
NETTYPE ipcshm,1,50,CPU
LISTEN_TIMEOUT 60
MAX_INCOMPLETE_CONNECTIONS 1024
FASTPOLL 1
MULTIPROCESSOR 0
VP_MEMORY_CACHE_KB 0
SINGLE_CPU_VP 0
CLEANERS 8AUTO_AIOVPS 1
DIRECT_IO 0
LOCKS 20000
DEF_TABLE_LOCKMODE page
RESIDENT 0
SHMBASE 0x44000000L
SHMVIRTSIZE 32656
SHMADD 8192
EXTSHMADD 8192
SHMTOTAL 0
SHMVIRT_ALLOCSEG 0,3
SHMNOACCESS
CKPTINTVL 300AUTO_CKPTS 1
RTO_SERVER_RESTART 0
BLOCKTIMEOUT 3600
TXTIMEOUT 300
DEADLOCK_TIMEOUT 60
HETERO_COMMIT 0
TAPEBLK 32
TAPESIZE 0
LTAPEBLK 32
LTAPESIZE 0
BAR_DEBUG 0
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 20
BAR_XFER_BUF_SIZE 31
RESTARTABLE_RESTORE ON
BAR_PROGRESS_FREQ 0
BAR_BSALIB_PATH
BACKUP_FILTER
RESTORE_FILTER
BAR_PERFORMANCE 0ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs
DD_HASHSIZE 31
DD_HASHMAX 10
DS_HASHSIZE 31
DS_POOLSIZE 127
PC_HASHSIZE 31
PC_POOLSIZE 127
STMT_CACHE 0
STMT_CACHE_HITS 0
STMT_CACHE_SIZE 512
STMT_CACHE_NOLIMIT 0
STMT_CACHE_NUMPOOL 1
USEOSTIME 0
STACKSIZE 32
ALLOW_NEWLINE 0
USELASTCOMMITTED NONE
FILLFACTOR 90
MAX_FILL_DATA_PAGES 0
ONLIDX_MAXMEM 5120
MAX_PDQPRIORITY 100
DS_MAX_QUERIES
DS_TOTAL_MEMORY
DS_MAX_SCANS 1048576
DS_NONPDQ_QUERY_MEM 128
DATASKIP
OPTCOMPIND 2
DIRECTIVES 1
EXT_DIRECTIVES 0
OPT_GOAL -1
IFX_FOLDVIEW 0AUTO_REPREPARE 1
RA_PAGES 64
RA_THRESHOLD 16
EXPLAIN_STAT 0
IFX_EXTEND_ROLE 0
SECURITY_LOCALCONNECTION
UNSECURE_ONSTAT
ADMIN_USER_MODE_WITH_DBSA
ADMIN_MODE_USERS
SSL_KEYSTORE_LABEL
PLCY_POOLSIZE 127
PLCY_HASHSIZE 31
USRC_POOLSIZE 127
USRC_HASHSIZE 31STAGEBLOB
OPCACHEMAX 0
ENCRYPT_HDR
ENCRYPT_SMX
ENCRYPT_CDR 0
ENCRYPT_CIPHERS
ENCRYPT_MAC
ENCRYPT_MACFILE
ENCRYPT_SWITCH
CDR_EVALTHREADS 1,2
CDR_DSLOCKWAIT 5
CDR_QUEUEMEM 4096
CDR_NIFCOMPRESS 0
CDR_SERIAL 0
CDR_DBSPACE
CDR_QHDR_DBSPACE
CDR_QDATA_SBSPACE
CDR_MAX_DYNAMIC_LOGS 0
CDR_SUPPRESS_ATSRISWARN
DRAUTO 0
DRINTERVAL 30
DRTIMEOUT 30
HA_ALIAS
DRIDXAUTO 0
LOG_INDEX_BUILDS
SDS_ENABLE
SDS_TIMEOUT 20
SDS_TEMPDBS
SDS_PAGINGREDIRECTED_WRITES 0
FAILOVER_CALLBACK
TEMPTAB_NOLOG 0
ON_RECVRY_THREADS 1
OFF_RECVRY_THREADS 10
DUMPSHMEM 1
DUMPGCORE 0
DUMPCORE 0
DUMPCNT 1
ALRM_ALL_EVENTS 0
STORAGE_FULL_ALARM 600,3
RAS_PLOG_SPEED 14900
RAS_LLOG_SPEED 742
EILSEQ_COMPAT_MODE 0
QSTATS 0
WSTATS 0
JVPJAVALIB /bin
JVPJAVAVM jvm
AUTO_LRU_TUNING 1
ROOTPATH /opt/IBM/informix/demo/server/online_root
MSGPATH /work1/stat/bcslinuxprod_online.log
TAPEDEV /dev/null
LTAPEDEV /dev/null
DBSERVERNAME bcslinuxprod
DBSERVERALIASES
SERVERNUM 0
ALARMPROGRAM /opt/IBM/informix/etc/alarmprogram.sh
DRLOSTFOUND /opt/IBM/informix/etc/dr.lostfound
BAR_ACT_LOG /work1/stat/bar_act.log
BAR_DEBUG_LOG /work1/stat/bar_debug.log
SYSALARMPROGRAM /opt/IBM/informix/etc/evidence.sh
DUMPDIR /work1/stat
JVPJAVAHOME /opt/IBM/informix/extend/krakatoa/jre
JVPHOME /opt/IBM/informix/extend/krakatoa/
JVPPROPFILE /opt/IBM/informix/extend/krakatoa/.jvpprops
JVPLOGFILE /opt/IBM/informix/demo/server/jvp.log
JVPCLASSPATH
/opt/IBM/informix/extend/krakatoa/krakatoa.jar:/opt/IBM/informix/ex
tend/krakatoa/jdbc.jar
ROOTSIZE 200000
BUFFERPOOL size=2K,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirt
y=60.000000
VPCLASS cpu,num=1,noage
BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6
UPDATABLE_SECONDARY 0
--------------------------------------------------------------------
Here is my table definition:
--------------------------------------------------------------------
create table "mdwilkie".noaa_sst_conventional_hpl_comp_24000000
(
type_of_observation integer,
source_of_observation integer,
dt datetime year to second,
latitude float,
longitude float,
sst float,
reliability integer,
solar_zenith_angle float,
satellite_zenith_angle float,
analyzed_field_sst float,
internal_error float,
solar_azimuth_angle float,
climatological_sst float,
beginning_row integer,
beginning_column integer,
avhrr_ch_1_average float,
avhrr_ch_2_average float,
avhrr_ch_3_average float,
avhrr_ch_4_average float,
avhrr_ch_5_average float,
space_view_ch_1 float,
space_view_ch_2 float,
space_view_ch_3 float,
ch_4_blackbody_temperature float,
ch_5_blackbody_temperature float,
year_of_observation integer
) extent size 16 next size 16 lock mode page;
start violations table for "mdwilkie".noaa_sst_conventional_hpl_comp_24000000
using noaa_sst_conventional_hpl_comp_24000000_vio,
noaa_sst_conventional_hpl_comp_24000000_dia;
create index "mdwilkie".noaa_sst_conventional_hpl_comp_24000000_dt_idx
on "mdwilkie".noaa_sst_conventional_hpl_comp_24000000 (dt)
using btree in miscdbind;
create index "mdwilkie".noaa_sst_conventional_hpl_comp_24000000_latitude_idx
on "mdwilkie".noaa_sst_conventional_hpl_comp_24000000 (latitude)
using btree in miscdbind;
create index "mdwilkie".noaa_sst_conventional_hpl_comp_24000000_longitude_idx
on "mdwilkie".noaa_sst_conventional_hpl_comp_24000000 (longitude)
using btree in miscdbind;
--------------------------------------------------------------------------------
Note th
MIKE DUNHAM-WILKIE wrote:
> I am running into performance problems inserting into an indexed table, using
> Informix 11.50. The following timings for loading into
> compressed/uncompressed, indexed/non-indexed tables using dbload/HPL-Deluxe
> more illustrate the problem. The number of rows inserted is 6 million.
>
> compressed/indexed/DBLoad or HPL/h:mm:ss.d
> Y/Y/DBLoad/3:47:39.9
> Y/N/DBLoad/0:10:58.1
> N/Y/DBLoad/3:31:13.4
> N/N/DBLoad/0:22:46.6
> Y/Y/HPL/3:26:17.6
> Y/N/HPL/0:08:31.7
> N/Y/HPL/3:28:16.1
> N/N/HPL/0:18:45.7
>
> Why are the inserts into the indexed tables so much slower? I would expect
> some performance hit of course, but not this much. I want the indexes left
> intact during the load, hence my use of HPL in Deluxe mode rather than
Express
> mode.
>
> I'm using pretty much "out of the box" configuration parameters:
>
> ------------------------------------------------------------------------
> onstat -c:>
> IBM Informix Dynamic Server Version 11.50.UC4 -- On-Line -- Up 4 days 17:59:
> 49 -- 38212 Kbytes> Configuration File:
/opt/informix/informix.11.50_UC4/etc/onconfig.bcslinuxprod
> ROOTNAME rootdbs
> ROOTOFFSET 0> MIRROR 0
> MIRRORPATH /opt/IBM/informix/tmp/demo_on.root_mirror
> MIRROROFFSET 0
> PHYSFILE 200000
> PLOG_OVERFLOW_PATH /opt/IBM/informix/tmp
> PHYSBUFF 128
> LOGFILES 49
> LOGSIZE 10000
> DYNAMIC_LOGS 2
> LOGBUFF 64
> LTXHWM 70
> LTXEHWM 80
> CONSOLE /work1/stat/bcslinuxprod_console.log
> TBLTBLFIRST 0
> TBLTBLNEXT 0
> TBLSPACE_STATS 1
> DBSPACETEMP temp1db
> SBSPACETEMP temp1sb
> SBSPACENAME temp1sb
> SYSSBSPACENAME temp1sbifmx
> ONDBSPACEDOWN 2
> NETTYPE ipcshm,1,50,CPU
> LISTEN_TIMEOUT 60
> MAX_INCOMPLETE_CONNECTIONS 1024
> FASTPOLL 1
> MULTIPROCESSOR 0
> VP_MEMORY_CACHE_KB 0
> SINGLE_CPU_VP 0
> CLEANERS 8> AUTO_AIOVPS 1
> DIRECT_IO 0
> LOCKS 20000
> DEF_TABLE_LOCKMODE page
> RESIDENT 0
> SHMBASE 0x44000000L
> SHMVIRTSIZE 32656
> SHMADD 8192
> EXTSHMADD 8192
> SHMTOTAL 0
> SHMVIRT_ALLOCSEG 0,3
> SHMNOACCESS
> CKPTINTVL 300> AUTO_CKPTS 1
> RTO_SERVER_RESTART 0
> BLOCKTIMEOUT 3600
> TXTIMEOUT 300
> DEADLOCK_TIMEOUT 60
> HETERO_COMMIT 0
> TAPEBLK 32
> TAPESIZE 0
> LTAPEBLK 32
> LTAPESIZE 0
> BAR_DEBUG 0
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 20
> BAR_XFER_BUF_SIZE 31
> RESTARTABLE_RESTORE ON
> BAR_PROGRESS_FREQ 0
> BAR_BSALIB_PATH
> BACKUP_FILTER
> RESTORE_FILTER
> BAR_PERFORMANCE 0> ISM_DATA_POOL ISMData
> ISM_LOG_POOL ISMLogs
> DD_HASHSIZE 31
> DD_HASHMAX 10
> DS_HASHSIZE 31
> DS_POOLSIZE 127
> PC_HASHSIZE 31
> PC_POOLSIZE 127
> STMT_CACHE 0
> STMT_CACHE_HITS 0
> STMT_CACHE_SIZE 512
> STMT_CACHE_NOLIMIT 0
> STMT_CACHE_NUMPOOL 1
> USEOSTIME 0
> STACKSIZE 32
> ALLOW_NEWLINE 0
> USELASTCOMMITTED NONE
> FILLFACTOR 90
> MAX_FILL_DATA_PAGES 0
> ONLIDX_MAXMEM 5120
> MAX_PDQPRIORITY 100
> DS_MAX_QUERIES
> DS_TOTAL_MEMORY
> DS_MAX_SCANS 1048576
> DS_NONPDQ_QUERY_MEM 128
> DATASKIP
> OPTCOMPIND 2
> DIRECTIVES 1
> EXT_DIRECTIVES 0
> OPT_GOAL -1
> IFX_FOLDVIEW 0> AUTO_REPREPARE 1
> RA_PAGES 64
> RA_THRESHOLD 16
> EXPLAIN_STAT 0
> IFX_EXTEND_ROLE 0
> SECURITY_LOCALCONNECTION
> UNSECURE_ONSTAT
> ADMIN_USER_MODE_WITH_DBSA
> ADMIN_MODE_USERS
> SSL_KEYSTORE_LABEL
> PLCY_POOLSIZE 127
> PLCY_HASHSIZE 31
> USRC_POOLSIZE 127
> USRC_HASHSIZE 31> STAGEBLOB
> OPCACHEMAX 0
> ENCRYPT_HDR
> ENCRYPT_SMX
> ENCRYPT_CDR 0
> ENCRYPT_CIPHERS
> ENCRYPT_MAC
> ENCRYPT_MACFILE
> ENCRYPT_SWITCH
> CDR_EVALTHREADS 1,2
> CDR_DSLOCKWAIT 5
> CDR_QUEUEMEM 4096
> CDR_NIFCOMPRESS 0
> CDR_SERIAL 0
> CDR_DBSPACE
> CDR_QHDR_DBSPACE
> CDR_QDATA_SBSPACE
> CDR_MAX_DYNAMIC_LOGS 0
> CDR_SUPPRESS_ATSRISWARN
> DRAUTO 0
> DRINTERVAL 30
> DRTIMEOUT 30
> HA_ALIAS
> DRIDXAUTO 0
> LOG_INDEX_BUILDS
> SDS_ENABLE
> SDS_TIMEOUT 20
> SDS_TEMPDBS
> SDS_PAGING> REDIRECTED_WRITES 0
> FAILOVER_CALLBACK
> TEMPTAB_NOLOG 0
> ON_RECVRY_THREADS 1
> OFF_RECVRY_THREADS 10
> DUMPSHMEM 1
> DUMPGCORE 0
> DUMPCORE 0
> DUMPCNT 1
> ALRM_ALL_EVENTS 0
> STORAGE_FULL_ALARM 600,3
> RAS_PLOG_SPEED 14900
> RAS_LLOG_SPEED 742
> EILSEQ_COMPAT_MODE 0
> QSTATS 0
> WSTATS 0
> JVPJAVALIB /bin
> JVPJAVAVM jvm
> AUTO_LRU_TUNING 1
> ROOTPATH /opt/IBM/informix/demo/server/online_root
> MSGPATH /work1/stat/bcslinuxprod_online.log
> TAPEDEV /dev/null
> LTAPEDEV /dev/null
> DBSERVERNAME bcslinuxprod
> DBSERVERALIASES
> SERVERNUM 0
> ALARMPROGRAM /opt/IBM/informix/etc/alarmprogram.sh
> DRLOSTFOUND /opt/IBM/informix/etc/dr.lostfound
> BAR_ACT_LOG /work1/stat/bar_act.log
> BAR_DEBUG_LOG /work1/stat/bar_debug.log
> SYSALARMPROGRAM /opt/IBM/informix/etc/evidence.sh
> DUMPDIR /work1/stat
> JVPJAVAHOME /opt/IBM/informix/extend/krakatoa/jre
> JVPHOME /opt/IBM/informix/extend/krakatoa/
> JVPPROPFILE /opt/IBM/informix/extend/krakatoa/.jvpprops
> JVPLOGFILE /opt/IBM/informix/demo/server/jvp.log
> JVPCLASSPATH
> /opt/IBM/informix/extend/krakatoa/krakatoa.jar:/opt/IBM/informix/ex
> tend/krakatoa/jdbc.jar
> ROOTSIZE 200000
> BUFFERPOOL size=2K,buffers=1000,lrus=8,lru_min_dirty=50.000000,lru_max_dirt
> y=60.000000
> VPCLASS cpu,num=1,noage
> BTSCANNER num=1,threshold=5000,rangesize=-1,alice=6
> UPDATABLE_SECONDARY 0
> -------------------------------------------------------------------->
> Here is my table definition:
>
> --------------------------------------------------------------------
> create table "mdwilkie".noaa_sst_conventional_hpl_comp_24000000
> (
>
> type_of_observation integer,
>
> source_of_observation integer,
>
> dt datetime year to second,
>
> latitude float,
>
> longitude float,
>
> sst float,
>
> reliability integer,
>
> solar_zenith_angle float,
>
> satellite_zenith_angle float,
>
> analyzed_field_sst float,
>
> internal_error float,
>
> solar_azimuth_angle float,
>
> climatological_sst float,
>
> beginning_row integer,
>
> beginning_column integer,
>
> avhrr_ch_1_average float,
>
> avhrr_ch_2_average float,
>
> avhrr_ch_3_average float,
>
> avhrr_ch_4_average float,
>
> avhrr_ch_5_average float,
>
> space_view_ch_1 float,
>
> space_view_ch_2 float,
>
> space_view_ch_3 float,
>
> ch_4_blackbody_temperature float,
>
> ch_5_blackbody_temperature float,
>
> year_of_observation integer
> ) extent size 16 next size 16 lock mode page;
>
> start violations table for "mdwilkie".noaa_sst_conventional_hpl_comp_24000000
>
> using noaa_sst_conventional_hpl_comp_24000000_vio,
> noaa_sst_conventional_hpl_comp_24000000_dia;
>
> create index "mdwilkie".noaa_sst_conventional_hpl_comp_24000000_dt_idx
>
> on "mdwilkie".noaa_sst_conventional_hpl_comp_24000000 (dt)
>@
I think that you have two problems:
1. Asking the engine to do the index IO at the same time as the table
data IO all to the different sections of the same single spindle is just
impractical.
2. You only have 1000 buffers configures so the engine is constantly
having to write those pages to disk and wait for the IOs to complete.
Suggestions? Obviously I think you need additional spindles, but that
aside, add more buffers, many more buffers. You have 6 million rows to load
at 240 bytes each so the data will require almost 3000 pages plus the
indexes so if you make the cache 4000 pages you may get the entire load into
memory at the same time. Honestly though, even 4000 cache pages is small,
that's not much of a live working set. The machine has 2GB of memory with
over 50MB free. IDS is currently using only 38MB of memory. If you
increase the buffer cache from 1000 pages to 10000 pages you will add only
20MB to the server's memory footprint and all of your operations will be
faster and smoother.
Another thing, are you using COOKED (filesystem) chunks or RAW? If
filesystem chunks then you should enable DIRECT_IO (which is currently
disabled in the ONCONFIG file) so that the engine can use KAIO threads for
IO instead of AIO VPs. KAIO is faster and more efficient in system
resources used. Also, if filesystem chunks what kind of filesystem? EXT4
is VERY BAD for database chunks and EXT3 isn't much better. If you want to
maximize your IO performance you should put the chunks in RAW space, or use
a COOKED (block) device rather than a filesystem, or at least put them in a
non-journaled filesystem like EXT2.
FYI: Linus Torvalds says that EXT4 (and EXT3 with write-back enabled) is not
safe, database performance issues aside!
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Jul 7, 2009 at 1:06 PM, MIKE DUNHAM-WILKIE <mike@barrodale.com>wrote:
> I am running into performance problems inserting into an indexed table,
> using
> Informix 11.50. The following timings for loading into
> compressed/uncompressed, indexed/non-indexed tables using dbload/HPL-Deluxe
> more illustrate the problem. The number of rows inserted is 6 million.
>
> compressed/indexed/DBLoad or HPL/h:mm:ss.d
> Y/Y/DBLoad/3:47:39.9
> Y/N/DBLoad/0:10:58.1
> N/Y/DBLoad/3:31:13.4
> N/N/DBLoad/0:22:46.6
> Y/Y/HPL/3:26:17.6
> Y/N/HPL/0:08:31.7
> N/Y/HPL/3:28:16.1
> N/N/HPL/0:18:45.7
>
> Why are the inserts into the indexed tables so much slower? I would expect
> some performance hit of course, but not this much. I want the indexes left
> intact during the load, hence my use of HPL in Deluxe mode rather than
> Express
> mode.
>
> I'm using pretty much "out of the box" configuration parameters:
>
> ------------------------------------------------------------------------
> onstat -c:>
> IBM Informix Dynamic Server Version 11.50.UC4 -- On-Line -- Up 4 days
> 17:59:
> 49 -- 38212 Kbytes> Configuration File:
> /opt/informix/informix.11.50_UC4/etc/onconfig.bcslinuxprod
> ROOTNAME rootdbs
> ROOTOFFSET 0> MIRROR 0
> MIRRORPATH /opt/IBM/informix/tmp/demo_on.root_mirror
> MIRROROFFSET 0
> PHYSFILE 200000
> PLOG_OVERFLOW_PATH /opt/IBM/informix/tmp
> PHYSBUFF 128
> LOGFILES 49
> LOGSIZE 10000
> DYNAMIC_LOGS 2
> LOGBUFF 64
> LTXHWM 70
> LTXEHWM 80
> CONSOLE /work1/stat/bcslinuxprod_console.log
> TBLTBLFIRST 0
> TBLTBLNEXT 0
> TBLSPACE_STATS 1
> DBSPACETEMP temp1db
> SBSPACETEMP temp1sb
> SBSPACENAME temp1sb
> SYSSBSPACENAME temp1sbifmx
> ONDBSPACEDOWN 2
> NETTYPE ipcshm,1,50,CPU
> LISTEN_TIMEOUT 60
> MAX_INCOMPLETE_CONNECTIONS 1024
> FASTPOLL 1
> MULTIPROCESSOR 0
> VP_MEMORY_CACHE_KB 0
> SINGLE_CPU_VP 0
> CLEANERS 8> AUTO_AIOVPS 1
> DIRECT_IO 0
> LOCKS 20000
> DEF_TABLE_LOCKMODE page
> RESIDENT 0
> SHMBASE 0x44000000L
> SHMVIRTSIZE 32656
> SHMADD 8192
> EXTSHMADD 8192
> SHMTOTAL 0
> SHMVIRT_ALLOCSEG 0,3
> SHMNOACCESS
> CKPTINTVL 300> AUTO_CKPTS 1
> RTO_SERVER_RESTART 0
> BLOCKTIMEOUT 3600
> TXTIMEOUT 300
> DEADLOCK_TIMEOUT 60
> HETERO_COMMIT 0
> TAPEBLK 32
> TAPESIZE 0
> LTAPEBLK 32
> LTAPESIZE 0
> BAR_DEBUG 0
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 20
> BAR_XFER_BUF_SIZE 31
> RESTARTABLE_RESTORE ON
> BAR_PROGRESS_FREQ 0
> BAR_BSALIB_PATH
> BACKUP_FILTER
> RESTORE_FILTER
> BAR_PERFORMANCE 0> ISM_DATA_POOL ISMData
> ISM_LOG_POOL ISMLogs
> DD_HASHSIZE 31
> DD_HASHMAX 10
> DS_HASHSIZE 31
> DS_POOLSIZE 127
> PC_HASHSIZE 31
> PC_POOLSIZE 127
> STMT_CACHE 0
> STMT_CACHE_HITS 0
> STMT_CACHE_SIZE 512
> STMT_CACHE_NOLIMIT 0
> STMT_CACHE_NUMPOOL 1
> USEOSTIME 0
> STACKSIZE 32
> ALLOW_NEWLINE 0
> USELASTCOMMITTED NONE
> FILLFACTOR 90
> MAX_FILL_DATA_PAGES 0
> ONLIDX_MAXMEM 5120
> MAX_PDQPRIORITY 100
> DS_MAX_QUERIES
> DS_TOTAL_MEMORY
> DS_MAX_SCANS 1048576
> DS_NONPDQ_QUERY_MEM 128
> DATASKIP
> OPTCOMPIND 2
> DIRECTIVES 1
> EXT_DIRECTIVES 0
> OPT_GOAL -1
> IFX_FOLDVIEW 0> AUTO_REPREPARE 1
> RA_PAGES 64
> RA_THRESHOLD 16
> EXPLAIN_STAT 0
> IFX_EXTEND_ROLE 0
> SECURITY_LOCALCONNECTION
> UNSECURE_ONSTAT
> ADMIN_USER_MODE_WITH_DBSA
> ADMIN_MODE_USERS
> SSL_KEYSTORE_LABEL
> PLCY_POOLSIZE 127
> PLCY_HASHSIZE 31
> USRC_POOLSIZE 127
> USRC_HASHSIZE 31> STAGEBLOB
> OPCACHEMAX 0
> ENCRYPT_HDR
> ENCRYPT_SMX
> ENCRYPT_CDR 0
> ENCRYPT_CIPHERS
> ENCRYPT_MAC
> ENCRYPT_MACFILE
> ENCRYPT_SWITCH
> CDR_EVALTHREADS 1,2
> CDR_DSLOCKWAIT 5
> CDR_QUEUEMEM 4096
> CDR_NIFCOMPRESS 0
> CDR_SERIAL 0
> CDR_DBSPACE
> CDR_QHDR_DBSPACE
> CDR_QDATA_SBSPACE
> CDR_MAX_DYNAMIC_LOGS 0
> CDR_SUPPRESS_ATSRISWARN
> DRAUTO 0
> DRINTERVAL 30
> DRTIMEOUT 30
> HA_ALIAS
> DRIDXAUTO 0
> LOG_INDEX_BUILDS
> SDS_ENABLE
> SDS_TIMEOUT 20
> SDS_TEMPDBS
> SDS_PAGING> REDIRECTED_WRITES 0
> FAILOVER_CALLBACK
> TEMPTAB_NOLOG 0
> ON_RECVRY_THREADS 1
> OFF_RECVRY_THREADS 10
> DUMPSHMEM 1
> DUMPGCORE 0
> DUMPCORE 0
> DUMPCNT 1
> ALRM_ALL_EVENTS 0
> STORAGE_FULL_ALARM 600,3
> RAS_PLOG_SPEED 14900
> RAS_LLOG_SPEED 742
> EILSEQ_COMPAT_MODE 0
> QSTATS 0
> WSTATS 0
> JVPJAVALIB /bin
> JVPJAVAVM jvm
> AUTO_LRU_TUNING 1
> ROOTPATH /opt/IBM/informix/demo/server/online_root
> MSGPATH /work1/stat/bcslinuxprod_online.log
> TAPEDEV /dev/null
> LTAPEDEV /dev/null
> DBSERVERNAME bcslinuxprod
> DBSERVERALIASES
> SERVERNUM 0
> ALARMPROGRAM /opt/IBM/informix/etc/alarmprogram.sh
> DRLOSTFOUND /opt/IBM/informix/etc/dr.lostfound
> BAR_ACT_LOG /
> compressed/indexed/DBLoad or HPL/h:mm:ss.d
> Y/Y/DBLoad/3:47:39.9
> Y/N/DBLoad/0:10:58.1
> N/Y/DBLoad/3:31:13.4
> N/N/DBLoad/0:22:46.6
> Y/Y/HPL/3:26:17.6
> Y/N/HPL/0:08:31.7
> N/Y/HPL/3:28:16.1
> N/N/HPL/0:18:45.7
8 min to load 6 million rows is not excessive, I'd like to get loaded that
fast. I have to wonder about the initial size of the table and the number of
extents on the indexes. I bet the indexes have mucho extents that's why you
don't see much difference between DELUX mode and dbload. Just one pass on the
indexes is killing you. Any thing you can do to improve the index building
phase will pay off big. Consider more virtual memory, buffers and how but temp
space?
That calculation of the number of pages being added to the table was
bothering me, and with good reason. The 6millions rows of 240 bytes each
don't take up 3000 pages, they take up a bit over 71,000 pages! All the
more reason to max out the buffer cache!
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Jul 7, 2009 at 1:42 PM, Art Kagel <art.kagel@gmail.com> wrote:
> I think that you have two problems:
>
>
> 1. Asking the engine to do the index IO at the same time as the table
> data IO all to the different sections of the same single spindle is just
> impractical.
> 2. You only have 1000 buffers configures so the engine is constantly
> having to write those pages to disk and wait for the IOs to complete.
>
> Suggestions? Obviously I think you need additional spindles, but that
> aside, add more buffers, many more buffers. You have 6 million rows to load
> at 240 bytes each so the data will require almost 3000 pages plus the
> indexes so if you make the cache 4000 pages you may get the entire load into
> memory at the same time. Honestly though, even 4000 cache pages is small,
> that's not much of a live working set. The machine has 2GB of memory with
> over 50MB free. IDS is currently using only 38MB of memory. If you
> increase the buffer cache from 1000 pages to 10000 pages you will add only
> 20MB to the server's memory footprint and all of your operations will be
> faster and smoother.
>
> Another thing, are you using COOKED (filesystem) chunks or RAW? If
> filesystem chunks then you should enable DIRECT_IO (which is currently
> disabled in the ONCONFIG file) so that the engine can use KAIO threads for
> IO instead of AIO VPs. KAIO is faster and more efficient in system
> resources used. Also, if filesystem chunks what kind of filesystem? EXT4
> is VERY BAD for database chunks and EXT3 isn't much better. If you want to
> maximize your IO performance you should put the chunks in RAW space, or use
> a COOKED (block) device rather than a filesystem, or at least put them in a
> non-journaled filesystem like EXT2.
>
> FYI: Linus Torvalds says that EXT4 (and EXT3 with write-back enabled) is
> not safe, database performance issues aside!
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
>
>
> On Tue, Jul 7, 2009 at 1:06 PM, MIKE DUNHAM-WILKIE <mike@barrodale.com>wrote:
>
>> I am running into performance problems inserting into an indexed table,
>> using
>> Informix 11.50. The following timings for loading into
>> compressed/uncompressed, indexed/non-indexed tables using
>> dbload/HPL-Deluxe
>> more illustrate the problem. The number of rows inserted is 6 million.
>>
>> compressed/indexed/DBLoad or HPL/h:mm:ss.d
>> Y/Y/DBLoad/3:47:39.9
>> Y/N/DBLoad/0:10:58.1
>> N/Y/DBLoad/3:31:13.4
>> N/N/DBLoad/0:22:46.6
>> Y/Y/HPL/3:26:17.6
>> Y/N/HPL/0:08:31.7
>> N/Y/HPL/3:28:16.1
>> N/N/HPL/0:18:45.7
>>
>> Why are the inserts into the indexed tables so much slower? I would expect
>> some performance hit of course, but not this much. I want the indexes left
>> intact during the load, hence my use of HPL in Deluxe mode rather than
>> Express
>> mode.
>>
>> I'm using pretty much "out of the box" configuration parameters:
>>
>> ------------------------------------------------------------------------
>> onstat -c:>>
>> IBM Informix Dynamic Server Version 11.50.UC4 -- On-Line -- Up 4 days
>> 17:59:
>> 49 -- 38212 Kbytes>> Configuration File:
>> /opt/informix/informix.11.50_UC4/etc/onconfig.bcslinuxprod
>> ROOTNAME rootdbs
>> ROOTOFFSET 0>> MIRROR 0
>> MIRRORPATH /opt/IBM/informix/tmp/demo_on.root_mirror
>> MIRROROFFSET 0
>> PHYSFILE 200000
>> PLOG_OVERFLOW_PATH /opt/IBM/informix/tmp
>> PHYSBUFF 128
>> LOGFILES 49
>> LOGSIZE 10000
>> DYNAMIC_LOGS 2
>> LOGBUFF 64
>> LTXHWM 70
>> LTXEHWM 80
>> CONSOLE /work1/stat/bcslinuxprod_console.log
>> TBLTBLFIRST 0
>> TBLTBLNEXT 0
>> TBLSPACE_STATS 1
>> DBSPACETEMP temp1db
>> SBSPACETEMP temp1sb
>> SBSPACENAME temp1sb
>> SYSSBSPACENAME temp1sbifmx
>> ONDBSPACEDOWN 2
>> NETTYPE ipcshm,1,50,CPU
>> LISTEN_TIMEOUT 60
>> MAX_INCOMPLETE_CONNECTIONS 1024
>> FASTPOLL 1
>> MULTIPROCESSOR 0
>> VP_MEMORY_CACHE_KB 0
>> SINGLE_CPU_VP 0
>> CLEANERS 8>> AUTO_AIOVPS 1
>> DIRECT_IO 0
>> LOCKS 20000
>> DEF_TABLE_LOCKMODE page
>> RESIDENT 0
>> SHMBASE 0x44000000L
>> SHMVIRTSIZE 32656
>> SHMADD 8192
>> EXTSHMADD 8192
>> SHMTOTAL 0
>> SHMVIRT_ALLOCSEG 0,3
>> SHMNOACCESS
>> CKPTINTVL 300>> AUTO_CKPTS 1
>> RTO_SERVER_RESTART 0
>> BLOCKTIMEOUT 3600
>> TXTIMEOUT 300
>> DEADLOCK_TIMEOUT 60
>> HETERO_COMMIT 0
>> TAPEBLK 32
>> TAPESIZE 0
>> LTAPEBLK 32
>> LTAPESIZE 0
>> BAR_DEBUG 0
>> BAR_MAX_BACKUP 0
>> BAR_RETRY 1
>> BAR_NB_XPORT_COUNT 20
>> BAR_XFER_BUF_SIZE 31
>> RESTARTABLE_RESTORE ON
>> BAR_PROGRESS_FREQ 0
>> BAR_BSALIB_PATH
>> BACKUP_FILTER
>> RESTORE_FILTER
>> BAR_PERFORMANCE 0>> ISM_DATA_POOL ISMData
>> ISM_LOG_POOL ISMLogs
>> DD_HASHSIZE 31
>> DD_HASHMAX 10
>> DS_HASHSIZE 31
>> DS_POOLSIZE 127
>> PC_HASHSIZE 31
>> PC_POOLSIZE 127
>> STMT_CACHE 0
>> STMT_CACHE_HITS 0
>> STMT_CACHE_SIZE 512
>> STMT_CACHE_NOLIMIT 0
>> STMT_CACHE_NUMPOOL 1
>> USEOSTIME 0
>> STACKSIZE 32
>> ALLOW_NEWLINE 0
>> USELASTCOMMITTED NONE
>> FILLFACTOR 90
>> MAX_FILL_DATA_PAGES 0
>> ONLIDX_MAXMEM 5120
>> MAX_PDQPRIORITY 100
>> DS_MAX_QUERIES
>> DS_TOTAL_MEMORY
>> DS_MAX_SCANS 1048576
>> DS_NONPDQ_QUERY_MEM 128
>> DATASKIP
>> OPTCOMPIND 2
>> DIRECTIVES 1
>> EXT_DIRECTIVES 0
>> OPT_GOAL -1
>> IFX_FOLDVIEW 0>> AUTO_REPREPARE 1
>> RA_PAGES 64
>> RA_THRESHOLD 16
>> EXPLAIN_STAT 0
>> IFX_EXTEND_ROLE 0
>> SECURITY_LOCALCONNECTION
>> UNSECURE_ONSTAT
>> ADMIN_USER_MODE_WITH_DBSA
>> ADMIN_MODE_USERS
>> SSL_KEYSTORE_LABEL
>> PLCY_POOLSIZE 127
>> PLCY_HASHSIZE 31
>> USRC_POOLSIZE 127
>> USRC_HASHSIZE 31>> STAGEBLOB
>> OPCACHEMAX 0
>> ENCRYPT_HDR
>> ENCRYPT_SMX
>> ENCRYPT_CDR 0
>> ENCRYPT_CIPHERS
>> ENCRYPT_MAC
>> ENCRYPT_MACFILE
>> ENCRYPT_SWITCH
>> CDR_EVALTHREADS 1,2
>> CDR_DSLOCKWAIT 5
>> CDR_QUEUEMEM 4096
>> CDR_NIFCOMPRESS 0
>> CDR_SERIAL 0>> CD
Hi Art,
sry must put this straight.
6 mio recs at 10 per page (which the OP don't have, it is only 8 per page)
- if pages are 2 KB - is 600,000 pages and he need:
1) extent size more like 1,000,000 than 16 and NEXT SIZE like 500,000
2) a bufferpool in the 500,000 range plus LRUs ~ 50 plus CLEANERS
and so on and so forth
This table will be > 1 GB and the ONCONFIG setup is a lab setup used
to demo the stores demo DB.
Now back to the OP:
you should either disable the index and reenable after load, and if
this is not possible, then load into a staging table and use fragmentation
for this table.
I think you have a reason to start the VIO table. Do you want a
filtering load to clean the data?
When you done with staging, set PDQPRIORITY and merge the rows into
your table.
In either case, you cannot expect rotational media to be fast enough
when you use near to zilch buffers. You already notices that you
are more or less waitiung for I/O to finish (I/O bound, WIO in sar)
all the time.
Disks are slow (~12 ms / write) and Solid State Disks, which makes
this faster will cost the price of you machine times 1,500
(www.tms.com). In your case SSDs are *not* the way to go.
What you also must do is to reconfigure the BT Cleaner so, that it kicks
in only after the load is finished. Now yout threshold is at 5,000
-> make this 100,000 and watch, if it kicks in. If so, double to
200,000 and so on.
dic_k
Art Kagel schrieb:
> That calculation of the number of pages being added to the table was
> bothering me, and with good reason. The 6millions rows of 240 bytes each
> don't take up 3000 pages, they take up a bit over 71,000 pages! All the
> more reason to max out the buffer cache!
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any entity
> with which I am affiliated nor those of the entities themselves.
>
> On Tue, Jul 7, 2009 at 1:42 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
>> I think that you have two problems:
>>
>>
>> 1. Asking the engine to do the index IO at the same time as the table
>> data IO all to the different sections of the same single spindle is just
>> impractical.
>> 2. You only have 1000 buffers configures so the engine is constantly
>> having to write those pages to disk and wait for the IOs to complete.
>>
>> Suggestions? Obviously I think you need additional spindles, but that
>> aside, add more buffers, many more buffers. You have 6 million rows to load
>> at 240 bytes each so the data will require almost 3000 pages plus the
>> indexes so if you make the cache 4000 pages you may get the entire load into
>> memory at the same time. Honestly though, even 4000 cache pages is small,
>> that's not much of a live working set. The machine has 2GB of memory with
>> over 50MB free. IDS is currently using only 38MB of memory. If you
>> increase the buffer cache from 1000 pages to 10000 pages you will add only
>> 20MB to the server's memory footprint and all of your operations will be
>> faster and smoother.
>>
>> Another thing, are you using COOKED (filesystem) chunks or RAW? If
>> filesystem chunks then you should enable DIRECT_IO (which is currently
>> disabled in the ONCONFIG file) so that the engine can use KAIO threads for
>> IO instead of AIO VPs. KAIO is faster and more efficient in system
>> resources used. Also, if filesystem chunks what kind of filesystem? EXT4
>> is VERY BAD for database chunks and EXT3 isn't much better. If you want to
>> maximize your IO performance you should put the chunks in RAW space, or use
>> a COOKED (block) device rather than a filesystem, or at least put them in a
>> non-journaled filesystem like EXT2.
>>
>> FYI: Linus Torvalds says that EXT4 (and EXT3 with write-back enabled) is
>> not safe, database performance issues aside!
>>
>> Art
>>
>> Art S. Kagel
>> Oninit (www.oninit.com)
>> IIUG Board of Directors (art@iiug.org)
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on my employer, Oninit, the IIUG, nor any other
>> organization with which I am associated either explicitly or implicitly.
>> Neither do those opinions reflect those of other individuals affiliated
>> with any entity with which I am affiliated nor those of the entities
>> themselves.
>>
>>
>>
>> On Tue, Jul 7, 2009 at 1:06 PM, MIKE DUNHAM-WILKIE
> <mike@barrodale.com>wrote:
>>> I am running into performance problems inserting into an indexed table,
>>> using
>>> Informix 11.50. The following timings for loading into
>>> compressed/uncompressed, indexed/non-indexed tables using
>>> dbload/HPL-Deluxe
>>> more illustrate the problem. The number of rows inserted is 6 million.
>>>
>>> compressed/indexed/DBLoad or HPL/h:mm:ss.d
>>> Y/Y/DBLoad/3:47:39.9
>>> Y/N/DBLoad/0:10:58.1
>>> N/Y/DBLoad/3:31:13.4
>>> N/N/DBLoad/0:22:46.6
>>> Y/Y/HPL/3:26:17.6
>>> Y/N/HPL/0:08:31.7
>>> N/Y/HPL/3:28:16.1
>>> N/N/HPL/0:18:45.7
>>>
>>> Why are the inserts into the indexed tables so much slower? I would expect
>>> some performance hit of course, but not this much. I want the indexes left
>>> intact during the load, hence my use of HPL in Deluxe mode rather than
>>> Express
>>> mode.
>>>
>>> I'm using pretty much "out of the box" configuration parameters:
>>>
>>> ------------------------------------------------------------------------
>>> onstat -c:>>>
>>> IBM Informix Dynamic Server Version 11.50.UC4 -- On-Line -- Up 4 days
>>> 17:59:
>>> 49 -- 38212 Kbytes>>> Configuration File:
>>> /opt/informix/informix.11.50_UC4/etc/onconfig.bcslinuxprod
>>> ROOTNAME rootdbs
>>> ROOTOFFSET 0>>> MIRROR 0
>>> MIRRORPATH /opt/IBM/informix/tmp/demo_on.root_mirror
>>> MIRROROFFSET 0
>>> PHYSFILE 200000
>>> PLOG_OVERFLOW_PATH /opt/IBM/informix/tmp
>>> PHYSBUFF 128
>>> LOGFILES 49
>>> LOGSIZE 10000
>>> DYNAMIC_LOGS 2
>>> LOGBUFF 64
>>> LTXHWM 70
>>> LTXEHWM 80
>>> CONSOLE /work1/stat/bcslinuxprod_console.log
>>> TBLTBLFIRST 0
>>> TBLTBLNEXT 0
>>> TBLSPACE_STATS 1
>>> DBSPACETEMP temp1db
>>> SBSPACETEMP temp1sb
>>> SBSPACENAME temp1sb
>>> SYSSBSPACENAME temp1sbifmx
>>> ONDBSPACEDOWN 2
>>> NETTYPE ipcshm,1,50,CPU
>>> LISTEN_TIMEOUT 60
>>> MAX_INCOMPLETE_CONNECTIONS 1024
>>> FASTPOLL 1
>>> MULTIPROCESSOR 0
>>> VP_MEMORY_CACHE_KB 0
>>> SINGLE_CPU_VP 0
>>> CLEANERS 8>>> AUTO_AIOVPS 1
>>> DIRECT_IO 0
>>> LOCKS 20000
>>> DEF_TABLE_LOCKMODE page
>>> RESIDENT 0
>>> SHMBASE 0x44000000L
>>> SHMVIRTSIZE 32656
>>> SHMADD 8192
>>> EXTSHMADD 8192
>>> SHMTOTAL 0
>>> SHMVIRT_ALLOCSEG 0,3
>>> SHMNOACCESS
>>> CKPTINTVL 300>>> AUTO_CKPTS 1
>>> RTO_SERVER_RESTART 0
>>> BLOCKTIMEOUT 3600
>>> TXTIMEOUT 300
>>> DEADLOCK_TIMEOUT 60
>>> HETERO_COMMIT 0
>>> TAPEBLK 32>>
You are right. That's twice my nice MS Calculator got the wrong answer to
the same problem. Probably wasn't 71000 pages but 710000 pages... cheching
with a REAL calculator... ya 712,872 pages. <sigh>
Creating the table with a large extent size or adjusting hte next size would
certainly help. The OP doesn't have the memory for 500,000 buffers. He
has about 50MB free memory available so that's 25,000 buffers roughly. Lots
of CLEANERS wouldn't help, here's why:
- Only a single chunk so chunk writes will only use one cleaner anyway
- If the OP increases the number of LRUs it might help LRU writes, but
with only one session writing there's no contention for LRU queues so even
though the OP has AUTO_LRU_TUNING enabled the engine will not increase the
number of LRUS beyond the 8 that are configured and so 8 CLEANERS are
sufficient.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Tue, Jul 7, 2009 at 6:44 PM, Richard Kofler <richard.kofler@chello.at>wrote:
> Hi Art,
>
> sry must put this straight.
> 6 mio recs at 10 per page (which the OP don't have, it is only 8 per page)
> - if pages are 2 KB - is 600,000 pages and he need:
> 1) extent size more like 1,000,000 than 16 and NEXT SIZE like 500,000
> 2) a bufferpool in the 500,000 range plus LRUs ~ 50 plus CLEANERS
>
> and so on and so forth
>
> This table will be > 1 GB and the ONCONFIG setup is a lab setup used
> to demo the stores demo DB.
>
> Now back to the OP:
> you should either disable the index and reenable after load, and if
> this is not possible, then load into a staging table and use fragmentation
> for this table.
> I think you have a reason to start the VIO table. Do you want a
> filtering load to clean the data?
> When you done with staging, set PDQPRIORITY and merge the rows into
> your table.
>
> In either case, you cannot expect rotational media to be fast enough
> when you use near to zilch buffers. You already notices that you
> are more or less waitiung for I/O to finish (I/O bound, WIO in sar)
> all the time.
> Disks are slow (~12 ms / write) and Solid State Disks, which makes
> this faster will cost the price of you machine times 1,500
> (www.tms.com). In your case SSDs are *not* the way to go.
>
> What you also must do is to reconfigure the BT Cleaner so, that it kicks
> in only after the load is finished. Now yout threshold is at 5,000
> -> make this 100,000 and watch, if it kicks in. If so, double to
> 200,000 and so on.
>
> dic_k
>
> Art Kagel schrieb:
> > That calculation of the number of pages being added to the table was
> > bothering me, and with good reason. The 6millions rows of 240 bytes each
> > don't take up 3000 pages, they take up a bit over 71,000 pages! All the
> > more reason to max out the buffer cache!
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> > with which I am associated either explicitly or implicitly. Neither do
> > those opinions reflect those of other individuals affiliated with any
> entity
> > with which I am affiliated nor those of the entities themselves.
> >
> > On Tue, Jul 7, 2009 at 1:42 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> >> I think that you have two problems:
> >>
> >>
> >> 1. Asking the engine to do the index IO at the same time as the table
> >> data IO all to the different sections of the same single spindle is just
> >> impractical.
> >> 2. You only have 1000 buffers configures so the engine is constantly
> >> having to write those pages to disk and wait for the IOs to complete.
> >>
> >> Suggestions? Obviously I think you need additional spindles, but that
> >> aside, add more buffers, many more buffers. You have 6 million rows to
> load
> >> at 240 bytes each so the data will require almost 3000 pages plus the
> >> indexes so if you make the cache 4000 pages you may get the entire load
> into
> >> memory at the same time. Honestly though, even 4000 cache pages is
> small,
> >> that's not much of a live working set. The machine has 2GB of memory
> with
> >> over 50MB free. IDS is currently using only 38MB of memory. If you
> >> increase the buffer cache from 1000 pages to 10000 pages you will add
> only
> >> 20MB to the server's memory footprint and all of your operations will be
> >> faster and smoother.
> >>
> >> Another thing, are you using COOKED (filesystem) chunks or RAW? If
> >> filesystem chunks then you should enable DIRECT_IO (which is currently
> >> disabled in the ONCONFIG file) so that the engine can use KAIO threads
> for
> >> IO instead of AIO VPs. KAIO is faster and more efficient in system
> >> resources used. Also, if filesystem chunks what kind of filesystem? EXT4
> >> is VERY BAD for database chunks and EXT3 isn't much better. If you want
> to
> >> maximize your IO performance you should put the chunks in RAW space, or
> use
> >> a COOKED (block) device rather than a filesystem, or at least put them
> in a
> >> non-journaled filesystem like EXT2.
> >>
> >> FYI: Linus Torvalds says that EXT4 (and EXT3 with write-back enabled) is
> >> not safe, database performance issues aside!
> >>
> >> Art
> >>
> >> Art S. Kagel
> >> Oninit (www.oninit.com)
> >> IIUG Board of Directors (art@iiug.org)
> >>
> >> Disclaimer: Please keep in mind that my own opinions are my own opinions
> >> and do not reflect on my employer, Oninit, the IIUG, nor any other
> >> organization with which I am associated either explicitly or implicitly.
> >> Neither do those opinions reflect those of other individuals affiliated
> >> with any entity with which I am affiliated nor those of the entities
> >> themselves.
> >>
> >>
> >>
> >> On Tue, Jul 7, 2009 at 1:06 PM, MIKE DUNHAM-WILKIE
> > <mike@barrodale.com>wrote:
> >>> I am running into performance problems inserting into an indexed table,
> >>> using
> >>> Informix 11.50. The following timings for loading into
> >>> compressed/uncompressed, indexed/non-indexed tables using
> >>> dbload/HPL-Deluxe
> >>> more illustrate the problem. The number of rows inserted is 6 million.
> >>>
> >>> compressed/indexed/DBLoad or HPL/h:mm:ss.d
> >>> Y/Y/DBLoad/3:47:39.9
> >>> Y/N/DBLoad/0:10:58.1
> >>> N/Y/DBLoad/3:31:13.4
> >>> N/N/DBLoad/0:22:46.6
> >>> Y/Y/HPL/3:26:17.6
> >>> Y/N/HPL/0:08:31.7
> >>> N/Y/HPL/3:28:16.1
> >>> N/N/HPL/0:18:45.7
> >>>
> >>> Why are the inserts into the indexed tables so much slower? I would
> expect
> >>> some performance hit of course, but not this much. I want the indexes
> left
> >>> intact du
Thank you all for the very helpful suggestions. I have made the recommending changes and want to report on the results. First, though, I'll explain why I can't load into an unindexed table and then create the index at the end. What I am doing is simulating a continuous load into a table, loading measurements from a continuously operating instrument. Specifically what I do is a 3-phase process: load some number N records into an uncompressed table, then compute compression statistics and compress the table (using 'execute function sysadmin:task("table compress repack shrink",...)'), then continue to load records (forever!) into the compressed table. For this experiment I picked N = 1000000 (probably higher than necessary), did my compression, then loaded another 1000000 records. (This is a smaller experiment than the 25,000,000 row load I reported in my original email, but it still illustrates the problem). Initial setup. FILLFACTOR 90, BUFFERS 1000, small initial and next extents. The timings for the three phases were 11+14+15 minutes. At the end my table had just 4 extents, but each of the three indexes had around 115 extents! Setup 1. Preallocate large initial extent, no other changes The timings went down to 9+13+12. Indexes are now 1 extent each Setup 2. Change FILLFACTOR to 50. Timings changed to 4+15+12 Setup 3. Change BUFFERS to 10000. Timings were reduced to 5+6+6 Setup 4. Change BUFFERS to 50000. Timings were reduced to 1+3+1 Setup 5. Change BUFFERS to 100000. Timings were reduced to .5+2+1 I also tried setting DIRECT_IO to 1, but that seemed to throw the compression phase into some sort of loop. I need to investigate that further. Looking at the log file during Setup 5 I spotted the following messages: ------------------------------------------------------------------------------- 16:04:06 Logical Log 1818 Complete, timestamp: 0x857243ec. 16:04:09 Performance Advisory: Logical log file size might be too small for a checkpoint to complete. 16:04:09 Results: The size of individual logical log files is too small for the current workload, resulting in each log file filling very quickly. If log files fill in less than 30 seconds, the checkpoint might remain blocked because the last log file fills during the time needed to perform the checkpoint. 16:04:09 Action: Increase the size of the individual logical log files so that it takes at least 30 seconds to fill each one. Look at the online log to determine how quickly the log files are filling, and then increase the size of the files proportionately. 16:04:10 Logical Log 1819 Complete, timestamp: 0x85742388. 16:04:11 Performance Advisory: Based on the current workload, the physical log might be too small to accommodate the time it takes to flush the buffer pool. 16:04:11 Results: The server might block transactions during checkpoints. 16:04:11 Action: If transactions are blocked during the checkpoint, increase the size of the physical log to at least 154490 KB. 16:04:11 Performance Advisory: The physical log is too small for automatic checkpoints. 16:04:11 Results: Automatic checkpoints are disabled. 16:04:11 Action: To enable automatic checkpoints, increase the physical log to at least 154490 KB. 16:04:13 Logical Log 1820 Complete, timestamp: 0x857593b9. 16:04:17 Logical Log 1821 Complete, timestamp: 0x85774be2. 16:04:19 Performance Advisory: The physical log is running out of room during checkpoint processing. 16:04:19 Results: Transactions are being blocked until the checkpoint is complete. 16:04:19 Action: Increase the physical log size. 16:04:20 Checkpoint Completed: duration was 3 seconds. 16:04:20 Thu Jul 9 - loguniq 1822, logpos 0x6d415c, timestamp: 0x8578f312 Interval: 12907 ---------------------------------------------------------------------------- I have logging turned off during the loading phases (1 and 3) but need it turned on for the compression phase (2). I will address the two advisories above and continue my testing. Thanks again for the advice,... Mike
MIKE DUNHAM-WILKIE schrieb: > Thank you all for the very helpful suggestions. I have made the recommending > changes and want to report on the results. First, though, I'll explain why I > can't load into an unindexed table and then create the index at the end. What > I am doing is simulating a continuous load into a table, loading measurements > from a continuously operating instrument. Specifically what I do is a 3-phase > process: load some number N records into an uncompressed table, then compute > compression statistics and compress the table (using 'execute function > sysadmin:task("table compress repack shrink",...)'), then continue to load > records (forever!) into the compressed table. For this experiment I picked N = > 1000000 (probably higher than necessary), did my compression, then loaded > another 1000000 records. (This is a smaller experiment than the 25,000,000 row > load I reported in my original email, but it still illustrates the problem). > > Initial setup. FILLFACTOR 90, BUFFERS 1000, small initial and next extents. > The timings for the three phases were 11+14+15 minutes. At the end my table > had just 4 extents, but each of the three indexes had around 115 extents! > > Setup 1. Preallocate large initial extent, no other changes > The timings went down to 9+13+12. Indexes are now 1 extent each > > Setup 2. Change FILLFACTOR to 50. Timings changed to 4+15+12 > > Setup 3. Change BUFFERS to 10000. Timings were reduced to 5+6+6 > > Setup 4. Change BUFFERS to 50000. Timings were reduced to 1+3+1 > > Setup 5. Change BUFFERS to 100000. Timings were reduced to .5+2+1 > > I also tried setting DIRECT_IO to 1, but that seemed to throw the compression > phase into some sort of loop. I need to investigate that further. > > Looking at the log file during Setup 5 I spotted the following messages: > > > ------------------------------------------------------------------------------- > 16:04:06 Logical Log 1818 Complete, timestamp: 0x857243ec. > 16:04:09 Performance Advisory: Logical log file size might be too small for a > > checkpoint to complete. > 16:04:09 Results: The size of individual logical log files is too small for > > the current workload, resulting in each log file filling very > > quickly. If log files fill in less than 30 seconds, the checkpoint > > might remain blocked because the last log file fills during the time > > needed to perform the checkpoint. > 16:04:09 Action: Increase the size of the individual logical log files so > > that it takes at least 30 seconds to fill each one. Look at the > > online log to determine how quickly the log files are filling, and > > then increase the size of the files proportionately. > 16:04:10 Logical Log 1819 Complete, timestamp: 0x85742388. > 16:04:11 Performance Advisory: Based on the current workload, the physical log > might be too small to > accommodate the time it takes to flush the buffer pool. > 16:04:11 Results: The server might block transactions during checkpoints. > 16:04:11 Action: If transactions are blocked during the checkpoint, increase > the size of the > physical log to at least 154490 KB. > 16:04:11 Performance Advisory: The physical log is too small for automatic > checkpoints. > 16:04:11 Results: Automatic checkpoints are disabled. > 16:04:11 Action: To enable automatic checkpoints, increase the physical log to > at least 154490 KB. > 16:04:13 Logical Log 1820 Complete, timestamp: 0x857593b9. > 16:04:17 Logical Log 1821 Complete, timestamp: 0x85774be2. > 16:04:19 Performance Advisory: The physical log is running out of room during > checkpoint processing. > 16:04:19 Results: Transactions are being blocked until the checkpoint is > complete. > 16:04:19 Action: Increase the physical log size. > 16:04:20 Checkpoint Completed: duration was 3 seconds. > 16:04:20 Thu Jul 9 - loguniq 1822, logpos 0x6d415c, timestamp: 0x8578f312 > Interval: 12907 > ---------------------------------------------------------------------------- > > I have logging turned off during the loading phases (1 and 3) but need it > turned on for the compression phase (2). > > I will address the two advisories above and continue my testing. > > Thanks again for the advice,... > Mike > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hi Mike, when you want to create a dictionary with using a first batch of rows, I guess you want to use this dictionary to insert shortened rows with batch #2 ff. Then I do not see a reason for FILLFACTOR < 100. FILLFACTOR only leaves some room in index pages, if not at 100. But it is valid for *all* indexes of *all* databases in the IDS instance. I consider it not a good idea to have indexes active during load anyway. You must also carefully think about BTSCANNER and the I/O you may create, if not configured in an optimal way. Loading with active index(es) always does a lot of index page splits. Depending if you have VARCHAR datatype, and because you are on IDS 11.50, you also might look in the AdminRef docs pg 1-68: MAX_FILL_DATA_PAGES Configuration Parameter. This can increase the number of rows fitting into 1 page. It depends on the value of maximum length defined in column having datatype VARCHAR minus avg(length(column of datatype VARCHAR)) Maybe you should also consider if it might be an advantage to use a bigger pagesize than default and therefore also use another bufferpool instead of the default bufferpool for this type of processing. Else every load is a good candidate to flood your buffers with data not used in OLTP, but decreaing the cache rate of OLTP heavily. As to the warnings you got in your online.log during load: I already complained about this. Load is a non-standard operation in a way, that it is normal to switch llog pieces very fast. This is the reason, why best way to load is w/o logging This you can archive by staging: Bulk load into a RAW table, then ALTER TABLE stagy_01 to TYPE ( standard ) and you will reduce write I/O to half and not flood you llog. Of course YMMV and there are many reasons to *not* switch of logging during load. This is why I complained about the advisor, which really should not throw such messages when a load opeartion is causing the fast llog switches. Maybe the clever advisor software could bring some color into a grey DBA day using a message like: 'I see you are loading a buch of data and I see that you switch your llog every 2 secs during this load. Please don't do this while replicationg llogs to a gazillion SDS servers and make sure, that you logical log backup can cope with 6 GByte / sec.' Or some such ;) But then, my opinion about self healing and friends is very well documented .... dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe