Názor k článku
Úvaha ohledně zneužívání LIKE v databázích
od LENIN POWER! - INFO: vacuuming "alexa.resultcache"
INFO: scanned index "resultcache1"...
INFO: vacuuming "alexa.resultcache"
INFO: scanned index "resultcache1" to remove 91 row versions
DETAIL: CPU 3.66s/6.46u sec elapsed 131.09 sec.
INFO: "resultcache": removed 91 row versions in 7 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.12 sec.
INFO: index "resultcache1" now contains 143751986 row versions in 497612 pages
DETAIL: 91 index row versions were removed.
74 index pages have been deleted, 74 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "resultcache": found 91 removable, 143751986 nonremovable row versions in 1647055 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 7503 unused item pointers.
73 pages contain useful free space.
0 pages are entirely empty.
CPU 14.72s/11.47u sec elapsed 361.19 sec.
na 8.3 migrovat nebudeme, k nicemu by nam to nebylo. db roste asi o 1 GB denne. Mame autovacuum + 1x denne vacuum. REINDEX ani VACUUM FULL delat nemuzeme, to by trvalo desitky hodin.
Ten /postmaster.pid to je taky opruz, mame tu napsano v poznamkach ze kdyz server spadne tak pgsql kvuli nemu nenastartuje. musi se rucne smazat. Boze to je spolehlivost. Ta pitoma databaze neumi ani spolehlive zjistit zda bezi?
pokud hroz´ı pˇreteˇcen´ı ˇc´ıtaˇce transakc´ı (varov´an´ı v logu) VACUUM FREEZE (1x za nˇekolik let). Neda se to zjistit nejakym selectem? ja nemam zajem se hrabat ve /var/log/messages - zaukoloval jsem admina.
Kdyby ta aplikace nebyla zbastlena v pythonu, nemela 6mb zdrojaku a nepouzivala modul import pg ktery je db neprenositelny tak uz by davno sel pg i se svym vacuum do haje. delat mvcc pres disk je pitomost.
Ne ze by se ten pgsql nejak vyrazne predrel pokud jde o odmazavani dat z velkych tablic. Pravda je tam DELETE CASCADE v relaci tak 1:80 ale stejne je to pomaly jak prase.
crawler=# DELETE FROM alexa.querycache where cached < '2009-03-17';
DELETE 67155
Time: 228892.404 ms
ted mne bezi DELETE + aktivni autovacuum cisti tabulky a dulezita query od klientu co bezi normalne tak 0.3sec normalne ted jede tak 7 sekund. To je radosti na starym belidle s db co nemaj workload manager a neumi poustet dulezite dotazy prioritne. Doufam ze neprijdou stiznosti.
Kdyz bezi autovacuum tak je to tedy znatelne pomalejsi:
crawler=# DELETE FROM alexa.querycache where cached < '2009-03-19';
DELETE 147471
Time: 1331750.621 ms
To vacuum je fakt za trest. Nerikal jsem vam ze je dulezite umet garantovat worst case? Takhle to moc nejde, napere se tam jedno automaticke vacuum a vykon jde do kytek.
taky by mne zajimalo proc se to vacuum chova takto, delam to na jedne tabulce a porad to cisteni restartuje. Proc tu tabulku a index musel cistit 3x? To to nezvladl v jednom pruchodu? To ale generuje strasnyho i/o.
crawler=# VACUUM verbose alexa.resultcache ;
INFO: vacuuming "alexa.resultcache"
INFO: scanned index "resultcache1" to remove 5592194 row versions
DETAIL: CPU 4.69s/18.03u sec elapsed 227.78 sec.
INFO: "resultcache": removed 5592194 row versions in 67423 pages
DETAIL: CPU 2.13s/0.45u sec elapsed 141.74 sec.
INFO: scanned index "resultcache1" to remove 5592205 row versions
DETAIL: CPU 4.69s/17.31u sec elapsed 170.26 sec.
INFO: "resultcache": removed 5592205 row versions in 63695 pages
DETAIL: CPU 1.85s/0.50u sec elapsed 80.27 sec.
INFO: scanned index "resultcache1" to remove 5005436 row versions
DETAIL: CPU 4.39s/32.36u sec elapsed 225.78 sec.
INFO: "resultcache": removed 5005436 row versions in 57313 pages
DETAIL: CPU 1.70s/0.46u sec elapsed 91.58 sec.
INFO: index "resultcache1" now contains 118633367 row versions in 498388 pages
DETAIL: 16189835 index row versions were removed.
87911 index pages have been deleted, 31703 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "resultcache": found 16189835 removable, 118633367 nonremovable row versions in 1649104 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 9117033 unused item pointers.
289416 pages contain useful free space.
0 pages are entirely empty.
CPU 31.78s/74.51u sec elapsed 1226.20 sec.
Teda zajimalo by mne ciste teoreticky zda firebird s tim vacuum taky takhle blbne. jo recovery.conf to je od autoru pgsql opravdu kanadsky zertik. Pripadam si jak roce 1987.
A to se tu bavime o pididatabazi 30GB. Co budeme delat az db naroste na 200GB? To uz abychom to zacali prepisovat pro ten Oraakl. Tady vidite ze pgsql ma pri nasazeni v realne netrivialni aplikaci dost vazne problemy. A to nechavam stranou jeho narocnou administraci na lidskou praci.
Vy si porad myslite ze se nam to vyplati? Kdyby jsme tam meli neco z trojky db2, mssql, oracle tak tyhle db zadne z vyse uvedenych problemu (vacuum normal/full/freeze migrace verzi) nemaji. Administrace pomoci klikacich nastroju, zalohovani do TSM bez problemu, rollforward recovery se dela 1 - slovy jednim prikazem. Ten pgsql ma strasne velky TCO.