Hlavní navigace

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"...

  • Článek je starý, nové názory již nelze přidávat.
  • 28. 4. 2009 2:42

    LENIN POWER! (neregistrovaný)
    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.

    autovacuum | on
    autovacuum_analyze_scale_factor | 0.1
    autovacuum_analyze_threshold | 250
    autovacuum_freeze_max_age | 200000000
    autovacuum_naptime | 60
    autovacuum_vacuum_cost_delay | -1
    autovacuum_vacuum_cost_limit | -1
    autovacuum_vacuum_scale_factor | 0.2
    autovacuum_vacuum_threshold | 500

    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.