On Mon, May 8, 2017 at 11:49 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> Hello,
>
> I use two dedicated bare metal servers (Online and Kimsufi). The first one takes much longer to execute a procedure
thatrecreates a database by truncating its tables, then copying the data from a set of text files; it is however much
fasterfor more typical SELECT and INSERT queries done by users.
>
> Here is the timing for the procedure :
>
> #Kimsufi server
> time psql -f myfile.sql mydb
> real 0m12.585s
> user 0m0.200s
> sys 0m0.076s
>
> #Online server
> time psql -f myfile.sql mydb
> real 1m15.410s
> user 0m0.144s
> sys 0m0.028s
>
> My questions:
>
> -Does the difference in 'buffered disk reads' explain the 6 fold increase in execution time for truncate/copy on the
Onlineserver?
The most likely cause of the difference would be that one server IS
honoring fsync requests from the db and the other one isn't.
If you run pgbench on both (something simple like pgbench -c 1 -T 60,
aka one thread for 60 seconds) on a machine running on a 7200RPM hard
drive, you should get approximately 120 transactions per second, or
less, since that's how many times a second a disk spinning at that
speed can write out data. If you get say 110 on the slow machine and
800 on the fast one, there's the culprit, the fast machine is not
honoring fsync requests and is not crash-safe. I.e. if you start
writing to the db and pull the power plug out the back of the machine
it will likely power up with a corrupted database.
> -Why are regular queries much faster on this same server?
That's a whole nother subject. Most likely the faster machine can fit
the whole db in memory, or has much faster memory, or the whole
dataset is cached etc etc.
For now concentrate on figuring out of you've got an fsync problem. If
the data is just test data etc that you can afford to lose then you
can leave off fsync and not worry. But in production this is rarely
the case.