Re: [PERFORM] XFS filessystem for Datawarehousing
Milen, XFS, EXT3, JFS For what reason are you planning to use a journaling FS? I think using WAL, fsyncing every transaction and using a journaling FS is tautologous. And if you have problems using EXT2 you can just add the journal later without loosing data. My tests using EXT2 showed a performance boost up to 50% on INSERTs. Christian I am pretty exited whether XFS will clearly outpertform ETX3 (no default setups for both are planned !). I am not sure whether is it worth to include JFS in comparison too ... Best Regards, Milen Kulev ** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely for the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and 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 those 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
Re: [PERFORM] Commit slower on faster PC
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: 1 number of transactions actually processed: 1/1 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: 1 number of transactions actually processed: 1/1 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: 1 number of transactions actually processed: 1/1 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: 1 number of transactions actually processed: 1/1 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 for the use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and 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 those 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 2: Don't 'kill -9' the postmaster
[PERFORM] 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 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)
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 on both machines:
shared_buffers = 2
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 log 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.21
PC2: socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 8 0.15
PC1: bind(10, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr(0.0.0.0)}, 16) = 0 0.07
PC2: bind (8, {sin_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr(0.0.0.0)}}, 16) = 0 0.07
PC1: getsockname(10, {sa_family=AF_INET, sin_port=htons(32820),
sin_addr=inet_addr(0.0.0.0)}, [16]) = 0 0.05
PC2: getsockname( 8, {sin_family=AF_INET, sin_port=htons(36219),
sin_addr=inet_addr(0.0.0.0)}}, [16]) = 0 0.05
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.06
PC2: setsockopt (8, SOL_TCP, TCP_NODELAY, [1], 4) = 0 0.04
Inserting and commiting the data: exec. time
-
PC1:
send(10, B\\0\0INSERT INTO performance_test VAL..., 175, 0) = 175
0.15
recv(10, 2\0\17INSERT 0 1\0Z\0\0\0\5T, 8192, 0) = 53 0.07
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.11
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.14
recv(8, 2\0\17INSERT 0 1\0Z\0\0\0\5T, 8192, 0) = 53 0.05
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.09
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 for the use of the individual
or entity to whom they are addressed and may be subject to legal privilege. If
you have received this e-mail in error you should notify the sender immediately
by reply e-mail, delete the message from your system and 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 those 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
