Обсуждение: Commit slower on faster PC
Hi,
please help me with the following problem:
I have noticed a strange performance behaviour using a commit statement on two different machines. On one of the
machinesthe commit is many times faster than on the other machine which has faster hardware. Server and client are
runningalways on the same machine.
Server version (same on both machines): PostgreSQL 8.1.3. (same binaries as well)
PC1:
----
Pentium 4 (2.8 GHz)
1GB RAM
IDE-HDD (approx. 50 MB/s rw), fs: ext3
Mandrake Linux: Kernel 2.4.22
PC2:
----
Pentium 4 (3.0 GHz)
2GB RAM
SCSI-HDD (approx. 65 MB/s rw), fs: ext3
Mandrake Linux: Kernel 2.4.32
Both installations of the database have the same configuration, different from default are only the following settings
onboth machines:
shared_buffers = 20000
listen_addresses = '*'
max_stack_depth = 4096
pgbench gives me the following results:
PC1:
----
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 269.905533 (including connections establishing)
tps = 293.625393 (excluding connections establishing)
PC2:
----
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 46.061935 (including connections establishing)
tps = 46.519634 (excluding connections establishing)
My own performance test sql script which inserts and (auto)commits some data into a simple table produces the following
logoutput in the server log:
PC1:
----
LOG: duration: 1.441 ms statement: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
STATEMENT: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
PC2:
----
LOG: duration: 29.979 ms statement: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
STATEMENT: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
I created a 'strace' one both machines which is interesting:
Opening the socket:
-------------------
PC1: socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 10 <0.000021>
PC2: socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 8 <0.000015>
PC1: bind(10, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("0.0.0.0")}, 16) = 0 <0.000007>
PC2: bind (8, {sin_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("0.0.0.0")}}, 16) = 0 <0.000007>
PC1: getsockname(10, {sa_family=AF_INET, sin_port=htons(32820), sin_addr=inet_addr("0.0.0.0")}, [16]) = 0 <0.000005>
PC2: getsockname( 8, {sin_family=AF_INET, sin_port=htons(36219), sin_addr=inet_addr("0.0.0.0")}}, [16]) = 0 <0.000005>
PC1: connect(10, {sa_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}, 16) = 0 <0.000440>
PC2: connect( 8, {sin_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}}, 16) = 0 <0.000394>
PC1: setsockopt(10, SOL_TCP, TCP_NODELAY, [1], 4) = 0 <0.000006>
PC2: setsockopt (8, SOL_TCP, TCP_NODELAY, [1], 4) = 0 <0.000004>
Inserting and commiting the data: <exec. time>
---------------------------------
PC1:
----
send(10, "B\....\0<\0INSERT INTO performance_test VAL"..., 175, 0) = 175 <0.000015>
recv(10, "2\....0\17INSERT 0 1\0Z\0\0\0\5T", 8192, 0) = 53 <0.000007>
send(10, "B\0\0\0\17\0S_2\0\0\0\0\0\0\0E\0\0\0\t\0\0\0\0\1S\0\0\0\4", 31, 0) = 31 <0.000011>
recv(10, "2\0\0\0\4C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 8192, 0) = 23 <0.000211>
PC2:
----
send(8, "B\....\0<\0INSERT INTO performance_test VAL"..., 175, 0) = 175 <0.000014>
recv(8, "2\....0\17INSERT 0 1\0Z\0\0\0\5T", 8192, 0) = 53 <0.000005>
send(8, "B\0\0\0\17\0S_2\0\0\0\0\0\0\0E\0\0\0\t\0\0\0\0\1S\0\0\0\4", 31, 0) = 31 <0.000009>
recv(8, "2\0\0\0\4C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 8192, 0) = 23 <0.0253>
Every command is a bit faster on PC2 except the last one which is many times slower.
Any help or hint where to look at would be highly appreciated because I'm running out of ideas ;-).
regards,
Christian
******************************************
The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely
forthe use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have
receivedthis e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your
systemand notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other
person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent
thoseof the company. The recipient should check this e-mail and any attachments for the presence of viruses. The
companyaccepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email.
******************************************
The IDE drive is almost certainly lying about flushing data to the disk.
Lower-end consumer drives often do.
What this means is that commits will be a whole lot faster, but the
database loses its ACID guarantees, because a power failure at the wrong
moment could corrupt the whole database.
If you don't care about your data and want the SCSI drive to perform
fast just like the IDE drive, you can set fsync = off in your
configuration file.
-- Mark
On Wed, 2006-07-12 at 10:16 -0600, Koth, Christian (DWBI) wrote:
> Hi,
>
> please help me with the following problem:
>
> I have noticed a strange performance behaviour using a commit statement on two different machines. On one of the
machinesthe commit is many times faster than on the other machine which has faster hardware. Server and client are
runningalways on the same machine.
>
> Server version (same on both machines): PostgreSQL 8.1.3. (same binaries as well)
>
> PC1:
> ----
> Pentium 4 (2.8 GHz)
> 1GB RAM
> IDE-HDD (approx. 50 MB/s rw), fs: ext3
> Mandrake Linux: Kernel 2.4.22
>
>
> PC2:
> ----
> Pentium 4 (3.0 GHz)
> 2GB RAM
> SCSI-HDD (approx. 65 MB/s rw), fs: ext3
> Mandrake Linux: Kernel 2.4.32
>
>
> Both installations of the database have the same configuration, different from default are only the following
settingson both machines:
>
> shared_buffers = 20000
> listen_addresses = '*'
> max_stack_depth = 4096
>
>
> pgbench gives me the following results:
> PC1:
> ----
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 1
> number of transactions per client: 10
> number of transactions actually processed: 10/10
> tps = 269.905533 (including connections establishing)
> tps = 293.625393 (excluding connections establishing)
>
> PC2:
> ----
> transaction type: TPC-B (sort of)
> scaling factor: 1
> number of clients: 1
> number of transactions per client: 10
> number of transactions actually processed: 10/10
> tps = 46.061935 (including connections establishing)
> tps = 46.519634 (excluding connections establishing)
>
>
> My own performance test sql script which inserts and (auto)commits some data into a simple table produces the
followinglog output in the server log:
>
> PC1:
> ----
> LOG: duration: 1.441 ms statement: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
> STATEMENT: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
>
> PC2:
> ----
> LOG: duration: 29.979 ms statement: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
> STATEMENT: INSERT INTO performance_test VALUES (500938362, 'Xawhefjmd');
>
>
> I created a 'strace' one both machines which is interesting:
>
> Opening the socket:
> -------------------
> PC1: socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 10 <0.000021>
> PC2: socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 8 <0.000015>
>
> PC1: bind(10, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("0.0.0.0")}, 16) = 0 <0.000007>
> PC2: bind (8, {sin_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("0.0.0.0")}}, 16) = 0 <0.000007>
>
> PC1: getsockname(10, {sa_family=AF_INET, sin_port=htons(32820), sin_addr=inet_addr("0.0.0.0")}, [16]) = 0 <0.000005>
> PC2: getsockname( 8, {sin_family=AF_INET, sin_port=htons(36219), sin_addr=inet_addr("0.0.0.0")}}, [16]) = 0
<0.000005>
>
> PC1: connect(10, {sa_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}, 16) = 0 <0.000440>
> PC2: connect( 8, {sin_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}}, 16) = 0 <0.000394>
>
> PC1: setsockopt(10, SOL_TCP, TCP_NODELAY, [1], 4) = 0 <0.000006>
> PC2: setsockopt (8, SOL_TCP, TCP_NODELAY, [1], 4) = 0 <0.000004>
>
>
> Inserting and commiting the data: <exec. time>
> ---------------------------------
> PC1:
> ----
> send(10, "B\....\0<\0INSERT INTO performance_test VAL"..., 175, 0) = 175 <0.000015>
> recv(10, "2\....0\17INSERT 0 1\0Z\0\0\0\5T", 8192, 0) = 53 <0.000007>
> send(10, "B\0\0\0\17\0S_2\0\0\0\0\0\0\0E\0\0\0\t\0\0\0\0\1S\0\0\0\4", 31, 0) = 31 <0.000011>
> recv(10, "2\0\0\0\4C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 8192, 0) = 23 <0.000211>
>
> PC2:
> ----
> send(8, "B\....\0<\0INSERT INTO performance_test VAL"..., 175, 0) = 175 <0.000014>
> recv(8, "2\....0\17INSERT 0 1\0Z\0\0\0\5T", 8192, 0) = 53 <0.000005>
> send(8, "B\0\0\0\17\0S_2\0\0\0\0\0\0\0E\0\0\0\t\0\0\0\0\1S\0\0\0\4", 31, 0) = 31 <0.000009>
> recv(8, "2\0\0\0\4C\0\0\0\vCOMMIT\0Z\0\0\0\5I", 8192, 0) = 23 <0.0253>
>
> Every command is a bit faster on PC2 except the last one which is many times slower.
> Any help or hint where to look at would be highly appreciated because I'm running out of ideas ;-).
>
>
> regards,
> Christian
>
>
> ******************************************
> The information contained in, or attached to, this e-mail, may contain confidential information and is intended
solelyfor the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you
havereceived this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from
yoursystem and notify your system manager. Please do not copy it for any purpose, or disclose its contents to any
otherperson. The views or opinions presented in this e-mail are solely those of the author and do not necessarily
representthose of the company. The recipient should check this e-mail and any attachments for the presence of viruses.
The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email.
> ******************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
On Wed, 12 Jul 2006 10:16:40 -0600 "Koth, Christian (DWBI)" <Christian.Koth@smiths-heimann.com> wrote: > I have noticed a strange performance behaviour using a commit statement on two different machines. On one of the machinesthe commit is many times faster than on the other machine which has faster hardware. Server and client are runningalways on the same machine. > > Server version (same on both machines): PostgreSQL 8.1.3. (same binaries as well) > > PC1: > ---- > Pentium 4 (2.8 GHz) > 1GB RAM > IDE-HDD (approx. 50 MB/s rw), fs: ext3 > Mandrake Linux: Kernel 2.4.22 > > > PC2: > ---- > Pentium 4 (3.0 GHz) > 2GB RAM > SCSI-HDD (approx. 65 MB/s rw), fs: ext3 > Mandrake Linux: Kernel 2.4.32 > > > Both installations of the database have the same configuration, different from default are only the following settingson both machines: > > shared_buffers = 20000 > listen_addresses = '*' > max_stack_depth = 4096 > > > pgbench gives me the following results: > PC1: > ---- > transaction type: TPC-B (sort of) > scaling factor: 1 > number of clients: 1 > number of transactions per client: 10 > number of transactions actually processed: 10/10 > tps = 269.905533 (including connections establishing) > tps = 293.625393 (excluding connections establishing) > > PC2: > ---- > transaction type: TPC-B (sort of) > scaling factor: 1 > number of clients: 1 > number of transactions per client: 10 > number of transactions actually processed: 10/10 > tps = 46.061935 (including connections establishing) > tps = 46.519634 (excluding connections establishing) I'm not sure 10 transactions is enough of a test. You could just be seeing the result of your IDE drive lying to you about actually writing your data. There may be other considerations but I would start with checking with 10,000 or 100,000 transactions to overcome the driver buffering. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Wednesday 12 July 2006 09:16, Koth, Christian (DWBI) wrote:
> Hi,
>
> please help me with the following problem:
>
> I have noticed a strange performance behaviour using a commit statement on
> two different machines. On one of the machines the commit is many times
> faster than on the other machine which has faster hardware. Server and
> client are running always on the same machine.
>
> Server version (same on both machines): PostgreSQL 8.1.3. (same binaries as
> well)
Heh, I bet you are being bit by the cache on the IDE drive. What happens if
you turn fsync off?
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Wed, Jul 12, 2006 at 10:16:40 -0600, "Koth, Christian (DWBI)" <Christian.Koth@smiths-heimann.com> wrote: > > I have noticed a strange performance behaviour using a commit statement on two different machines. On one of the machinesthe commit is many times faster than on the other machine which has faster hardware. Server and client are runningalways on the same machine. > > Server version (same on both machines): PostgreSQL 8.1.3. (same binaries as well) > > PC1: > ---- > IDE-HDD (approx. 50 MB/s rw), fs: ext3 > > PC2: > ---- > SCSI-HDD (approx. 65 MB/s rw), fs: ext3 > > Both installations of the database have the same configuration, different from default are only the following settingson both machines: > > pgbench gives me the following results: > PC1: > ---- > tps = 293.625393 (excluding connections establishing) > > PC2: > ---- > tps = 46.519634 (excluding connections establishing) Have you checked to see if the ide drive is lying about having written the data to the platters?
Hello all, thanks a lot for your help so far. You all where right about the IDE drive is somehow caching the data. See the test results below. I also get different tps values every time I run pgbench on PC1 (between 300 and 80 tps for 100 transactions). I don't think it's a good idea to disable fsync even if ext3 (doing a sync also every x second or so) and a UPS is used. Christian PC1 (IDE): ---------- fsynch on: ---------- number of transactions per client: 100 number of transactions actually processed: 100/100 tps = 213.115558 (including connections establishing) tps = 214.710227 (excluding connections establishing) number of transactions per client: 10000 number of transactions actually processed: 10000/10000 tps = 126.159130 (including connections establishing) tps = 126.163172 (excluding connections establishing) fsynch off: ----------- number of transactions per client: 100 number of transactions actually processed: 100/100 tps = 413.849044 (including connections establishing) tps = 419.028942 (excluding connections establishing) number of transactions per client: 10000 number of transactions actually processed: 10000/10000 tps = 166.057838 (including connections establishing) tps = 166.064227 (excluding connections establishing) PC2 (SCSI): ----------- fsynch on: ---------- number of transactions per client: 100 number of transactions actually processed: 100/100 tps = 44.640785 (including connections establishing) tps = 44.684649 (excluding connections establishing) number of transactions per client: 10000 number of transactions actually processed: 10000/10000 tps = 42.322486 (including connections establishing) tps = 42.324096 (excluding connections establishing) fsynch off: ----------- number of transactions per client: 100 number of transactions actually processed: 100/100 tps = 910.406861 (including connections establishing) tps = 925.428936 (excluding connections establishing) number of transactions per client: 10000 number of transactions actually processed: 10000/10000 tps = 957.376543 (including connections establishing) tps = 957.603815 (excluding connections establishing) ****************************************** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely forthe use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have receivedthis e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your systemand notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent thoseof the company. The recipient should check this e-mail and any attachments for the presence of viruses. The companyaccepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. ******************************************