BLOB updates -> database size explodes

Поиск
Список
Период
Сортировка
От Dimitar Misev
Тема BLOB updates -> database size explodes
Дата
Msg-id 51A52762.4080909@gmail.com
обсуждение исходный текст
Ответы Re: BLOB updates -> database size explodes  (Stephen Scheck <singularsyntax@gmail.com>)
Список pgsql-general
I'm having some issue with BLOB updates (via ECPG). The total blobs size
should be ~280MB, but after partially updating all of them for 150 times
the size on disk grows up from 184MB to 18GB.

In more details:

There are 608 blobs of size 460800 bytes. All blobs are updated
piecewise in 150 repetitions; so first all blobs are updated in bytes 0
- 3071, then 3072 - 6143, etc. In the end on the disk the database is 18GB.

Computing the size of pg_largeobject gives me 267MB:

    SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
      pg_size_pretty
    ----------------
      267 MB

On the other hand, this gives me 18GB, and du -sh on the disk also
reports 18.4GB:

    SELECT tablename,
            pg_size_pretty(size) AS size_pretty,
            pg_size_pretty(total_size) AS total_size_pretty
    FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
                     pg_total_relation_size(schemaname||'.'||tablename)
    AS total_size
           FROM pg_tables) AS TABLES
    WHERE TABLES.tablename = 'pg_largeobject'
    ORDER BY total_size DESC;
        tablename    | size_pretty | total_size_pretty
    ----------------+-------------+-------------------
      pg_largeobject | 18 GB       | 18 GB


Doing these updates takes 85 minutes on quad-core i7 with 6GB RAM and
SSD hard disk. This is PostgreSQL 8.4.12 on Debian 64 bit.

Anyone knows what's going on here?


В списке pgsql-general по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: Strange behavior of "=" as assignment operator
Следующее
От: YuChi
Дата:
Сообщение: Re: Help, How to start the server??