From: "Craig McIlwee" <craig.mcilwee@openroadsconsulting.com>
Subject: Re: Slow performance when querying millions of rows
To: pgsql-performance@postgresql.org
In-Reply-To: <4E0A58EB.90509@2ndQuadrant.com>
Message-ID: <20110628235050.77a9baf7@mail.openroadsconsulting.com>
Date: Tue, 28 Jun 2011 19:50:50 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----------MIME--2116592281-41-delim"

This is a multi-part message in MIME format.

------------MIME--2116592281-41-delim
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=3E On 06/28/2011 05=3A28 PM=2C Craig McIlwee wrote=3A
=3E =3E Autovacuum is disabled for these tables since the data is never=20=

=3E =3E updated=2E  The tables that we are testing with at the moment will=
 not=20
=3E =3E grow any larger and have been both clustered and analyzed=2E
=3E=20
=3E Note that any such prep to keep from ever needing to maintain these=20=

=3E tables in the future should include the FREEZE option=2C possibly with=
=20
=3E some parameters tweaked first to make it more aggressive=2E  Autovacuum=
=20
=3E will eventually revisit them in order to prevent transaction ID=20
=3E wrap-around=2C even if it=27s disabled=2E  If you=27re going to the tro=
uble of=20
=3E prepping them so they are never touched again=2C you should do a freeze=
=20
=3E with the right parameters to keep this from happening again=2E
=3E=20
=3E =3E work=5Fmem=3A 512MB
=3E =3E shared=5Fbuffers=3A 64MB=2C 512MB=2C and 1024MB=2C each yielded the=
 same query=20
=3E =3E plan and took the same amount of time to execute give or take a few=
=20
=3E =3E seconds
=3E=20
=3E shared=5Fbuffers doesn=27t normally impact the query plan=3B it impacts=
 how=20
=3E much churn there is between the database and the operating system cache=
=2C=20
=3E mainly important for making write-heavy work efficient=2E  On Windows=
=2C=20
=3E you=27ll probably be safe to set this to 512MB and forget about it=2E=
  It=20
=3E doesn=27t benefit from large values anyway=2E

I was thinking that shared buffers controlled the amount of data=2C primari=
ly table and index pages=2C that the database could store in memory at once=
=2E  Based on that assumption=2C I thought that a larger value would enable=
 an entire table + index to be in memory together and speed up the query=2E=
  Am I wrong=3F

=3E=20
=3E This is a very large work=5Fmem setting however=2C so be careful that y=
ou=20
=3E won=27t have many users connecting at once if you=27re going to use it=
=2E =20
=3E Each connection can use a multiple of work=5Fmem=2C making it quite pos=
sible=20
=3E you could run out of memory with this configuration=2E  If that low use=
r=20
=3E count is true=2C you may want to make sure you=27re enforcing it by low=
ering=20
=3E max=5Fconnections=2C as a safety measure to prevent problems=2E

I plan on lowering this quite a bit since I haven=27t seen much of a boost=
 by increasing it=2E

=3E=20
=3E =3E Since the daily tables are only 360MB=2C I would hope that the enti=
re=20
=3E =3E table could be pulled into memory with one large sequential read=2E=
  Of=20
=3E =3E course this assumes that the file pieces are stored contiguously=2C=
 but=20
=3E =3E auto defrag is enabled and shows low fragmentation so Im trusting=
 =28as=20
=3E =3E much as one can=29 Windows to do the right thing here=2E  My drives=
 have a=20
=3E =3E 150MB/s sustained max throughput=2C and considering that data is sp=
read=20
=3E =3E across 5 drives I would hope to at least be able to reach the singl=
e=20
=3E =3E disk theoretical limit and read an entire table plus the index into=
=20
=3E =3E memory about 4 to 5 seconds=2E  Based on the analyze output=2C each=
 daily=20
=3E =3E table averages 6 to 7 seconds=2C so Im pretty close there and maybe=
 just=20
=3E =3E limited by disk speed=3F
=3E=20
=3E One thing to note is that your drive speed varies based on what part of=
=20
=3E the disk things are located at=3B the slower parts of the drive will be=
=20
=3E much less than 150MB/s=2E
=3E=20
=3E On Linux servers it=27s impossible to reach something close to the disk=
=27s=20
=3E raw speed without making the operating system read-ahead feature much=
=20
=3E more aggressive than it is by default=2E  Because PostgreSQL fetches a=
=20
=3E single block at a time=2C to keep the drive completely busy something h=
as=20
=3E to notice the pattern of access and be reading data ahead of when the=
=20
=3E database even asks for it=2E  You may find a parameter you can tune in=
 the=20
=3E properties for the drives somewhere in the Windows Control Panel=2E  An=
d=20
=3E there=27s a read-ahead setting on your PERC card that=27s better than=
=20
=3E nothing you may not have turned on =28not as good as the Linux one=2C b=
ut=20
=3E it=27s useful=29=2E  There are two useful settings there =28=22on=22 an=
d =22adaptive=22=20
=3E if I recall correctly=29 that you can try=2C to see which works better=
=2E

Looks like they are set to adaptive read-ahead now=2E  If the database is e=
xecuting many concurrent queries=2C is it reasonable to suspect that the IO=
 requests will compete with each other in such a way that the controller wo=
uld rarely see many sequential requests since it is serving many processes=
=3F  The controller does have an =27on=27 option also that forces read-ahea=
d=2C maybe that would solve the issue if we can rely on the data to survive=
 in the cache until the actual read request takes place=2E

=3E=20
=3E =3E Ive read documentation that says I should be able to set statistics=
=20
=3E =3E values for an entire table as opposed to per column=2C but havent f=
ound=20
=3E =3E how to do that=2E  I guess I was either too lazy to update statisti=
cs on=20
=3E =3E each column or just didnt think it would help much=2E
=3E=20
=3E You can adjust the statistics target across the entire database using=
=20
=3E the default=5Fstatistics=5Ftarget setting=2C or you can tweak them per=
 column=20
=3E using ALTER TABLE=2E  There is no table-level control=2E  I find it=20=

=3E difficult to answer questions about whether there is enough stats or no=
t=20
=3E without actually looking at pg=5Fstats to see how the database is=20
=3E interpreting the data=2C and comparing it against the real distribution=
=2E =20
=3E This is an area where flailing about trying things doesn=27t work very=
=20
=3E well=3B you need to be very systematic about the analysis and testing=
=20
=3E strategy if you=27re going to get anywhere useful=2E  It=27s not easy t=
o do=2E
=3E=20
=3E As a larger commentary on what you=27re trying to do=2C applications li=
ke=20
=3E this often find themselves at a point one day where you just can=27t al=
low=20
=3E arbitrary user queries to run against them anymore=2E  What normally=20=

=3E happens then is that the most common things that people really need end=
=20
=3E up being run one and stored in some summary form=2C using techniques su=
ch=20
=3E as materialized views=3A  http=3A//wiki=2Epostgresql=2Eorg/wiki/Materia=
lized=5FViews
=3E=20
=3E In your case=2C I would start now on trying to find the common patters=
 to=20
=3E the long running reports that people generate=2C and see if it=27s poss=
ible=20
=3E to pre-compute some portion of them and save that summary=2E  And you m=
ay=20
=3E find yourself in a continuous battle with business requests regardless=
=2E =20
=3E It=27s often key decision makers who feel they should be able to query=
 any=20
=3E way they want=2C regardless of its impact on the database=2E  Finding a=
=20
=3E middle ground there is usually challenging=2E
=3E=20
=3E --=20
=3E Greg Smith   2ndQuadrant US    greg=402ndQuadrant=2Ecom   Baltimore=2C=
 MD
=3E Comprehensive and Customized PostgreSQL Training Classes=3A
=3E http=3A//www=2E2ndquadrant=2Eus/postgresql-training/
=3E=20
=3E=20
=3E --=20
=3E Sent via pgsql-performance mailing list =28pgsql-performance=40postgres=
ql=2Eorg=29
=3E To make changes to your subscription=3A
=3E http=3A//www=2Epostgresql=2Eorg/mailpref/pgsql-performance
=3E=20

Thanks=2C
Craig=


This e-mail communication =28including any attachments=29 may contain confi=
dential and/or privileged material intended solely for the individual or en=
tity to which it is addressed=2E
P - Think before you print=2E

------------MIME--2116592281-41-delim
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=3Chtml=3E
=3Cbody=3E
=26gt=3B On 06/28/2011 05=3A28 PM=2C Craig McIlwee wrote=3A=3Cbr=3E=26gt=3B=
 =26gt=3B Autovacuum is disabled for these tables since the data is never=
 =3Cbr=3E=26gt=3B =26gt=3B updated=2E=26nbsp=3B The tables that we are test=
ing with at the moment will not =3Cbr=3E=26gt=3B =26gt=3B grow any larger a=
nd have been both clustered and analyzed=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=
=3B Note that any such prep to keep from ever needing to maintain these=20=
=3Cbr=3E=26gt=3B tables in the future should include the FREEZE option=2C p=
ossibly with =3Cbr=3E=26gt=3B some parameters tweaked first to make it more=
 aggressive=2E=26nbsp=3B Autovacuum =3Cbr=3E=26gt=3B will eventually revisi=
t them in order to prevent transaction ID =3Cbr=3E=26gt=3B wrap-around=2C e=
ven if it=27s disabled=2E=26nbsp=3B If you=27re going to the trouble of=20=
=3Cbr=3E=26gt=3B prepping them so they are never touched again=2C you shoul=
d do a freeze =3Cbr=3E=26gt=3B with the right parameters to keep this from=
 happening again=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B work=5Fmem=3A=
 512MB=3Cbr=3E=26gt=3B =26gt=3B shared=5Fbuffers=3A 64MB=2C 512MB=2C and 10=
24MB=2C each yielded the same query =3Cbr=3E=26gt=3B =26gt=3B plan and took=
 the same amount of time to execute give or take a few =3Cbr=3E=26gt=3B=20=
=26gt=3B seconds=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B shared=5Fbuffers doesn=27=
t normally impact the query plan=3B it impacts how =3Cbr=3E=26gt=3B much ch=
urn there is between the database and the operating system cache=2C =3Cbr=
=3E=26gt=3B mainly important for making write-heavy work efficient=2E=26nbs=
p=3B On Windows=2C =3Cbr=3E=26gt=3B you=27ll probably be safe to set this t=
o 512MB and forget about it=2E=26nbsp=3B It =3Cbr=3E=26gt=3B doesn=27t bene=
fit from large values anyway=2E=3Cbr=3E=3Cbr=3EI was thinking that shared b=
uffers controlled the amount of data=2C primarily table and index pages=2C=
 that the database could store in memory at once=2E=26nbsp=3B Based on that=
 assumption=2C I thought that a larger value would enable an entire table +=
 index to be in memory together and speed up the query=2E=26nbsp=3B Am I wr=
ong=3F=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B This is a very large work=
=5Fmem setting however=2C so be careful that you =3Cbr=3E=26gt=3B won=27t h=
ave many users connecting at once if you=27re going to use it=2E=26nbsp=3B=
 =3Cbr=3E=26gt=3B Each connection can use a multiple of work=5Fmem=2C makin=
g it quite possible =3Cbr=3E=26gt=3B you could run out of memory with this=
 configuration=2E=26nbsp=3B If that low user =3Cbr=3E=26gt=3B count is true=
=2C you may want to make sure you=27re enforcing it by lowering =3Cbr=3E=26=
gt=3B max=5Fconnections=2C as a safety measure to prevent problems=2E=3Cbr=
=3E=3Cbr=3EI plan on lowering this quite a bit since I haven=27t seen much=
 of a boost by increasing it=2E=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B=20=
=26gt=3B Since the daily tables are only 360MB=2C I would hope that the ent=
ire =3Cbr=3E=26gt=3B =26gt=3B table could be pulled into memory with one la=
rge sequential read=2E=26nbsp=3B Of =3Cbr=3E=26gt=3B =26gt=3B course this a=
ssumes that the file pieces are stored contiguously=2C but =3Cbr=3E=26gt=3B=
 =26gt=3B auto defrag is enabled and shows low fragmentation so Im trusting=
 =28as =3Cbr=3E=26gt=3B =26gt=3B much as one can=29 Windows to do the right=
 thing here=2E=26nbsp=3B My drives have a =3Cbr=3E=26gt=3B =26gt=3B 150MB/s=
 sustained max throughput=2C and considering that data is spread =3Cbr=3E=
=26gt=3B =26gt=3B across 5 drives I would hope to at least be able to reach=
 the single =3Cbr=3E=26gt=3B =26gt=3B disk theoretical limit and read an en=
tire table plus the index into =3Cbr=3E=26gt=3B =26gt=3B memory about 4 to=
 5 seconds=2E=26nbsp=3B Based on the analyze output=2C each daily =3Cbr=3E=
=26gt=3B =26gt=3B table averages 6 to 7 seconds=2C so Im pretty close there=
 and maybe just =3Cbr=3E=26gt=3B =26gt=3B limited by disk speed=3F=3Cbr=3E=
=26gt=3B =3Cbr=3E=26gt=3B One thing to note is that your drive speed varies=
 based on what part of =3Cbr=3E=26gt=3B the disk things are located at=3B t=
he slower parts of the drive will be =3Cbr=3E=26gt=3B much less than 150MB/=
s=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B On Linux servers it=27s impossible to=
 reach something close to the disk=27s =3Cbr=3E=26gt=3B raw speed without m=
aking the operating system read-ahead feature much =3Cbr=3E=26gt=3B more ag=
gressive than it is by default=2E=26nbsp=3B Because PostgreSQL fetches a=20=
=3Cbr=3E=26gt=3B single block at a time=2C to keep the drive completely bus=
y something has =3Cbr=3E=26gt=3B to notice the pattern of access and be rea=
ding data ahead of when the =3Cbr=3E=26gt=3B database even asks for it=2E=
=26nbsp=3B You may find a parameter you can tune in the =3Cbr=3E=26gt=3B pr=
operties for the drives somewhere in the Windows Control Panel=2E=26nbsp=3B=
 And =3Cbr=3E=26gt=3B there=27s a read-ahead setting on your PERC card that=
=27s better than =3Cbr=3E=26gt=3B nothing you may not have turned on =28not=
 as good as the Linux one=2C but =3Cbr=3E=26gt=3B it=27s useful=29=2E=26nbs=
p=3B There are two useful settings there =28=26quot=3Bon=26quot=3B and =26q=
uot=3Badaptive=26quot=3B =3Cbr=3E=26gt=3B if I recall correctly=29 that you=
 can try=2C to see which works better=2E=3Cbr=3E=3Cbr=3ELooks like they are=
 set to adaptive read-ahead now=2E=26nbsp=3B If the database is executing m=
any concurrent queries=2C is it reasonable to suspect that the IO requests=
 will compete with each other in such a way that the controller would rarel=
y see many sequential requests since it is serving many processes=3F=26nbsp=
=3B The controller does have an =27on=27 option also that forces read-ahead=
=2C maybe that would solve the issue if we can rely on the data to survive=
 in the cache until the actual read request takes place=2E=3Cbr=3E=3Cbr=3E=
=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Ive read documentation that says I shoul=
d be able to set statistics =3Cbr=3E=26gt=3B =26gt=3B values for an entire=
 table as opposed to per column=2C but havent found =3Cbr=3E=26gt=3B =26gt=
=3B how to do that=2E=26nbsp=3B I guess I was either too lazy to update sta=
tistics on =3Cbr=3E=26gt=3B =26gt=3B each column or just didnt think it wou=
ld help much=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B You can adjust the statist=
ics target across the entire database using =3Cbr=3E=26gt=3B the default=5F=
statistics=5Ftarget setting=2C or you can tweak them per column =3Cbr=3E=26=
gt=3B using ALTER TABLE=2E=26nbsp=3B There is no table-level control=2E=26n=
bsp=3B I find it =3Cbr=3E=26gt=3B difficult to answer questions about wheth=
er there is enough stats or not =3Cbr=3E=26gt=3B without actually looking a=
t pg=5Fstats to see how the database is =3Cbr=3E=26gt=3B interpreting the d=
ata=2C and comparing it against the real distribution=2E=26nbsp=3B =3Cbr=3E=
=26gt=3B This is an area where flailing about trying things doesn=27t work=
 very =3Cbr=3E=26gt=3B well=3B you need to be very systematic about the ana=
lysis and testing =3Cbr=3E=26gt=3B strategy if you=27re going to get anywhe=
re useful=2E=26nbsp=3B It=27s not easy to do=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26=
gt=3B As a larger commentary on what you=27re trying to do=2C applications=
 like =3Cbr=3E=26gt=3B this often find themselves at a point one day where=
 you just can=27t allow =3Cbr=3E=26gt=3B arbitrary user queries to run agai=
nst them anymore=2E=26nbsp=3B What normally =3Cbr=3E=26gt=3B happens then i=
s that the most common things that people really need end =3Cbr=3E=26gt=3B=
 up being run one and stored in some summary form=2C using techniques such=
 =3Cbr=3E=26gt=3B as materialized views=3A=26nbsp=3B http=3A//wiki=2Epostgr=
esql=2Eorg/wiki/Materialized=5FViews=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B In yo=
ur case=2C I would start now on trying to find the common patters to =3Cbr=
=3E=26gt=3B the long running reports that people generate=2C and see if it=
=27s possible =3Cbr=3E=26gt=3B to pre-compute some portion of them and save=
 that summary=2E=26nbsp=3B And you may =3Cbr=3E=26gt=3B find yourself in a=
 continuous battle with business requests regardless=2E=26nbsp=3B =3Cbr=3E=
=26gt=3B It=27s often key decision makers who feel they should be able to q=
uery any =3Cbr=3E=26gt=3B way they want=2C regardless of its impact on the=
 database=2E=26nbsp=3B Finding a =3Cbr=3E=26gt=3B middle ground there is us=
ually challenging=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B -- =3Cbr=3E=26gt=3B G=
reg Smith=26nbsp=3B=26nbsp=3B 2ndQuadrant US=26nbsp=3B=26nbsp=3B=26nbsp=3B=
 greg=402ndQuadrant=2Ecom=26nbsp=3B=26nbsp=3B Baltimore=2C MD=3Cbr=3E=26gt=
=3B Comprehensive and Customized PostgreSQL Training Classes=3A=3Cbr=3E=26g=
t=3B http=3A//www=2E2ndquadrant=2Eus/postgresql-training/=3Cbr=3E=26gt=3B=
 =3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B -- =3Cbr=3E=26gt=3B Sent via pgsql-perfo=
rmance mailing list =28pgsql-performance=40postgresql=2Eorg=29=3Cbr=3E=26gt=
=3B To make changes to your subscription=3A=3Cbr=3E=26gt=3B http=3A//www=2E=
postgresql=2Eorg/mailpref/pgsql-performance=3Cbr=3E=26gt=3B =3Cbr=3E=3Cbr=
=3EThanks=2C=3Cbr=3ECraig=3CBR /=3E
=3CBR /=3E
=3Cspan style=3D=22font-family=3A =27calibri=27=2C=27sans-serif=27=3B font-=
size=3A 8pt=3B mso-ascii-theme-font=3A minor-latin=3B mso-fareast-font-fami=
ly=3A =27times new roman=27=3B mso-fareast-theme-font=3A minor-fareast=3B m=
so-hansi-theme-font=3A minor-latin=3B mso-bidi-font-family=3A =27times new=
 roman=27=3B mso-bidi-theme-font=3A minor-bidi=3B mso-ansi-language=3A en-u=
s=3B mso-fareast-language=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-=
proof=3A yes=3B=22=3E=3Ci=3EThis e-mail communication =28including any atta=
chments=29 may contain confidential and/or privileged material intended sol=
ely for the individual or entity to which it is addressed=2E=3Cbr=3E=3C/i=
=3E=3C/span=3E=3Cb=3E=3Cspan style=3D=22font-family=3A webdings=3B color=3A=
 =23003300=3B font-size=3A 18pt=3B mso-fareast-font-family=3A =27times new=
 roman=27=3B mso-fareast-theme-font=3A minor-fareast=3B mso-bidi-font-famil=
y=3A =27times new roman=27=3B mso-bidi-theme-font=3A minor-bidi=3B mso-ansi=
-language=3A en-us=3B mso-fareast-language=3A en-us=3B mso-bidi-language=3A=
 ar-sa=3B mso-no-proof=3A yes=3B=22=3EP=3C/span=3E=3Cspan style=3D=22font-f=
amily=3A =27arial=27=2C=27sans-serif=27=3B color=3A =23003300=3B font-size=
=3A 8pt=3B mso-fareast-font-family=3A =27times new roman=27=3B mso-fareast-=
theme-font=3A minor-fareast=3B mso-ansi-language=3A en-us=3B mso-fareast-la=
nguage=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-proof=3A yes=3B=22=
=3E - Think before you print=3C/span=3E=3Cspan style=3D=22font-family=3A=20=
=27arial=27=2C=27sans-serif=27=3B color=3A =23003300=3B font-size=3A 10pt=
=3B mso-fareast-font-family=3A =27times new roman=27=3B mso-fareast-theme-f=
ont=3A minor-fareast=3B mso-ansi-language=3A en-us=3B mso-fareast-language=
=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-proof=3A yes=3B=22=3E=2E=
=3C/span=3E=3C/b=3E=3CBR /=3E
=3C/body=3E
=3C/html=3E


------------MIME--2116592281-41-delim--