On Mon, May 8, 2017 at 11:49 AM, Vincent Veyron <vv.lists@xxxxxxxxxx> wrote: > Hello, > > I use two dedicated bare metal servers (Online and Kimsufi). The first one takes much longer to execute a procedure that recreates a database by truncating its tables, then copying the data from a set of text files; it is however much faster for 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 Online server? 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
