Skip site navigation (1) Skip section navigation (2)

Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5

From: luo roger <roger(dot)maillist(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5
Date: 2009-03-17 15:59:52
Message-ID: 9dbf5dd0903170859x5d4219d5q51056a73c690a08@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Test hardware:
MB:GA-EP43-DS3L, CPU :intel 7400 RAM:2G HD:320G sata

debian5, freebsd7.1 are installed by default choice by cd-rom
debian use XFS, Freebsd use UFS+softdateup
all software installed by app_get or pkg_add, change NOTHING configure.
at begening of every testing, use initdb create new data, don't edit
default postgressql.conf

this is table struct:
\d user_account;
                      Table "public.user_account"
  Column   |         Type          |             Modifiers
-----------+-----------------------+-----------------------------------
 passport  | character varying(32) | not null default ''::character(1)
 spassword | character(40)         | not null default ''::character(1)
 guid      | integer               | not null default 0
 ptype     | integer               | default 0
Indexes:
    "user_account_pk_guid" PRIMARY KEY, btree (guid)
    "user_account_key_passport" UNIQUE, btree (passport)

use sql file  to insert data, one insert sql include 1000 row.
ua1m.sql include 1000 insert sql.
so, there are all 1,000,000 row .

usage : time psql node < ua1m.sql

===============================================================================
ONE test:
create table an primary key and unique, insert date.

debian : 3 min
freebsd: 10 min

debian win. faster thrice times then freebsd.

===============================================================================
TWO test:
step 1: create table without key and unique, after insert date,
step 2: ALTER TABLE....ADD pk and unique

          insert      alter table
debian :   45s           26s
freebsd:   18s           10s

freebsd win.

================================================================================

whe ONE test:
I use iostat to observer IO.
freebsd : at beginning, io is 20-30M , after 30s, io go down slowly,
about 5M-7M
debian always keep one level.

freedb# iostat 2
      tty             ad1             cpu
tin tout  KB/t tps  MB/s  us ni sy in id
   0   22 11.88   8  0.09   0  0  0  0 100
   0   22  0.00   0  0.00   0  0  0  0 100
   0   22  0.00   0  0.00   0  0  0  0 100
   0   22  0.00   0  0.00   0  0  0  0 100
   0   75 42.62  52  2.16   4  0  1  0 95
   0  388 47.42 491 22.75  30  0  4  0 66
   0  388 46.60 502 22.85  32  0  3  0 65
   0  321 50.01 588 28.69  26  0  4  0 69
   0  298 45.77 592 26.44  26  0  2  0 71
   0  328 43.86 536 22.96  27  0  3  1 69
   0  269 45.72 659 29.42  25  0  2  1 72
   0  239 35.69 661 23.05  21  0  2  1 76
   0  216 26.91 535 14.06  19  0  2  0 80
   0  343 42.71 520 21.69  34  0  3  0 62
   0   29 33.25 343 11.13   1  0  2  0 97
   0   96 27.97 412 11.26   8  0  2  0 90
   0  319 27.48 429 11.52  25  0  3  0 71
   0  126 38.58 357 13.45  10  0  2  0 87
   0   81 34.50 302 10.19   6  0  2  0 91
   0   81 36.62 288 10.32   5  0  2  0 92
   0   66 30.89 285  8.60   4  0  2  0 94
   0   44 43.47 382 16.23   4  0  2  0 94
   0   21 32.31 297  9.37   0  0  1  0 99
   0   36 27.29 385 10.26   2  0  1  0 97
   0   21 22.12 350  7.56   0  0  1  0 99
   0   66 26.02 477 12.11   5  0  2  0 92
   0   51 36.14 292 10.32   3  0  1  0 96
   0   29 31.48 278  8.55   1  0  1  0 98
   0   44 35.26 284  9.80   3  0  2  0 95
   0   51 34.27 295  9.87   3  0  1  0 96
   0   44 30.35 238  7.06   3  0  2  0 96
   0   44 28.12 290  7.98   2  0  2  0 96
   0   44 30.64 271  8.11   3  0  1  0 96
   0   51 32.52 288  9.16   3  0  2  0 96
   0   44 33.29 265  8.61   2  0  1  0 96



debian1:~# iostat -t 2 sda
Linux 2.6.26-1-686 (debian1)    03/16/2009      _i686_

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               2.66         2.17       246.58     329693   37377968
sda               0.50         0.00         8.00          0         16
sda               0.00         0.00         0.00          0          0
sda              10.00         0.00      3117.00          0       6234
sda              49.50         0.00     25833.50          0      51667
sda              33.50         0.00     10091.00          0      20182
sda              51.50         0.00     31448.50          0      62897
sda              29.50         0.00      9900.00          0      19800
sda              51.00         0.00     32721.00          0      65442
sda              52.50         0.00     24387.00          0      48774
sda              69.50         0.00     25178.00          0      50356
sda              70.00         0.00     40984.50          0      81969
sda              28.50         0.00      9674.00          0      19348
sda              63.50         0.00     35200.50          0      70401
sda              64.00         0.00     25347.50          0      50695
sda              72.50         0.00     44291.50          0      88583
sda              33.00         0.00      8569.50          0      17139
sda              32.00         0.00      8192.00          0      16384
sda              52.00         0.00     24642.00          0      49284
sda             366.00         0.00     55748.50          0     111497
sda              91.50         0.00      6927.00          0      13854
sda              42.00         0.00      7020.50          0      14041
sda             117.50         0.00      8630.00          0      17260
sda             101.00         0.00      9893.50          0      19787
sda             104.00         0.00     12161.50          0      24323
sda              52.50         0.00      9542.00          0      19084
sda             133.50         0.00     56748.50          0     113497
sda             514.50         0.00     30546.50          0      61093
sda             233.00         0.00     11944.00          0      23888
sda              43.50         0.00      9901.50          0      19803
sda              54.00         0.00     12983.00          0      25966
sda              87.00         0.00     18138.50          0      36277
sda             144.50         0.00     39053.00          0      78106
sda             409.00         0.00     61243.50          0     122487
sda             191.00         0.00     11877.50          0      23755
sda              87.50         0.00     25915.50          0      51831
sda             120.50         0.00     24951.50          0      49903
sda             200.00         0.00     34608.00          0      69216
sda             172.50         0.00     25180.00          0      50360
sda             397.50         0.00     60361.50          0     120723
sda             298.50         0.00     35150.00          0      70300
sda             130.00         0.00     17058.50          0      34117
sda             145.50         0.00     31988.50          0      63977
sda              33.50         0.00     13424.50          0      26849
sda             106.00         0.00     32461.00          0      64922
sda             195.00         0.00     27111.00          0      54222
sda             212.00         0.00     41948.00          0      83896
sda             297.00         0.00     49826.50          0      99653
sda             393.50         0.00     21554.00          0      43108
sda             199.50         0.00     15239.00          0      30478
sda             249.50         0.00     29903.00          0      59806
sda             214.50         0.00     23047.50          0      46095
sda             253.50         0.00     34445.00          0      68890
sda             326.50         0.00     18723.00          0      37446
sda             274.00         0.00     22240.50          0      44481
sda             164.00         0.00     20395.50          0      40791
sda             203.50         0.00     28445.00          0      56890
sda              76.50         0.00     12331.50          0      24663
sda             274.00         0.00     40263.00          0      80526
sda             456.00         0.00     38636.00          0      77272
sda             311.50         0.00     13403.50          0      26807
sda             126.00         0.00      2441.00          0       4882
sda             244.00         0.00     18099.00          0      36198
sda             248.00         0.00     27507.00          0      55014
sda             264.50         0.00     31205.50          0      62411
sda             330.50         0.00     26000.50          0      52001
sda             322.00         0.00     33353.00          0      66706
sda             185.50         0.00     32089.50          0      64179
sda             144.50         0.00     32589.00          0      65178
sda             368.50         0.00     40860.50          0      81721
sda             429.50         0.00     20813.00          0      41626
sda             165.50         0.00     16998.00          0      33996
sda             383.50         0.00     28479.50          0      56959
sda             204.50         0.00     32974.00          0      65948
sda             316.00         0.00     26019.00          0      52038
sda             366.50         0.00     20155.50          0      40311
sda             273.00         0.00     23505.00          0      47010
sda             260.50         0.00     57853.50          0     115707
sda             418.50         0.00     18528.00          0      37056
sda             329.50         0.00     19092.00          0      38184
sda             165.00         0.00     19736.50          0      39473
sda             296.50         0.00     24410.00          0      48820
sda             247.50         0.00     22831.50          0      45663
sda             320.00         0.00     31209.50          0      62419
sda             330.00         0.00     37919.00          0      75838
sda             315.50         0.00     45214.00          0      90428
sda             442.50         0.00     26841.50          0      53683
sda             316.50         0.00     19826.50          0      39653

pgsql-performance by date

Next:From: Ron MayerDate: 2009-03-17 17:09:36
Subject: Extremely slow intarray index creation and inserts.
Previous:From: Jignesh K. ShahDate: 2009-03-17 13:18:11
Subject: Re: Proposal of tunable fix for scalability of 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group