Virtualizace PostgreSQL

[MP]

Zdravim,
ma nekdo zkusenosti s virtualizaci PostgreSQL a jeho vykonosti? Mam tu rozjety Xen 4.1 s paravirtualizovanem VM s PostgreSQL 9.3 (10GB+ db) a mam problem s vykonem proti samotnemu zelezu. Testovano na SELECT dotazech v pgbenchi i samotnych SELECT dotazech ve webove aplikaci. Pokud za 100% dam vykon cachovaneho dotazu na zeleze, tak na samotnem Xen kernelu je dotaz pomalejsi ca o 20-100%, zatimco VM bezici na Xenu je proti Xenu pomalejsi (ci dokonce rychlejsi) o cca 10%. Zpracovani merene jednodussi stranky se 160 dotazy (v psql) pak dopada i takto:

zelezo: 0.15s
xen kernel: 0.2s
VM: 0.21s

Na trojitem join dotazu je pak vysledek klidne takovyto:
zelezo: 2.5s
xen kernel: 9s
VM: 9s

Chystam se zmerit Xen HVM a otestovat KVM, Proxmox, ale kazdopadne, ma nekdo zkusenosti s vykonosti PostgreSQL na ruznych virtualizacich ?

[Reklama]

[Jakub L.]

Hodilo by se sem hodit prováděcí plány z obou variant...

EXPLAIN ANALYZE SELECT ...

[Jan Forman]

Proxmox (OpenVZ), výkonnostní rozdíl neměřitelný téměř za všech okolností...

Jinak full virtualizace různé - obvykle až 50% ztráty v IO něco málo v CPU.
Pokaždé se to chová jinak. Raději v ostrém provozu nepoužívám.

[MP]

Tak dodavam cerstve testy.

Konfigurace:
kernel 3.14.7, Xen 4.3, PostgreSQL 9.3.4
NOXEN,XEN kernel: 2x Xeon X5650  @ 2.67GHz, 96GB RAM, RAID10
PVM: 4xjadro,8GB RAM  hypervizor, 20x jadro, 64GB RAM, LVM nad RAID10

Poradi testu (db jsou shodne, import do cerstve vytvoreneho clusteru,default config):
1]restart stroje
2]5x explain analyze A, posledni hodnota
3]5x explain analyze B, posledni hodnota

Kód: [Vybrat]

EXPLAIN ANALYZE SELECT * FROM web LIMIT 1;

Kód: [Vybrat]

NOXEN
 Limit  (cost=0.00..0.34 rows=1 width=2022) (actual time=0.003..0.003 rows=1 loops=1)
   ->  Seq Scan on web  (cost=0.00..10.30 rows=30 width=2022) (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.020 ms


XEN KERNEL
 Limit  (cost=0.00..0.34 rows=1 width=2022) (actual time=0.006..0.008 rows=1 loops=1)
   ->  Seq Scan on web  (cost=0.00..10.30 rows=30 width=2022) (actual time=0.003..0.003 rows=1 loops=1)
 Total runtime: 0.030 ms


XEN PVM
 Limit  (cost=0.00..0.34 rows=1 width=2022) (actual time=0.006..0.007 rows=1 loops=1)
   ->  Seq Scan on web  (cost=0.00..10.30 rows=30 width=2022) (actual time=0.003..0.003 rows=1 loops=1)
 Total runtime: 0.030 ms

Kód: [Vybrat]

EXPLAIN ANALYZE SELECT date_from, date_to FROM (SELECT eshop.cart_items2properties.value AS date_from, eshop.cart_items.iditem AS id_from, eshop.cart_items.idproductprice AS idproductprice FROM eshop.cart_items2properties INNER JOIN eshop.cart_items ON eshop.cart_items.iditem = eshop.cart_items2properties.iditem WHERE eshop.cart_items.idcont = 430 AND eshop.cart_items2properties.idproperty = 10) AS table_from INNER JOIN (SELECT eshop.cart_items2properties.value AS date_to, eshop.cart_items.iditem AS id_to FROM eshop.cart_items2properties INNER JOIN eshop.cart_items ON eshop.cart_items.iditem = eshop.cart_items2properties.iditem WHERE eshop.cart_items.idcont = 430 AND eshop.cart_items2properties.idproperty = 11) AS table_to ON id_from = id_to INNER JOIN eshop.products2prices ON eshop.products2prices.idprice = idproductprice INNER JOIN eshop.products ON eshop.products.idproduct = eshop.products2prices.idproduct WHERE idcategory = 12;

Kód: [Vybrat]

NOXEN
 Nested Loop  (cost=1.58..26.02 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=1)
   ->  Nested Loop  (cost=1.44..25.82 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1)
         ->  Nested Loop  (cost=1.16..17.52 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1)
               ->  Nested Loop  (cost=0.87..17.10 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.58..16.63 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=1)
                           ->  Index Scan using cart_items_idcont_idx on cart_items cart_items_1  (cost=0.29..8.31 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                 Index Cond: (idcont = 430)
                           ->  Index Scan using cart_items2properties_pkey on cart_items2properties  (cost=0.29..8.31 rows=1 width=16) (never executed)
                                 Index Cond: ((iditem = cart_items_1.iditem) AND (idproperty = 10))
                     ->  Index Scan using cart_items2properties_pkey on cart_items2properties cart_items2properties_1  (cost=0.29..0.46 rows=1 width=16) (never executed)
                           Index Cond: ((iditem = cart_items2properties.iditem) AND (idproperty = 11))
               ->  Index Scan using cart_items_pkey on cart_items  (cost=0.29..0.41 rows=1 width=8) (never executed)
                     Index Cond: (iditem = cart_items2properties.iditem)
                     Filter: (idcont = 430)
         ->  Index Scan using prices_pkey on products2prices  (cost=0.28..8.29 rows=1 width=8) (never executed)
               Index Cond: (idprice = cart_items.idproductprice)
   ->  Index Scan using products_pkey on products  (cost=0.14..0.18 rows=1 width=4) (never executed)
         Index Cond: (idproduct = products2prices.idproduct)
         Filter: (idcategory = 12)
 Total runtime: 0.083 ms


XEN KERNEL
 Nested Loop  (cost=1.58..26.02 rows=1 width=24) (actual time=0.018..0.018 rows=0 loops=1)
   ->  Nested Loop  (cost=1.44..25.82 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=1)
         ->  Nested Loop  (cost=1.16..17.52 rows=1 width=28) (actual time=0.013..0.013 rows=0 loops=1)
               ->  Nested Loop  (cost=0.87..17.10 rows=1 width=36) (actual time=0.011..0.011 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.58..16.63 rows=1 width=20) (actual time=0.009..0.009 rows=0 loops=1)
                           ->  Index Scan using cart_items_idcont_idx on cart_items cart_items_1  (cost=0.29..8.31 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                 Index Cond: (idcont = 430)
                           ->  Index Scan using cart_items2properties_pkey on cart_items2properties  (cost=0.29..8.31 rows=1 width=16) (never executed)
                                 Index Cond: ((iditem = cart_items_1.iditem) AND (idproperty = 10))
                     ->  Index Scan using cart_items2properties_pkey on cart_items2properties cart_items2properties_1  (cost=0.29..0.46 rows=1 width=16) (never executed)
                           Index Cond: ((iditem = cart_items2properties.iditem) AND (idproperty = 11))
               ->  Index Scan using cart_items_pkey on cart_items  (cost=0.29..0.41 rows=1 width=8) (never executed)
                     Index Cond: (iditem = cart_items2properties.iditem)
                     Filter: (idcont = 430)
         ->  Index Scan using prices_pkey on products2prices  (cost=0.28..8.29 rows=1 width=8) (never executed)
               Index Cond: (idprice = cart_items.idproductprice)
   ->  Index Scan using products_pkey on products  (cost=0.14..0.18 rows=1 width=4) (never executed)
         Index Cond: (idproduct = products2prices.idproduct)
         Filter: (idcategory = 12)
 Total runtime: 0.235 ms


XEN PVM
 Nested Loop  (cost=1.58..26.02 rows=1 width=24) (actual time=0.018..0.018 rows=0 loops=1)
   ->  Nested Loop  (cost=1.44..25.83 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=1)
         ->  Nested Loop  (cost=1.16..17.52 rows=1 width=28) (actual time=0.013..0.013 rows=0 loops=1)
               ->  Nested Loop  (cost=0.87..17.10 rows=1 width=36) (actual time=0.011..0.011 rows=0 loops=1)
                     ->  Nested Loop  (cost=0.58..16.63 rows=1 width=20) (actual time=0.009..0.009 rows=0 loops=1)
                           ->  Index Scan using cart_items_idcont_idx on cart_items cart_items_1  (cost=0.29..8.31 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                 Index Cond: (idcont = 430)
                           ->  Index Scan using cart_items2properties_pkey on cart_items2properties  (cost=0.29..8.31 rows=1 width=16) (never executed)
                                 Index Cond: ((iditem = cart_items_1.iditem) AND (idproperty = 10))
                     ->  Index Scan using cart_items2properties_pkey on cart_items2properties cart_items2properties_1  (cost=0.29..0.46 rows=1 width=16) (never executed)
                           Index Cond: ((iditem = cart_items2properties.iditem) AND (idproperty = 11))
               ->  Index Scan using cart_items_pkey on cart_items  (cost=0.29..0.41 rows=1 width=8) (never executed)
                     Index Cond: (iditem = cart_items2properties.iditem)
                     Filter: (idcont = 430)
         ->  Index Scan using prices_pkey on products2prices  (cost=0.28..8.29 rows=1 width=8) (never executed)
               Index Cond: (idprice = cart_items.idproductprice)
   ->  Index Scan using products_pkey on products  (cost=0.14..0.18 rows=1 width=4) (never executed)
         Index Cond: (idproduct = products2prices.idproduct)
         Filter: (idcategory = 12)
 Total runtime: 0.098 ms


Kód: [Vybrat]

EXPLAIN ANALYSE SELECT COUNT(*) FROM crm.accounts a JOIN crm.cards c USING(idcont) JOIN crm.bills b USING(idcard) JOIN crm.transactions t USING(idbill);

Kód: [Vybrat]

NOXEN
 Aggregate  (cost=157590.52..157590.53 rows=1 width=0) (actual time=2656.890..2656.890 rows=1 loops=1)
   ->  Hash Join  (cost=80824.65..155511.34 rows=831672 width=0) (actual time=1175.764..2520.553 rows=1963768 loops=1)
         Hash Cond: (t.idbill = b.idbill)
         ->  Seq Scan on transactions t  (cost=0.00..39398.98 rows=2038798 width=4) (actual time=0.036..365.622 rows=2038798 loops=1)
         ->  Hash  (cost=77270.09..77270.09 rows=216605 width=4) (actual time=1175.529..1175.529 rows=504930 loops=1)
               Buckets: 4096  Batches: 32 (originally 16)  Memory Usage: 1025kB
               ->  Hash Join  (cost=54616.11..77270.09 rows=216605 width=4) (actual time=645.200..1075.406 rows=504930 loops=1)
                     Hash Cond: (b.idcard = c.idcard)
                     ->  Seq Scan on bills b  (cost=0.00..12491.95 rows=530995 width=8) (actual time=0.013..104.175 rows=530995 loops=1)
                     ->  Hash  (cost=49614.39..49614.39 rows=304858 width=4) (actual time=645.119..645.119 rows=305364 loops=1)
                           Buckets: 4096  Batches: 16  Memory Usage: 680kB
                           ->  Hash Join  (cost=18253.42..49614.39 rows=304858 width=4) (actual time=239.971..585.989 rows=305364 loops=1)
                                 Hash Cond: (c.idcont = a.idcont)
                                 ->  Seq Scan on cards c  (cost=0.00..16272.01 rows=737501 width=8) (actual time=0.015..119.988 rows=737501 loops=1)
                                 ->  Hash  (cost=11243.63..11243.63 rows=427263 width=4) (actual time=226.637..226.637 rows=427263 loops=1)
                                       Buckets: 4096  Batches: 32  Memory Usage: 480kB
                                       ->  Seq Scan on accounts a  (cost=0.00..11243.63 rows=427263 width=4) (actual time=0.011..107.226 rows=427263 loops=1)
 Total runtime: 2656.949 ms


XEN KERNEL
 Aggregate  (cost=157590.52..157590.53 rows=1 width=0) (actual time=9046.954..9046.955 rows=1 loops=1)
   ->  Hash Join  (cost=80824.65..155511.34 rows=831672 width=0) (actual time=3699.613..7960.917 rows=1963768 loops=1)
         Hash Cond: (t.idbill = b.idbill)
         ->  Seq Scan on transactions t  (cost=0.00..39398.98 rows=2038798 width=4) (actual time=0.070..1341.906 rows=2038798 loops=1)
         ->  Hash  (cost=77270.09..77270.09 rows=216605 width=4) (actual time=3699.273..3699.273 rows=504930 loops=1)
               Buckets: 4096  Batches: 32 (originally 16)  Memory Usage: 1025kB
               ->  Hash Join  (cost=54616.11..77270.09 rows=216605 width=4) (actual time=2180.896..3351.673 rows=504930 loops=1)
                     Hash Cond: (b.idcard = c.idcard)
                     ->  Seq Scan on bills b  (cost=0.00..12491.95 rows=530995 width=8) (actual time=0.023..356.997 rows=530995 loops=1)
                     ->  Hash  (cost=49614.39..49614.39 rows=304858 width=4) (actual time=2180.776..2180.776 rows=305364 loops=1)
                           Buckets: 4096  Batches: 16  Memory Usage: 680kB
                           ->  Hash Join  (cost=18253.42..49614.39 rows=304858 width=4) (actual time=744.920..1964.467 rows=305364 loops=1)
                                 Hash Cond: (c.idcont = a.idcont)
                                 ->  Seq Scan on cards c  (cost=0.00..16272.01 rows=737501 width=8) (actual time=0.021..510.422 rows=737501 loops=1)
                                 ->  Hash  (cost=11243.63..11243.63 rows=427263 width=4) (actual time=672.426..672.426 rows=427263 loops=1)
                                       Buckets: 4096  Batches: 32  Memory Usage: 480kB
                                       ->  Seq Scan on accounts a  (cost=0.00..11243.63 rows=427263 width=4) (actual time=0.020..333.121 rows=427263 loops=1)
 Total runtime: 9047.029 ms


XEN PVM
 Aggregate  (cost=157965.64..157965.65 rows=1 width=0) (actual time=9090.061..9090.062 rows=1 loops=1)
   ->  Hash Join  (cost=81037.02..155856.31 rows=843732 width=0) (actual time=3698.381..8004.511 rows=1963768 loops=1)
         Hash Cond: (t.idbill = b.idbill)
         ->  Seq Scan on transactions t  (cost=0.00..39398.98 rows=2038798 width=4) (actual time=0.076..1384.699 rows=2038798 loops=1)
         ->  Hash  (cost=77431.19..77431.19 rows=219746 width=4) (actual time=3698.015..3698.015 rows=504930 loops=1)
               Buckets: 4096  Batches: 32 (originally 16)  Memory Usage: 1025kB
               ->  Hash Join  (cost=54728.81..77431.19 rows=219746 width=4) (actual time=2154.487..3346.828 rows=504930 loops=1)
                     Hash Cond: (b.idcard = c.idcard)
                     ->  Seq Scan on bills b  (cost=0.00..12491.95 rows=530995 width=8) (actual time=0.028..371.698 rows=530995 loops=1)
                     ->  Hash  (cost=49656.92..49656.92 rows=309111 width=4) (actual time=2154.360..2154.360 rows=305364 loops=1)
                           Buckets: 4096  Batches: 16  Memory Usage: 680kB
                           ->  Hash Join  (cost=18253.42..49656.92 rows=309111 width=4) (actual time=712.692..1944.499 rows=305364 loops=1)
                                 Hash Cond: (c.idcont = a.idcont)
                                 ->  Seq Scan on cards c  (cost=0.00..16272.01 rows=737501 width=8) (actual time=0.022..532.262 rows=737501 loops=1)
                                 ->  Hash  (cost=11243.63..11243.63 rows=427263 width=4) (actual time=640.137..640.137 rows=427263 loops=1)
                                       Buckets: 4096  Batches: 32  Memory Usage: 480kB
                                       ->  Seq Scan on accounts a  (cost=0.00..11243.63 rows=427263 width=4) (actual time=0.020..322.998 rows=427263 loops=1)
 Total runtime: 9090.140 ms


[-]

disky pre virtual su na LVM alebo v subore? Ako je nastavena cache, aky je filesystem na fyzickom storage? Hovoris o clusteri, mas na storage GFS2 alebo podobny multizurnalovy FS? Ake je MTU na sietovke v takom pripade? Problem nie je vPgSQL, takmer naisto je problem v IO vykonnosti storage.

[Reklama]

[Jakub L.]

Co z těch prováděcích plánů zvládám vykoukat:

Je jasné, že se věci provedou úplně stejně na všech těch mašinách. (stejný exekuční plán, stejné costs) a všechno to protahují sekvenční skeny...

Co bych zkusil:

• Zkusil bych udělat benchmark disků/úložišť
• na virtuálu upravit costy IO

[MP]

Jak upravit costy I/O ?

To storage je lokalne primo na serveru, je to 8x300GB SAS (6gbps P410i) v raid10:

NOXEN,XEN: diskovy oddil primo nad R10, scheduler cfq
PVM: diskovy oddil nad LVM, scheduler cfq

[MP]

Jak upravit costy I/O ?

To storage je lokalne primo na serveru, je to 8x300GB SAS (6gbps P410i) v raid10:

NOXEN,XEN: diskovy oddil primo nad R10 (/dev/sdaX), scheduler cfq
PVM: diskovy oddil nad LVM (/dev/lvm/vm), scheduler cfq

FS je ve vsech pripadech ext4. Jedna se o samotny stroj s jedinou psql instanci.MTU apod neni meneno.
Me se zda hlavne divne, ze uz jen vymena klasickeho kernelu za xen kernel znamena, ze je tak velky propad. ocekaval jsem tak 10%, ono na googlu toho ohledne porovnavacich perf testu neni. Nekde jim to jede ok, nekde upozornuji na polovicni IO v XENu, ale proc a jak to poresit pripadne, nic.

[Jakub L.]

pokud jde o IO costs, myslím tohle: http://www.postgresql.org/docs/9.2/static/runtime-config-query.html

Víc mě nenapadá. Doporučil bych se poptat na IRC: #postgresql on irc.freenode.net - tam umí často poskytnout dost dobré rady...

[Iwik]

Máš správne zarovnane particie v tej virtualke? http://www.thomas-krenn.com/en/wiki/Partition_Alignment