MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=_07484997ecc6d97e6fc994314a87b521"
Date: Sat, 07 May 2011 20:46:32 -0300
From: Ezequiel Lovelle <elovelle@dialdata.com.ar>
To: Fernando Hevia <fhevia@gmail.com>
Cc: Arpug <arpug@postgresql.org>
Subject: Re: Consulta
Organization: Teleservicios y Marketing S.A
In-Reply-To: <BANLkTimSS6HryMYyrKfP4zgske62c9ZdeA@mail.gmail.com>
References: <a525e92786a1a11f37ee3bbf813c1d69@dialdata.com.ar>
	<BANLkTim3pnqT0wC+z6x9gZGj7YdFm-PtSw@mail.gmail.com>
	<7e584a1df61e628d7b16d01a27d1a9d6@dialdata.com.ar>
	<BANLkTimB6XFr=4WUOueysXL62fW_8xbWWQ@mail.gmail.com>
	<3c82fe28fca089df4df103ce6823b1fd@dialdata.com.ar>
	<BANLkTi=k+VLwMUbTc1vJHK1KmnRiSO2_bQ@mail.gmail.com>
	<589af2fcfb2678bdf4db3045f370af4b@dialdata.com.ar>
	<BANLkTimSS6HryMYyrKfP4zgske62c9ZdeA@mail.gmail.com>
Message-ID: <6726d28cf45b54e9d92355257775977c@dialdata.com.ar>
User-Agent: DDM/0.5

--=_07484997ecc6d97e6fc994314a87b521
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=UTF-8

  

Ok, con fsync=on directamente a postgres me tardo:

#time php
script.php
real 5m34.471s
user 0m2.527s
sys 0m1.917s

 procs memory page
disks faults cpu
 r b w avm fre flt re pi po fr sr ad4 ad6 in sy cs us
sy id
 0 0 0 2336M 7457M 2 0 0 0 0 0 356 356 1260 3185 10173 1 1 98
 0 0
0 2336M 7457M 2 0 0 0 0 0 435 435 1515 3853 12193 1 1 98
 0 0 0 2336M
7457M 3 0 0 0 0 0 531 532 1843 4668 14781 0 1 98
 0 0 0 2336M 7457M 4 0
0 0 0 0 751 752 2605 6544 20742 1 1 98
 0 0 0 2336M 7457M 4 0 0 0 0 0
925 923 3238 7997 25565 1 2 98
 0 0 0 2336M 7457M 3 0 0 0 0 0 627 628
2183 5503 17403 0 1 99
 0 0 0 2336M 7457M 4 0 0 0 4 0 641 641 2193 5575
17673 1 1 98
 0 0 0 2336M 7456M 3 0 0 0 0 0 682 681 2340 5949 18812 1 1
98
 0 0 0 2336M 7456M 4 0 0 0 0 0 681 683 2382 5964 18988 1 2 98
 0 0 0
2336M 7456M 3 0 0 0 0 0 628 628 2180 5491 17401 0 2 98
 0 0 0 2336M
7456M 2 0 0 0 0 0 545 544 1906 4787 15182 0 1 99
 0 0 0 2336M 7456M 3 0
0 0 8 0 552 552 1910 4829 15343 1 1 98
 0 0 0 2336M 7456M 3 0 0 0 0 0
457 458 1608 4092 13009 0 1 99
 0 0 0 2336M 7456M 1 0 0 0 0 0 405 403
1409 3596 11401 0 1 98
 0 0 0 2336M 7456M 41 0 0 0 0 0 222 224 790 2078
6525 0 0 100
 0 0 0 2336M 7456M 3 0 0 0 0 0 693 693 2403 6102 19174 1 1
98
 0 0 0 2336M 7456M 5 0 0 0 0 0 828 827 2910 7228 23070 1 2 97
 0 0 0
2336M 7456M 3 0 0 0 4 0 715 716 2486 6238 19804 1 3 97
 0 0 0 2336M
7456M 4 0 0 0 0 0 733 733 2544 6395 20261 0 1 98
 0 0 0 2336M 7456M 636
0 0 0 656 0 532 532 1833 4879 14835 1 1 98
 0 0 0 2336M 7455M 2 0 0 0 8
0 598 598 2067 5180 16650 1 1 98
 0 0 0 2336M 7455M 3 0 0 0 0 0 667 667
2316 5865 18498 1 1 98
 0 0 0 2336M 7455M 6 0 0 0 0 0 880 879 3048 7688
24303 1 2 97
 0 0 0 2336M 7455M 27 0 0 0 0 0 758 759 2608 6650 20957 1 1
98
 0 0 0 2336M 7455M 4 0 0 0 6 0 715 713 2457 6154 19698 1 1 98
 0 0 0
2336M 7455M 9 0 0 0 0 0 176 178 632 1679 5289 0 0 100
 0 0 0 2336M 7455M
1 0 0 0 0 0 412 410 1442 3660 11588 1 1 98
 0 0 0 2336M 7455M 3 0 0 0 0
0 492 494 1712 4333 13768 1 1 98
 0 0 0 2336M 7455M 1 0 0 0 0 0 330 330
1167 2946 9470 0 1 98
 0 0 0 2336M 7455M 3 0 0 0 0 0 410 410 1441 3657
11561 0 1 99
 0 0 0 2336M 7455M 3 0 0 0 0 0 692 691 2401 6043 19114 0 1
98
 1 0 0 2336M 7455M 4 0 0 0 0 0 726 726 2526 6316 20039 1 1 98
 0 0 0
2336M 7455M 3 0 0 0 0 0 686 687 2393 5999 19103 1 1 98
 0 0 0 2336M
7454M 4 0 0 0 0 0 702 702 2427 6121 19372 1 2 98
 0 0 0 2336M 7454M 5 0
0 0 0 0 681 679 2351 5950 18803 0 2 98
 0 0 0 2336M 7454M 4 0 0 0 4 0
718 719 2479 6240 19789 0 1 99
 0 0 0 2336M 7454M 37 0 0 0 0 0 666 667
2325 5835 18548 1 1 98
 0 0 0 2336M 7454M 6 0 0 0 0 0 746 744 2587 6540
20625 1 2 98
 0 0 0 2336M 7454M 8 0 0 0 0 0 810 810 2844 7114 22456 1 2
97
 0 0 0 2336M 7454M 632 0 0 0 649 0 496 497 1742 4577 13957 0 1 99
 0
0 0 2328M 7455M 265 0 0 0 662 0 189 189 677 1795 5696 0 0 99
 0 0 0
2328M 7455M 0 0 0 0 0 0 0 0 10 171 456 0 0 100
 0 0 0 2363M 7454M 414 0
0 0 161 0 4 4 33 6478 601 2 0 98
 0 0 0 2363M 7454M 0 0 0 0 0 0 0 0 7
151 456 0 0 100
 0 0 0 2363M 7454M 0 0 0 0 0 0 0 0 18 149 575 0 0 100
 0
0 0 2363M 7454M 0 0 0 0 0 0 3 3 10 147 485 0 0 100
 0 0 0 2363M 7454M 0
0 0 0 0 0 0 0 6 149 465 0 0 100
 0 0 0 2363M 7454M 2 0 0 0 0 0 0 0 7 152
489 0 0 100
 0 0 0 2363M 7454M 0 0 0 0 4 0 2 2 24 149 604 0 0 100
 0 0 0
2363M 7454M 0 0 0 0 8 0 9 9 22 147 562 0 0 100

LOGS 

May 7 20:40:54
pgsql1 postgres[2024] [99998-1] LOG: statement: INSERT INTO tabla (aa,
bb, cc, dd, ee) VALUES ('99997
','99997','99997','99997','99997')
May 7
20:40:54 pgsql1 postgres[2024] [99999-1] LOG: statement: INSERT INTO
tabla (aa, bb, cc, dd, ee) VALUES
('99998
','99998','99998','99998','99998')
May 7 20:40:54 pgsql1
postgres[2024] [100000-1] LOG: statement: INSERT INTO tabla (aa, bb, cc,
dd, ee) VALUES ('9999
9','99999','99999','99999','99999')
May 7 20:40:54
pgsql1 postgres[2024] [100001-1] LOG: statement: INSERT INTO tabla (aa,
bb, cc, dd, ee) VALUES
('1000
00','100000','100000','100000','100000')
May 7 20:41:52 pgsql1
postgres[1954] [5-1] LOG: checkpoint starting: time
May 7 20:44:32
pgsql1 postgres[1954] [6-1] LOG: checkpoint complete: wrote 799 buffers
(0.3%); 0 transaction log file(s) added, 0 removed, 0 recycled;
write
=160.550 s, sync=0.008 s, total=160.562 s

En los logs no me
aparece gran cosa, la verdad que estoy medio perdido.

Saludos

On Sat,
7 May 2011 19:40:37 -0300, Fernando Hevia wrote: 

> Ojo: Deshabilitar
fsync aplica únicamente en casos muy especiales, como la restauración
inicial de una base de datos. Es un modo de operación insegura y puede
provocar corrupción en la base si hay un crash o corte de luz. 
> 
> Los
tiempos que te pasé son con fsync = on. 
> 
> Fijate los logs y vmstat
que te arrojan con fsync =on mientras corres el test. 
> 
> 2011/5/7
Ezequiel Lovelle 
> 
>> Puse fsync = off 
>> 
>> #time php script.php

>> 
>> real 0m50.592s
>> user 0m1.744s
>> sys 0m1.243s 
>> 
>> Mejoro
bastante, en cuanto pueda te paso lo de los logs, ¿alguna idea de algún
otro parámetro para tocar? 
>> 
>> On Sat, 7 May 2011 17:09:59 -0300,
Fernando Hevia wrote: 
>> 
>>> 2011/5/7 Ezequiel Lovelle 
>>> 
>>>>
Gracias por tu respuesta, se que no es una manera eficiente pero es que
quiero testear la bbdd en todos los aspectos. 
>>>> 
>>>> Te comento,
cuando lo hago desde la consola de postgres me da lo siguiente: 
>>>>

>>>> bbdd=> timing
>>>> El despliegue de duraciÃ³n estÃ¡ activado.
>>>>
bbdd=> INSERT INTO tabla (aa, bb, cc, dd, ee) VALUES (generate_series(1,
100000),generate_series(1, 100000),generate_series(1,
100000),generate_series(1, 100000),generate_series(1, 100000));
>>>>
INSERT 0 100000
>>>> DuraciÃ³n: 1486,699 ms 
>>>> 
>>>> Ahí veo que me
funciono perfecto, me ganas por unos ms jeje. (destaco que en realidad
esto es todo detrás de un pgpool conectado con 3 nodos, no a una bbdd
postgres directa) Pero el resultado fue bueno. 
>>>> 
>>>> El problema
es cuando lo hago con php desde un webserver, me tarda lo siguiente:

>>>> 
>>>> #time php script.php 
>>>> 
>>>> real 22m21.733s
>>>> user
0m1.846s
>>>> sys 0m1.902s 
>>>> 
>>>> Un problema de red no creo que
sea ya que todos estos servers de testeo estan en una red separada de la
mia en un switch de 100M. 
>>>> 
>>>> Lo que me hace pensar que el
problema es la velocidad del procesamiento de php en el webserver...
cosa que me parace muy rara. Igualmente voy a hacer el mismo script en
bash o perl aver si es un problema de php.
>>> 
>>> 22 minutos es una
barbaridad. 
>>> Habilitá log_checkpoints y log_lock_waits en
postgres.conf. 
>>> Fijate que dicen los logs de postgres mientras
ejecutás los inserts. 
>>> Y corré unVMSTAT 1en el server de la base
mientras ejecutás el script. 
>>> 
>>> Alguna pista tiene que salir de
esto. 
>>> Slds., 
>>> Fernando. 
>>> 
>>>> 
 

Links:
------
[1]
mailto:elovelle@dialdata.com.ar
[2] mailto:elovelle@dialdata.com.ar

--=_07484997ecc6d97e6fc994314a87b521
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=UTF-8

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
<html><body>
<p>Ok, con fsync=3Don directamente a postgres me tardo:<br /><br /><br />#t=
ime php script.php<br />real&nbsp;&nbsp;&nbsp; 5m34.471s<br />user&nbsp;&nb=
sp;&nbsp; 0m2.527s<br />sys&nbsp;&nbsp;&nbsp;&nbsp; 0m1.917s<br /><br />&nb=
sp;procs&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 page&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; disks&nbsp;&nbsp;&nbsp;&nbsp; =
faults&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cpu<br />&nbsp;r b w=
&nbsp;&nbsp;&nbsp;&nbsp; avm&nbsp;&nbsp;&nbsp; fre&nbsp;&nbsp; flt&nbsp; re=
&nbsp; pi&nbsp; po&nbsp;&nbsp;&nbsp; fr&nbsp; sr ad4 ad6&nbsp;&nbsp; in&nbs=
p;&nbsp; sy&nbsp;&nbsp; cs us sy id<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp=
; 7457M&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0=
&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 356 356 1260 3185 10173&nbsp; 1&nb=
sp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7457M&nbsp;&nbsp;&nbsp;&n=
bsp; 2&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&=
nbsp;&nbsp; 0 435 435 1515 3853 12193&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&n=
bsp;&nbsp; 2336M&nbsp; 7457M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&=
nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 531 532 1843 =
4668 14781&nbsp; 0&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7457=
M&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;=
&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 751 752 2605 6544 20742&nbsp; 1&nbsp; 1 =
98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7457M&nbsp;&nbsp;&nbsp;&nbsp; 4=
&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&=
nbsp; 0 925 923 3238 7997 25565&nbsp; 1&nbsp; 2 98<br />&nbsp;0 0 0&nbsp;&n=
bsp; 2336M&nbsp; 7457M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; =
0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 627 628 2183 5503 1=
7403&nbsp; 0&nbsp; 1 99<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7457M&nbsp=
;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;=
&nbsp;&nbsp; 4&nbsp;&nbsp; 0 641 641 2193 5575 17673&nbsp; 1&nbsp; 1 98<br =
/>&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;=
&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; =
0 682 681 2340 5949 18812&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2=
336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp=
;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 681 683 2382 5964 18988&n=
bsp; 1&nbsp; 2 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp=
;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;=
&nbsp; 0&nbsp;&nbsp; 0 628 628 2180 5491 17401&nbsp; 0&nbsp; 2 98<br />&nbs=
p;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;=
 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 545 =
544 1906 4787 15182&nbsp; 0&nbsp; 1 99<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&n=
bsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp=
; 0&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp; 0 552 552 1910 4829 15343&nbsp; 1=
&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp=
;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;=
 0&nbsp;&nbsp; 0 457 458 1608 4092 13009&nbsp; 0&nbsp; 1 99<br />&nbsp;0 0 =
0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; 0&nbs=
p;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 405 403 14=
09 3596 11401&nbsp; 0&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7=
456M&nbsp;&nbsp;&nbsp; 41&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&n=
bsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 222 224&nbsp; 790 2078 6525&nbsp; 0&nbsp; =
0 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp=
; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbs=
p;&nbsp; 0 693 693 2403 6102 19174&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp=
;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp; 0&nbsp;&nbs=
p; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 828 827 2910 722=
8 23070&nbsp; 1&nbsp; 2 97<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&n=
bsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nb=
sp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0 715 716 2486 6238 19804&nbsp; 1&nbsp; 3 97<=
br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7456M&nbsp;&nbsp;&nbsp;&nbsp; 4&nb=
sp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbs=
p; 0 733 733 2544 6395 20261&nbsp; 0&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp=
; 2336M&nbsp; 7456M&nbsp;&nbsp; 636&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;=
 0&nbsp;&nbsp; 656&nbsp;&nbsp; 0 532 532 1833 4879 14835&nbsp; 1&nbsp; 1 98=
<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 2&n=
bsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nb=
sp; 0 598 598 2067 5180 16650&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbs=
p; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&=
nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 667 667 2316 5865 184=
98&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&=
nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&n=
bsp;&nbsp; 0&nbsp;&nbsp; 0 880 879 3048 7688 24303&nbsp; 1&nbsp; 2 97<br />=
&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp; 27&nbsp;&nbsp; =
0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 758 7=
59 2608 6650 20957&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nb=
sp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;=
 0&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp; 0 715 713 2457 6154 19698&nbsp; 1&=
nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;=
&nbsp; 9&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; =
0&nbsp;&nbsp; 0 176 178&nbsp; 632 1679 5289&nbsp; 0&nbsp; 0 100<br />&nbsp;=
0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; 0=
&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 412 41=
0 1442 3660 11588&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbs=
p; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; =
0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 492 494 1712 4333 13768&nbsp; 1&n=
bsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&=
nbsp; 1&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0=
&nbsp;&nbsp; 0 330 330 1167 2946 9470&nbsp; 0&nbsp; 1 98<br />&nbsp;0 0 0&n=
bsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&=
nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 410 410 1441 =
3657 11561&nbsp; 0&nbsp; 1 99<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7455=
M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;=
&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 692 691 2401 6043 19114&nbsp; 0&nbsp; 1 =
98<br />&nbsp;1 0 0&nbsp;&nbsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 4=
&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&=
nbsp; 0 726 726 2526 6316 20039&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&n=
bsp; 2336M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; 0&nbsp;&nbsp; =
0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 686 687 2393 5999 1=
9103&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7454M&nbsp=
;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;=
&nbsp;&nbsp; 0&nbsp;&nbsp; 0 702 702 2427 6121 19372&nbsp; 1&nbsp; 2 98<br =
/>&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;=
&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; =
0 681 679 2351 5950 18803&nbsp; 0&nbsp; 2 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2=
336M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp=
;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; 0 718 719 2479 6240 19789&n=
bsp; 0&nbsp; 1 99<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7454M&nbsp;&nbsp=
;&nbsp; 37&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp=
; 0&nbsp;&nbsp; 0 666 667 2325 5835 18548&nbsp; 1&nbsp; 1 98<br />&nbsp;0 0=
 0&nbsp;&nbsp; 2336M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp; 0&nb=
sp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 746 744 2=
587 6540 20625&nbsp; 1&nbsp; 2 98<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; =
7454M&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&n=
bsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0 810 810 2844 7114 22456&nbsp; 1&nbsp=
; 2 97<br />&nbsp;0 0 0&nbsp;&nbsp; 2336M&nbsp; 7454M&nbsp;&nbsp; 632&nbsp;=
&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 649&nbsp;&nbsp; 0 496 497 =
1742 4577 13957&nbsp; 0&nbsp; 1 99<br />&nbsp;0 0 0&nbsp;&nbsp; 2328M&nbsp;=
 7455M&nbsp;&nbsp; 265&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp=
; 662&nbsp;&nbsp; 0 189 189&nbsp; 677 1795 5696&nbsp; 0&nbsp; 0 99<br />&nb=
sp;0 0 0&nbsp;&nbsp; 2328M&nbsp; 7455M&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp=
; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbs=
p;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 10&nbsp; 171&nbsp; 456&nbsp; 0&nbsp; 0=
 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp; 414&nbsp;&n=
bsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 161&nbsp;&nbsp; 0&nbsp;&nbsp=
; 4&nbsp;&nbsp; 4&nbsp;&nbsp; 33 6478&nbsp; 601&nbsp; 2&nbsp; 0 98<br />&nb=
sp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp=
; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbs=
p;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; 7&nbsp; 151&nbsp; 456&nbsp; 0&nb=
sp; 0 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&=
nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0=
&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 18&nbsp; 149&nbsp; 5=
75&nbsp; 0&nbsp; 0 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;=
&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&=
nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 3&nbsp;&nbsp; 3&nbsp;&nbsp; 10&nbsp=
; 147&nbsp; 485&nbsp; 0&nbsp; 0 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp=
; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0=
&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&=
nbsp;&nbsp; 6&nbsp; 149&nbsp; 465&nbsp; 0&nbsp; 0 100<br />&nbsp;0 0 0&nbsp=
;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; 0&nbsp;&nbs=
p; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nb=
sp;&nbsp; 0&nbsp;&nbsp;&nbsp; 7&nbsp; 152&nbsp; 489&nbsp; 0&nbsp; 0 100<br =
/>&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;=
&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; =
0&nbsp;&nbsp; 2&nbsp;&nbsp; 2&nbsp;&nbsp; 24&nbsp; 149&nbsp; 604&nbsp; 0&nb=
sp; 0 100<br />&nbsp;0 0 0&nbsp;&nbsp; 2363M&nbsp; 7454M&nbsp;&nbsp;&nbsp;&=
nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp; 8=
&nbsp;&nbsp; 0&nbsp;&nbsp; 9&nbsp;&nbsp; 9&nbsp;&nbsp; 22&nbsp; 147&nbsp; 5=
62&nbsp; 0&nbsp; 0 100<br /><br /><br />LOGS</p>
<p><br />May&nbsp; 7 20:40:54 pgsql1 postgres[2024] [99998-1] LOG:&nbsp; st=
atement: INSERT INTO tabla (aa, bb, cc, dd, ee) VALUES ('99997<br />','9999=
7','99997','99997','99997')<br />May&nbsp; 7 20:40:54 pgsql1 postgres[2024]=
 [99999-1] LOG:&nbsp; statement: INSERT INTO tabla (aa, bb, cc, dd, ee) VAL=
UES ('99998<br />','99998','99998','99998','99998')<br />May&nbsp; 7 20:40:=
54 pgsql1 postgres[2024] [100000-1] LOG:&nbsp; statement: INSERT INTO tabla=
 (aa, bb, cc, dd, ee) VALUES ('9999<br />9','99999','99999','99999','99999'=
)<br />May&nbsp; 7 20:40:54 pgsql1 postgres[2024] [100001-1] LOG:&nbsp; sta=
tement: INSERT INTO tabla (aa, bb, cc, dd, ee) VALUES ('1000<br />00','1000=
00','100000','100000','100000')<br />May&nbsp; 7 20:41:52 pgsql1 postgres[1=
954] [5-1] LOG:&nbsp; checkpoint starting: time<br />May&nbsp; 7 20:44:32 p=
gsql1 postgres[1954] [6-1] LOG:&nbsp; checkpoint complete: wrote 799 buffer=
s (0.3%); 0 transaction log file(s) added, 0 removed, 0 recycled; write<br =
/>=3D160.550 s, sync=3D0.008 s, total=3D160.562 s<br /><br />En los logs no=
 me aparece gran cosa, la verdad que estoy medio perdido.<br /><br />Saludo=
s<br /><br /></p>
<p>On Sat, 7 May 2011 19:40:37 -0300, Fernando Hevia wrote:</p>
<blockquote type=3D"cite" style=3D"padding-left:5px; border-left:#1010ff 2p=
x solid; margin-left:5px; width:100%"><!-- html ignored --><!-- head ignore=
d --><!-- meta ignored -->
<p>Ojo: Deshabilitar fsync aplica &uacute;nicamente en casos muy especiales=
, como la restauraci&oacute;n inicial de una base de datos. Es un modo de o=
peraci&oacute;n insegura y puede provocar corrupci&oacute;n en la base si h=
ay un crash o corte de luz.</p>
<div></div>
<div>Los tiempos que te pas&eacute; son con fsync =3D on.</div>
<div></div>
<div>Fijate los logs y vmstat que te arrojan con fsync =3Don mientras corre=
s el test.</div>
<div><br /><br />
<div class=3D"gmail_quote">2011/5/7 Ezequiel Lovelle <span>&lt;<a href=3D"m=
ailto:elovelle@dialdata.com.ar">elovelle@dialdata.com.ar</a>&gt;</span><br =
/>
<blockquote class=3D"gmail_quote" style=3D"margin: 0  0  0  .8ex; border-le=
ft: 1px  #ccc  solid; padding-left: 1ex;">
<div>
<p>Puse fsync =3D off</p>
<p>#time php script.php</p>
<p>real&nbsp;&nbsp;&nbsp; 0m50.592s<br />user&nbsp;&nbsp;&nbsp; 0m1.744s<br=
 />sys&nbsp;&nbsp;&nbsp;&nbsp; 0m1.243s</p>
<p>Mejoro bastante, en cuanto pueda te paso&nbsp;lo de los logs, &iquest;al=
guna idea de alg&uacute;n otro par&aacute;metro para tocar?</p>
<div>
<div class=3D"h5">
<p>On Sat, 7 May 2011 17:09:59 -0300, Fernando Hevia wrote:</p>
<p>&nbsp;</p>
</div>
</div>
<blockquote style=3D"padding-left: 5px; border-left: #1010ff  2px  solid; m=
argin-left: 5px; width: 100%;"><br /><br />
<div class=3D"gmail_quote">
<div>
<div class=3D"h5">2011/5/7 Ezequiel Lovelle <span>&lt;<a href=3D"mailto:elo=
velle@dialdata.com.ar">elovelle@dialdata.com.ar</a>&gt;</span><br />
<blockquote class=3D"gmail_quote" style=3D"margin: 0  0  0  .8ex; border-le=
ft: 1px  #ccc  solid; padding-left: 1ex;">
<div>
<p>Gracias por tu respuesta, se que no&nbsp;es&nbsp;una manera&nbsp;eficien=
te pero es que quiero testear la bbdd en todos los aspectos.</p>
<p>Te comento, cuando lo hago desde la consola de postgres me da lo siguien=
te:</p>
<p>bbdd=3D&gt; \timing<br />El despliegue de duraci&Atilde;&sup3;n est&Atil=
de;&iexcl; activado.<br />bbdd=3D&gt; INSERT INTO&nbsp;tabla (aa, bb, cc, d=
d, ee) VALUES (generate_series(1, 100000),generate_series(1, 100000),genera=
te_series(1, 100000),generate_series(1, 100000),generate_series(1, 100000))=
;<br /> INSERT 0 100000<br />Duraci&Atilde;&sup3;n: 1486,699 ms</p>
<p>Ah&iacute; veo que me funciono perfecto, me ganas por unos ms jeje. (des=
taco que en realidad esto es todo detr&aacute;s de un pgpool conectado&nbsp=
;con 3 nodos, no a una bbdd postgres directa) Pero el resultado fue bueno=
=2E</p>
<p>El problema es cuando lo hago con php desde un webserver,&nbsp;me tarda =
lo siguiente:</p>
<p>#time php script.php</p>
<p>real&nbsp;&nbsp;&nbsp; 22m21.733s<br />user&nbsp;&nbsp;&nbsp; 0m1.846s<b=
r />sys&nbsp;&nbsp;&nbsp;&nbsp; 0m1.902s</p>
<p>Un problema de red no creo que sea ya que todos estos servers de testeo =
estan en una red separada de la mia en un switch de 100M.</p>
<p>Lo que me hace pensar que el problema es la velocidad del procesamiento =
de php en el webserver... cosa que me parace muy rara. Igualmente voy&nbsp;=
 a hacer el mismo script en bash o perl&nbsp;aver si es un problema de php=
=2E</p>
</div>
</blockquote>
<div>22 minutos es una barbaridad.&nbsp;</div>
<div>Habilit&aacute; log_checkpoints y log_lock_waits en postgres.conf.</di=
v>
<div>Fijate que dicen los logs de postgres mientras ejecut&aacute;s los ins=
erts.&nbsp;</div>
</div>
</div>
<div>Y corr&eacute; un<strong>vmstat 1</strong>en el server de la base mien=
tras ejecut&aacute;s el script.</div>
<div class=3D"im">
<div>Alguna pista tiene que salir de esto.</div>
<div>Slds.,</div>
<div>Fernando.</div>
<blockquote class=3D"gmail_quote" style=3D"margin: 0  0  0  .8ex; border-le=
ft: 1px  #ccc  solid; padding-left: 1ex;">
<div>
<p>&nbsp;</p>
</div>
</blockquote>
</div>
</div>
</blockquote>
<p>&nbsp;</p>
</div>
</blockquote>
</div>
</div>
</blockquote>
</body></html>

--=_07484997ecc6d97e6fc994314a87b521--