Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5
| От | luo roger |
|---|---|
| Тема | Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5 |
| Дата | |
| Msg-id | 9dbf5dd0903170859x5d4219d5q51056a73c690a08@mail.gmail.com обсуждение исходный текст |
| Список | 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 по дате отправления:
Предыдущее
От: "Jignesh K. Shah"Дата:
Сообщение: Re: Proposal of tunable fix for scalability of 8.4