Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Insert slow after large delete

    Hi,

    There have been regular large deletes over the past few months on a big table that is 84GB ( 888, 879,462 rows).. we are using db2 v8.2. I noticed that a program that was inserting records after reading data from a file has been doing so at a pretty slow pace now. As far as I can tell, the select speed isn't as badly affected. Am I correct in assuming that a REORG of the index on the table will fix the problem? If so, I tried to run the REORG command on our backup database just to try it out and got back an error that said transaction log is full.

    Here is command that I gave --

    db2 reorg indexes all for table <tablename> allow write access cleanup only

    Here is what I have as a part of the config for the log files..

    db2 get db config for dbname | grep -i logfilsiz
    Log file size (4KB) (LOGFILSIZ) = 8000

    db2 get db config for dbname | grep -i PRIMARY
    Number of primary log files (LOGPRIMARY) = 10

    db2 get db config for dbname | grep -i SECONDARY
    Number of secondary log files (LOGSECOND) = 5


    Can someone please help.. is there any other solution I can try out? We will eventually be splitting the huge table up based on certain ranges but until that is done I need to figure out a solution that will atleast put the insert speed back to what it was ( if not faster).

    Thanks in advance..

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Have you run REORGCHK command on this table?
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Hi, no I didn't run that command but will try that out...thanks..

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Hi, I ran the REORGCHK command on the table on our backup server and apparently, it's still chugging along even though the query has been running for atleast 12 hours now.. of course, our backup db isn't as fast as production but honestly, I didn't want to mess with our production server first. I will let it run through the night since I don't see any crazy behavior on the db since running the command anyway. Have you ever seen REORGCHK take a long time to execute..? I really hope I get the results soon so I know whether to just REORG the index or if I need to REORG the table as well...

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Is there room in your mainitenance window for an 'regular' offline reorg? The thought of online reorgs on such a big table gives mee goose-pickels
    You have the right mindset when you want to split-up such a table.... but when are you planning to upgrade to a version of db2 which is still supported? I would do that 1st before splitting the table. Your hands are tied while on v8.2. Can be done though, using an UNION ALL view.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    It collects statistics on the table by default.
    If you know that you have up to date statistics you can run REORGCHK with CURRENT STATISTICS option.
    To collect the statistics on huge table I would suggest you to run RUNSTATS with TABLESAMPLE SYSTEM (N) option, where N could be 1, for example. Large is better of course for quality but will run longer.

    Another question is: what will you do if it says that you should reorg this huge table?
    Probably if you don't have enough space then offline reorg will run for ages. You can run online reorg but it will run even longer.

    Probably the best way in your case is to alter this table with APPEND ON option and measure the insert performance...
    Regards,
    Mark.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    Hi, thanks so much for the replies! Finally, after 18 hours of running the query it gave me the results of the REORGCHK command. I have attached a screenshot of the output.

    Also, I have attached screenshots of what I get back from running df and top on our production db...as you can tell we have quite a bit of space. And another screenshot is that of the insert logs of a program that we have running daily.. it reads records from a file and inserts rows.. seems to be taking 11 seconds to insert each row.

    I don't think reorg offline is an option since we have customers logging in all the time and we can't really afford downtime especially if it could take days. Any advice you can give me will be greatly appreciated...thanks for the help!!
    Attached Thumbnails Attached Thumbnails REORGCHK_Output.png   INSERT_COMMAND_LOG.png   PROD_DB_TOP_COMMAND.png   PROD_DB_DISK_USAGE.png  

  8. #8
    Join Date
    Nov 2007
    Posts
    265
    Sorry not sure if the images are even visible so here are the results ---

    REORGCHK results ---

    Code:
    Doing RUNSTATS ....
    
    
    Table statistics:
    
    F1: 100 * OVERFLOW / CARD < 5
    F2: 100 * (Effective Space Utilization of Data Pages) > 70
    F3: 100 * (Required Pages / Total Pages) > 80
    
    SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG
    ----------------------------------------------------------------------------------------
    Table: DB2INST1.READINGS
    DB2INST1      READINGS           8.9e+08     0 5e+06 5e+06      - 4.36e+10   0  97  99 ---
    ----------------------------------------------------------------------------------------
    
    Index statistics:
    
    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
    F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
    
    SCHEMA   NAME                 CARD  LEAF ELEAF  LVLS ISIZE  NDEL   KEYS  F4  F5  F6  F7  F8 REORG
    -------------------------------------------------------------------------------------------------
    Table: DB2INST1.READINGS
    SYSIBM   SQL040428191501470  9e+08 5e+06  9644     4    16 2e+05  9e+08  43  51   2   0   0 *----
    -------------------------------------------------------------------------------------------------
    
    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
    for indexes that are not in the same sequence as the base table. When multiple
    indexes are defined on a table, one or more indexes may be flagged as needing
    REORG.  Specify the most important index for REORG sequencing.
    
    Tables defined using the ORGANIZE BY clause and the corresponding dimension
    indexes have a '*' suffix to their names. The cardinality of a dimension index
    is equal to the Active blocks statistic of the table.

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    Insert row log ---

    Code:
    [2013-Aug-29 16:42:22] row stored successfully.
    [2013-Aug-29 16:42:33] row stored successfully.
    [2013-Aug-29 16:42:43] row stored successfully.
    [2013-Aug-29 16:42:54] row stored successfully.
    [2013-Aug-29 16:43:05] row stored successfully.
    [2013-Aug-29 16:43:16] row stored successfully.
    [2013-Aug-29 16:43:27] row stored successfully.
    [2013-Aug-29 16:43:38] row stored successfully.
    [2013-Aug-29 16:43:49] row stored successfully.
    [2013-Aug-29 16:43:59] row stored successfully.
    [2013-Aug-29 16:44:10] row stored successfully.
    [2013-Aug-29 16:44:21] row stored successfully.
    [2013-Aug-29 16:44:32] row stored successfully.
    [2013-Aug-29 16:44:41] row stored successfully.
    [2013-Aug-29 16:44:52] row stored successfully.
    [2013-Aug-29 16:45:03] row stored successfully.
    [2013-Aug-29 16:45:14] row stored successfully.
    [2013-Aug-29 16:45:25] row stored successfully.
    [2013-Aug-29 16:45:35] row stored successfully.
    [2013-Aug-29 16:45:46] row stored successfully.
    [2013-Aug-29 16:45:57] row stored successfully.
    [2013-Aug-29 16:46:07] row stored successfully.
    [2013-Aug-29 16:46:18] row stored successfully.
    [2013-Aug-29 16:46:28] row stored successfully.

    Prod db disk usage :

    Code:
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/sda2              34G  8.3G   24G  27% /
    /dev/mapper/DBVolume-data
                          2.5T  147G  2.2T   7% /DATA
    tmpfs                  16G     0   16G   0% /dev/shm
    Prod db top command :
    Code:
    top - 16:48:09 up 544 days, 13:58,  1 user,  load average: 0.01, 0.02, 0.00
    Tasks: 370 total,   1 running, 369 sleeping,   0 stopped,   0 zombie
    Cpu(s):  0.2%us,  0.2%sy,  0.0%ni, 99.6%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:  32959324k total, 32764604k used,   194720k free,   136168k buffers
    Swap: 31249400k total,  2437020k used, 28812380k free, 31308760k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                    
     4912 db2inst1      15   0 17.8g  48m  37m S  0.3  0.2   3:42.99 db2sysc                                                                                     
    32687 root      15   0 12896 1332  828 R  0.3  0.0   0:00.04 top                                                                                         
        1 root      15   0 10372  688  580 S  0.0  0.0   0:45.96 init                                                                                        
        2 root      RT  -5     0    0    0 S  0.0  0.0   2:33.35 migration/0                                                                                 
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0                                                                                 
        4 root      RT  -5     0    0    0 S  0.0  0.0   2:03.60 migration/1                                                                                 
        5 root      34  19     0    0    0 S  0.0  0.0   0:00.08 ksoftirqd/1                                                                                 
        6 root      RT  -5     0    0    0 S  0.0  0.0   2:02.20 migration/2                                                                                 
        7 root      34  19     0    0    0 S  0.0  0.0   0:00.04 ksoftirqd/2                                                                                 
        8 root      RT  -5     0    0    0 S  0.0  0.0   1:59.00 migration/3                                                                                 
        9 root      34  19     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/3

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By the looks of statistics, neither the table nor the index need reorganization. Is this the only index on the table?

    You'll need to look at the snapshots during the insertion to determine where the bottleneck is. The system seems almost idle, so the problem may be in the inserting application itself.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By the way, this looks quite strange:
    Code:
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 
     4912 db2inst1      15   0 17.8g  48m  37m S  0.3  0.2   3:42.99 db2sysc
    DB2 has 17 GB of virtual memory allocated, but only 48 MB of it is resident, I would expect the number to be much higher.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    By the looks of statistics, neither the table nor the index need reorganization. Is this the only index on the table?

    You'll need to look at the snapshots during the insertion to determine where the bottleneck is. The system seems almost idle, so the problem may be in the inserting application itself.
    Thanks for the quick response! Yes, that is the only index on the table...that is interesting, I thought a bunch of deletes would have indicated a reorg necessary on the table. There have been quite a few updates on the records over the years too. Of course, I don't want to touch the table if it's not necessary.

    I checked back in the logs of the python program that insert records from the file... back in April it was taking around 7 seconds to insert a row and now in August it takes about 11 seconds. I will look into the app to see if it is actually causing this...

  13. #13
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    By the way, this looks quite strange:
    Code:
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 
     4912 db2inst1      15   0 17.8g  48m  37m S  0.3  0.2   3:42.99 db2sysc
    DB2 has 17 GB of virtual memory allocated, but only 48 MB of it is resident, I would expect the number to be much higher.
    Oh, the rows keep fluctuating when I run top.. it must have been from the time that I took it and the rows that were captured... here's an example of what i get now..
    Code:
     6666 root      15   0 12896 1328  828 R  0.3  0.0   0:03.96 top                                                                              
     8379 db2inst1      15   0 17.7g 4156 2256 S  0.3  0.0  55:18.87 db2sysc                                                                          
     8402 db2inst1      16   0 17.7g 7.2g 7.2g S  0.3 22.9   9:39.21 db2sysc                                                                          
    20915 db2inst1      15   0 17.8g  68m  62m S  0.3  0.2   0:23.05 db2sysc                                                                          
        1 root      15   0 10372  688  580 S  0.0  0.0   0:45.97 init                                                                             
        2 root      RT  -5     0    0    0 S  0.0  0.0   2:33.38 migration/0                                                                      
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0                                                                      
        4 root      RT  -5     0    0    0 S  0.0  0.0   2:03.61 migration/1                                                                      
        5 root      34  19     0    0    0 S  0.0  0.0   0:00.09 ksoftirqd/1

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I see. You seem to be running multiple logical partitions on a single server, which is not the best configuration. Also, the partitions appear to be unevenly utilized, which hints at the possible problems with your choice of distribution keys. Is the table into which you're inserting partitioned, and if so, do inserts happen to be directed mostly to a single partition?
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    I see. You seem to be running multiple logical partitions on a single server, which is not the best configuration. Also, the partitions appear to be unevenly utilized, which hints at the possible problems with your choice of distribution keys. Is the table into which you're inserting partitioned, and if so, do inserts happen to be directed mostly to a single partition?
    Thanks for getting back to me.. the table was created a long time ago by someone that used to work with me and this is the info on the groups, bufferpools, etc specific to this readings table only----


    Code:
    CREATE DATABASE PARTITION GROUP "DB2INST1_READING_GROUP" ON DBPARTITIONNUMS
                    (0);
    
    
    CREATE BUFFERPOOL "DB2INST1_READING_BP"  DATABASE PARTITION GROUP "DB2INST1_READING_GROUP"  SIZE 640000 PAGESIZE 8192 NOT EXTENDED STORAGE;
    
    
    CREATE REGULAR TABLESPACE READING_TBLSP IN DATABASE PARTITION GROUP DB2INST1_READING_GROUP PAGESIZE 8192 MANAGED BY SYSTEM
             USING ('/DATA/db2inst1_reading/db2inst1_reading_sp1',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp2',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp3',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp4',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp5',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp6',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp7',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp8',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp9',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp10',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp11',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp12',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp13',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp14',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp15',
                    '/DATA/db2inst1_reading/db2inst1_reading_sp16')
             EXTENTSIZE 32
             PREFETCHSIZE 192
             BUFFERPOOL DB2INST1_READING_BP
             OVERHEAD 24.100000
             TRANSFERRATE 0.900000
             FILE SYSTEM CACHING
             DROPPED TABLE RECOVERY ON;
    
    
    
     CREATE TABLE READINGS"  (
                      val1 INTEGER NOT NULL ,
                      val2 TIMESTAMP NOT NULL ,
                      -------
                      -------
                      ------
     )
                     DATA CAPTURE CHANGES
                     PARTITIONING KEY (val1)  USING HASHING
                       IN "READING_TBLSP" ;
    
    
    Lastly, there's one primary key ( combination of a few columns) and one index ( combination of a few columns). Inserts are not directed anywhere in particular.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •