Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I generated a dump file of 21 MB or so:

pg_dump --format=tar --verbose --file=database.backup mydatabase

When I import this file on windows doing:

pg_restore --dbname mydatabase --verbose database.backup

It takes 1 hour to be done.

Doing the same on Ubuntu 10.10 64bits boxes, takes about 7 hours!

Of course I'm talking about the same hardware specs (Dell Studio XPS). Same RAM, CPU, etc.

In both cases I'm using out of box configs for PostgreSQL 8.4.7.

Perhaps the distros configuration is different... Perhaps some optimization that just windows distro is doing?


Extra info: On Windows 7 -> NTFS. On Ubuntu 10.10 -> ext4


When I do

pg_dump --format=tar --verbose --file=workspace/work/dumps/loaded.backup mydb

I takes only 5 seconds! If I restore on an empty new db doing:

pg_restore --dbname mydb-2 --verbose workspace/work/dumps/loaded.backup

I takes only 10 seconds. (Problem solved?... almost) It seems the db guys exported the original dump using different options. Perhaps --inserts option?

The big difference between Windows and Ubuntu using the original dump is still bothering my mind. Any thoughts on this?

share|improve this question

migrated from stackoverflow.com Apr 16 '11 at 23:29

This question came from our site for professional and enthusiast programmers.

1  
Probably a better question for "those admin/server guys" ;-) –  pst Apr 13 '11 at 17:23
    
It'd be a good thing if you filed a bug report in Ubuntu's Launchpad, to get the right people to look at it. –  Simon Richter Apr 13 '11 at 17:23
    
dumb file, hehe ;) –  sacklpicka Apr 13 '11 at 17:23
1  
@IErbaer: I fixed that, insulting your database backup tools is never a good idea. –  mu is too short Apr 13 '11 at 17:59
1  
Are you sure it is 21MB, and not 21GB? If it is 21GB then a difference can be explained if Windows does have hard drive write cache enabled and Linux does not. You can try start Postgres with -F (disable fsync) for duration of restore. If it really is 21MB then all I can say is that something is very, very wrong. –  Tometzky Apr 14 '11 at 8:47

3 Answers 3

Even one hour is very long for a small dump file of 21 MB. We are restoring databases of 2 GB compressed dump file in about 30 minutes but we might have better hardware ;-)

What you should read first:

http://www.postgresql.org/docs/8.4/static/populate.html

It is all about your problem. It tells you how to poulate a database fast.

Aditional tips:

  • First enable logging of all statements with duration time and see what's going on
  • Increase shared_buffers, default on ubuntu 10.10 is only 24 MB, see http://www.postgresql.org/docs/8.4/static/kernel-resources.html#SYSVIPC for configuring your linux system to accept higher values
  • use --format=custom or -Fc for dumping. It's the best choice
  • you can run pg_restore on multiple CPU with "-j" but i guess you have other problems that getting the last bits of performance

For further information:

share|improve this answer

Edit: I missed that you said the dump is merely 21MB, and not even compressed. Even 1 hour is a very long time to restore that amount of data. Could you shed some light on what the dump contains? What sort of table structure, how many indexes and what kind? Functional indexes? GiST/GIN indexes? How much data is generated after the dump is restored?

The PostgreSQL mailing list might be a better place to discuss this.

Old post

The default PostgreSQL configuration is very conservative in terms of resource requirements. Which means that during bulk-loading, it has to perform very frequent checkpoints (your Postgres logs are probably full of checkpoint warnings).

I suspect that PostgreSQL on Windows might not correctly flush everything to disk, thus checkpoints don't affect performance much. If true, this is of course bad for database integrity.

If my assumptions are true, bumping checkpoint_segments up to 50 in the Ubuntu configuration should make it perform similarly to Windows. (There are lots of other tunables, but this is the most important one for bulk-loading)

Also, what does SHOW wal_sync_method say on your Ubuntu installation? It should be fdatasync for optimal performance, but some versions defaulted to open_datasync.

share|improve this answer
    
That still doesn't explain 7 hours to restore a 21 MB dump. –  Peter Eisentraut Apr 14 '11 at 4:44
    
Oh sorry, I somehow missed the fact that it's 21 MB. Even 1 hour is way too long for that -- I'd expect 1 minute max. It would be interesting to hear what the dump contains? –  intgr Apr 14 '11 at 8:33
    
Ok, I will detail some structures. (On original question). –  berserkpi Apr 14 '11 at 15:40

Try to turn autovacuum off in your postgresql.conf.

If that does not help, try defragmenting you disc...

Also, I wonder what was the filesystem in both cases?

share|improve this answer
    
On Windows7 -> NTFS. On Ubuntu 10.10 -> ext4 –  berserkpi Apr 14 '11 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.