After an upgrade to a server I sometimes help out with, the perl script responsible for importing about 300 000 records into a Postgres database went from taking 2.5 to 6 hours.
Not overly fond of reworking things that aren't broken, I decided not to tweak the scripts to use COPY instead of the usual massive, bulky number of INSERTs... There had to be some bottle-neck somewhere...
Ultimately, checking out the warning logs pointed me to setting the fsync setting in postgres to "off" - which made things tons better. Sure things won't work nicely if there's an OS or hardware freak-out, but if that happens, the state of the database will pretty much be the least of my problems.
Time taken to import those records now: 1.5 hours. I'm pretty convinced I'm missing something else subtle out here, but such a performance gain for so little effort is worth a few words here.
http://www.postgresql.org/docs/8.1/interactive/runtime-config-wal.html provides some more info.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment