From: Florian Schröck <fschroeck@aycan.de>
To: pgsql-performance@postgresql.org
Subject: Very slow update statement on 40mio rows
Date: Fri, 15 Feb 2013 15:30:55 +0100
Message-ID: <511E469F.70308@aycan.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi!
I'm new to this mailinglist and I'm new to postgres as well. It is about
our own backup software (java); we switched the DB from MySQL to
postgres and we need some help.
The backup database holds all files from the server in the database. On
my testing platform there are about 40 mio rows and the DB size grew to
about 70 GB (the same DB was about 11 GB with MySQL, but this is another
issue).
Before each backup job, there is a reset to get a consistent state of
all files:
UPDATE BackupFiles SET nTapeNr=0, nAFIOCounter=0, nBlockCounter=0,
cStatus='NEW'::StatusT, bOnSetBlue=false, bOnSetYellow=false,
nLastBackupTS= '0001-01-01 00:00:00' WHERE cStatus='NEW' OR
cStatus='WRITING'OR cStatus='ONTAPE';
Explain analyze: http://explain.depesz.com/s/8y5
The statement takes 60-90 minutes. In MySQL the same statement takes
about 2 minutes on the same HW.
I tried to optimize the settings but until now without success.
Can we optimize this update statement somehow? Do you have any other ideas?
Any help is appreciated! Thank you!
*My current config:*
shared_buffers = 2GB
work_mem = 16MB
wal_buffers = 16MB
checkpoint_segments = 45
random_page_cost = 2.0
effective_cache_size = 6GB
*HW:*
2x Intel E5405 @ 2.00GHz
8 GB RAM
3ware 9650SE-16ML RAID Controller, all caches enabled
DB is on a RAID6 with 14x 1TB (since the DB partition on the RAID1 was
too small)
*SW:*
Debian Squeeze 6.0 with Kernel 3.5.4
Postgres 8.4.13 (standard Debian package)
*Table:*
+---------------+-----------------------------+---------------------------------------------------------------------+
| Column | Type
| Modifiers |
+---------------+-----------------------------+---------------------------------------------------------------------+
| _rowid | bigint | not null default
nextval('backupfiles__rowid_seq'::regclass) |
| cfilename | bytea | not
null |
| nfilesize | bigint | not null default
0::bigint |
| nfilectimets | timestamp without time zone | not null default
'1970-01-01 00:00:00'::timestamp without time zone |
| ntapenr | integer | not null default
0 |
| nafiocounter | bigint | not null default
0::bigint |
| nblockcounter | bigint | not null default
0::bigint |
| cstatus | statust | not null default
'NEW'::statust |
| bonsetblue | boolean | default
false |
| bonsetyellow | boolean | default
false |
| nlastbackupts | timestamp without time zone | not null default
'1970-01-01 00:00:00'::timestamp without time zone |
+---------------+-----------------------------+---------------------------------------------------------------------+
Indexes:
"backupfiles_pkey" PRIMARY KEY, btree (_rowid)
"cfilename_index" btree (cfilename)
"cstatus_index" btree (cstatus)
"nfilectimets_index" btree (nfilectimets)
"ntapenr_index" btree (ntapenr)
*Example row:*
+--------+-----------------------------+-----------+---------------------+---------+--------------+---------------+---------+------------+--------------+---------------------+
| _rowid | cfilename | nfilesize | nfilectimets
| ntapenr | nafiocounter | nblockcounter | cstatus | bonsetblue |
bonsetyellow | nlastbackupts |
+--------+-----------------------------+-----------+---------------------+---------+--------------+---------------+---------+------------+--------------+---------------------+
| 1 | /dicom/log/datadir_init.log | 1790 | 2013-01-30 14:02:48
| 0 | 0 | 0 | NEW | f |
f | 0001-01-01 00:00:00 |
+--------+-----------------------------+-----------+---------------------+---------+--------------+---------------+---------+------------+--------------+---------------------+
--
Mit freundlichen Grüßen
Best regards
Florian Schröck
IT Services
aycan Digitalsysteme GmbH
Innere Aumühlstr. 5
97076 Würzburg . Germany
Tel. +49 (0)9 31. 270 40 88
Fax +49 (0)9 31. 270 40 89
mailto:fschroeck@aycan.de
mailto:store@aycan.de
http://www.aycan.de
Geschäftsführer: Dipl.-Ing. Stephan Popp
Sitz der Gesellschaft: Würzburg
Eingetragen beim Amtsgericht Würzburg unter HRB 6043
Ust-Id Nr. DE 190658226
aycan PACS
Ihre Vorteile: www.aycan.de/pacs-wechsel <http://www.aycan.de/pacs-wechsel;
Was ist ein VNA?: www.aycan.de/vna <http://www.aycan.de/vna;
Attachments:aycan_email_footer_pacs-wechsel.png (image/png, 13.2K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: fschroeck@aycan.de
Subject: Re: Very slow update statement on 40mio rows
In-Reply-To: <511E469F.70308@aycan.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox