Re: Postgres 7.3.1 poor insert/update/search performance - Mailing list pgsql-performance
From | Brian Hirt |
---|---|
Subject | Re: Postgres 7.3.1 poor insert/update/search performance |
Date | |
Msg-id | 1CE2D1AE-2DAB-11D7-BE6C-000393D9FD00@mobygames.com Whole thread Raw |
In response to | Re: Postgres 7.3.1 poor insert/update/search performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Postgres 7.3.1 poor insert/update/search performance
Re: Postgres 7.3.1 poor insert/update/search performance |
List | pgsql-performance |
Tom and others: There has been a lot of talk about shared memory size recently, along with many conflicting statements from various people. Earlier threads said that setting the shared buffer to a high values (like 512MB on a 2GB dedicated DB server) is not a good idea. A couple of reasons were mentioned. a) potential inefficiencies with the kernel and VM system b) modern kernels aggressive caching with all free memory and c) the shared memory stealing from memory the kernel would use to cache, etc. So my question is: if the kernel is caching all this data, what's the benefit of setting this to 1000 or higher? Why wouldn't i just set it to 0 if I believe my kernel is doing a good job. From all the discussion on this topic, it's still not clear to me how to calculate what value this should be set at and why. I've read these documents and others and have yet to find explanations and recommendations that i can use. http://www.postgresql.org/docs/momjian/hw_performance.pdf http://www.postgresql.org/idocs/index.php?runtime-config.html http://www.postgresql.org/idocs/index.php?kernel-resources.html http://www.postgresql.org/idocs/index.php?performance-tips.html http://www.ca.postgresql.org/docs/momjian/hw_performance/node6.html http://www.ca.postgresql.org/docs/momjian/hw_performance/node5.html http://www.ca.postgresql.org/docs/faq-english.html#3.6 This is such a common topic, it would be nice to see a more definitive and comprehensive section in the docs for tuning. Google searches for "shared_buffers site:www.postgresql.org" and "tuning site:www.postgresql.org" come up with little info. FYI: I've been running our database which is mostly read only with 1500 buffers. On a whole, we see very little IO. postgresql performs many many million queries a day, many simple, many complex. Though the database is relatively small, around 3GB. --brian On Tuesday, January 21, 2003, at 03:31 PM, Tom Lane wrote: > Seth Robertson <pgsql-performance@sysd.com> writes: >> I'll try that and report back later, but I was under the (false?) >> impression that it was primarily important when you had multiple >> database connections using the same table. > > Definitely false. shared_buffers needs to be 1000 or so for > production-grade performance. There are varying schools of thought > about whether it's useful to raise it even higher, but in any case > 64 is just a toy-installation setting. > >> seth=> explain analyze select accum from test where val = 5; >> QUERY PLAN >> ---------------------------------------------------------------------- >> ------------------------- >> Seq Scan on test (cost=0.00..323.89 rows=1 width=4) (actual >> time=0.13..14.20 rows=1 loops=1) >> Filter: (val = 5) >> Total runtime: 14.26 msec >> (3 rows) > >> seth=> explain analyze update test set accum = accum + 53 where val = >> '5'; >> QUERY PLAN >> ---------------------------------------------------------------------- >> ----------------------------------------- >> Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 >> width=18) (actual time=0.24..0.24 rows=1 loops=1) >> Index Cond: (val = 5::bigint) >> Total runtime: 0.39 msec >> (3 rows) > > The quotes are important when you are dealing with BIGINT indexes. > You won't get an indexscan if the constant looks like int4 rather than > int8. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org)
pgsql-performance by date: