Thread: Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5
Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5
From
luo roger
Date:
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