Instructions: Visit: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Ser
ID: 3920002 • Letter: I
Question
Instructions: Visit: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Read on the options available for tuning under the categories of: Work_Mem and Effective_Cache_Size and consider how they would possibly be able to improve performance within your environment. Under the impression that users are complaining about poor performance of their queries, answer the following questions (approximately 250 words total):
Which of the two options would you tune?
Why did you make this selection?
What would you update the values to?
If this doesn't fix performance issues, what would your next step be?
Explanation / Answer
I'll choose to first tune the Work_Mem as generally having low work_mem takes longer time to execute a query and there by more wait for the users. To setup the Work_Mem, I'll look for ‘temporary file’ lines in logs and set to 2-3 times the largest temp file I see cause it gives a huge speed-up if set it properly. But be careful, as it can use that amount of memory per query. For Effective_Cache_Size its generally said to be set at 50% of the available memory. If Work_Mem doesn't work my first choice will be to check for the data that is being accessed frequently and then indexing it. Proper indexing makes queries very fast. Also indexing is based more on analysis rather than guess, so you need to know which data is more frequently accessed. Also keep a check of disk space and system load as it help in determining which way to optimise.