Re: PostgreSQL caching
От | Richard Huxton |
---|---|
Тема | Re: PostgreSQL caching |
Дата | |
Msg-id | 40AE2174.7060107@archonet.com обсуждение исходный текст |
Ответ на | PostgreSQL caching (Vitaly Belman <vitalib@012.net.il>) |
Ответы |
Re: PostgreSQL caching
|
Список | pgsql-performance |
Vitaly Belman wrote: > Hello, > > I have the following problem: > > When I run some query after I just run the Postmaster, it takse > several seconds to execute (sometimes more than 10), if I rerun it > again afterwards, it takes mere milliseconds. > > So, I guess it has to do with PostgreSQL caching.. But how exactly > does it work? What does it cache? And how can I control it? There are two areas of cache - PostgreSQL's shared buffers and the operating system's disk-cache. You can't directly control what data is cached, it just keeps track of recently used data. It sounds like PG isn't being used for a while so your OS decides to use its cache for webserver files. > I would like to load selected information in the memory before a user > runs the query. Can I do it somehow? As PostgreSQL is used in my case > as webserver, it isn't really helping if the user has to wait 10 > seconds every time he goes to a new page (even if refreshing the page > would be really quick, sine Postgre already loaded the data to > memory). If you could "pin" data in the cache it would run quicker, but at the cost of everything else running slower. Suggested steps: 1. Read the configuration/tuning guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php 2. Post a sample query/explain analyse that runs very slowly when not cached. 3. If needs be, you can write a simple timed script that performs a query. Or, the autovacuum daemon might be what you want. -- Richard Huxton Archonet Ltd
В списке pgsql-performance по дате отправления: