1

I'm using PostgreSQL 9.1 on Ubuntu 12.04. The development machine has 2GB RAM and it's a Centrino Duo.

The Backup command is pg_dump and takes less than 5 seconds to run from the terminal.

But my restore is done like this:

/usr/bin/psql --host localhost --port 5432 --username "postgres" --quiet "dbHRS" < "t2"

The text file "t2" has around 5000 lines of inserts. But it takes around 2 minutes to complete.

Why does it take this long? Is there something I can do to make it faster?

ADDITIONAL INFO: The Backup was done like this:

/usr/bin/pg_dump --host localhost --port 5432 --username "postgres" --role "mizk" --no-password  --format plain --data-only --inserts --column-inserts --verbose --file "abc" "dbHRS"

So the file abc contains JUST A SET OF INSERT QUERIES. That's ALL. No stored procs, no triggers...

I cannot accept the slowness to restore. What's strange is that when I copy-paste the text file's contents into a query window on PG-Admin, it's pretty fast. Just a couple of seconds. So I think it's got to do with the way the psql command works. Maybe I'm wrong.

Improve this question
2
  • 1
    Are the tables indexed? You may gain quite a bit of performance if you drop the indexes at the start of the run, then recreate them afterwards. As it is, Postgres may be recreating the index(es) for every insert.
    – Jos
    Apr 22, 2014 at 15:37
  • Hmmm... that's a good one. I never looked at it like that. I do have primary keys on every table (naturally). This may be a contributing factor. Let's check it out. Thanks!
    – itsols
    Apr 22, 2014 at 15:46

2 Answers 2

Reset to default
3

Not knowing exactly what's being done, I can only speak generally:

  • Reads are simply reads from memory or disk.
  • Writes have to check constraints, action triggers and then write to disk. Usually within a transaction that needs to be setup, actioned committed and then cleaned up. And then there's cache invalidation.
  • Most disks (and their filesystems) read much faster than they write.

I'm not surprised a restore takes longer than a backup but if you really think it's erroneously long, benchmark your queries and work out what the problem is.

Improve this answer
5
  • Thank you for your inputs and I'm sorry if I was not clear enough. I've updated my question with more info at the bottom.
    – itsols
    Apr 22, 2014 at 14:58
  • did you make a mistake when you stated that a "backup takes longer than a restore" ?
    – itsols
    Apr 23, 2014 at 0:39
  • It was. Edited.
    – Oli ♦
    Apr 23, 2014 at 1:37
  • I know it's possible to run explain and look at performance of a query. But in my case I'm running a file containing several (single) inserts from the Ubuntu terminal. Is there a way I can measure its performance? Thanks!
    – itsols
    Apr 23, 2014 at 4:03
  • I'm suggesting you look at the time of a single insert compared to the time of a single select. By the sounds of it, it should show you a significant difference.
    – Oli ♦
    Apr 23, 2014 at 7:05
0

As Oli mentioned, writes take more time. A real world example is a database I had to migrate for a customer. It was quite large, and took about 24 hours to run the complete dump.

Importing took about 3 times as long, however. With your test computer, it sounds like a desktop machine, meaning only one hard drive (with no RAID). If you continue to experience issues after benchmarking your queries, you may see what kind of hard drive is in the machine. Faster rotational speeds will be better in this application, so if you have a 5400 RPM drive, adding a drive specifically for SQL is an option. If you have a 7200 RPM drive, it is a little better, but you may still want to upgrade.

Depending on the size of the database, they make some small capacity SSD drives that are not too costly. If you set your SQL to run on that, it should improve performance. Along with that, your current set up most likely has the OS and the database on the same hard drive. Splitting those up should increase performance.

Improve this answer
1
  • We're only talking 5000 records at most. I don't think this is a lot.
    – itsols
    Apr 23, 2014 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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