On 01/22/2015 06:06 AM, Andrey Lizenko wrote:
Hello all,
I have a problem with growing WAL-files populating a temporary table.
After running the following script 8192 times (each in separate
connection) I can see 3*16 MB WAL files.
3 * 16 = 48MB
Say each row takes 10 bytes(an underestimate).
8000 rows * 8192 connections * 10 bytes = 655,360,000 bytes or 655.36
megabytes.
CREATE TEMP TABLE IF NOT EXISTS positiontemporarytable
(pos_instrument_id integer, pos_code varchar(40));
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
prepare pos_insert (integer, varchar(40)) as insert into
"positiontemporarytable" values($1, $2);
execute pos_insert ('76','27958');
execute pos_insert ('71','9406:58');
......
<about 8000 same 'executes' commands>
......
execute pos_insert ('74','19406:58');
COMMIT;
It was tested via pgbench:
pgbench -l -t 8192 -C -f /db/postgres/report_test.sql db_test
and simple bash script:
for i in {1..8192}
do
echo $i
psql -f /db/postgres/report_test.sql db_test
done
Results are the same. Server version 9.3.5 (it seems 9.2.4 and 9.2.9
also affected by this).
As metioned, for example, in Robert Haas blog
http://rhaas.blogspot.ru/2010/05/global-temporary-and-unlogged-tables.html
3. They are not WAL-logged.
Whats wrong with it in my case?
Nothing as far as I can see.
--
Regards, Andrey Lizenko
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
