Commit slower on faster PC
От | Koth, Christian (DWBI) |
---|---|
Тема | Commit slower on faster PC |
Дата | |
Msg-id | F5F602C218633C419C7BCA6CDDC6E90A023A996F@COSSMGMBX04.EMAIL.CORP.TLD обсуждение исходный текст |
Ответы |
Re: Commit slower on faster PC
(Mark Lewis <mark.lewis@mir3.com>)
Re: Commit slower on faster PC ("D'Arcy J.M. Cain" <darcy@druid.net>) Re: Commit slower on faster PC ("Joshua D. Drake" <jd@commandprompt.com>) Re: Commit slower on faster PC (Bruno Wolff III <bruno@wolff.to>) |
Список | pgsql-performance |
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. ******************************************
В списке pgsql-performance по дате отправления: