How to enhance the chance that data is in disk cache
От | Jona |
---|---|
Тема | How to enhance the chance that data is in disk cache |
Дата | |
Msg-id | 42AD844F.3070700@oismail.com обсуждение исходный текст |
Ответ на | Re: Updates on large tables are extremely slow (Mark Kirkwood <markir@paradise.net.nz>) |
Ответы |
Re: How to enhance the chance that data is in disk cache
|
Список | pgsql-performance |
Hi there I have a query (please refer to http://213.173.234.215:8080/get_content_plan.htm for the query as well as query plan) that is slow when it's run the first time and fast(ish) on all successive runs within a reasonable time period. That is, if the query is not run for like 30 min, execution time returns to the initial time. This leads me to suspect that when the query is first run, all used data have to be fetched from the disk where as once it has been run all data is available in the OS's disk cache. Comparing the execution times we're talking roughly a factor 35 in time difference, thus optimization would be handy. Is there anway to either enhance the chance that the data can be found in the disk cache or allowing the database to fetch the data faster? Is this what the CLUSTER command is for, if so, which tables would I need to cluster? Or is my only option to de-normalize the table structure around this query to speed it up? Furthermore, it seems the database spends the majority of its time in the loop marked with italic in the initial plan, any idea what it spends its time on there? Database is PG 7.3.9 on RH ES 3.0, with Dual XEON 1.9GHz processors and 2GB of RAM. effective_cache_size = 100k shared_buffers = 14k random_page_cost = 3 default_statistics_target = 50 VACUUM ANALYZE runs every few hours, so statistics should be up to date. Appreciate any input here. Cheers Jona
В списке pgsql-performance по дате отправления: