WhiteRider wrote:There are always multiple PostgreSQL processes running regardless of active connections, because it's always there waiting for work to do. I think there are about 6 normal PostgreSQL processes - they use virtually no resources until they are needed though.
Thanks. For the record, when I was multitabling 20+ tables and I checked I had something like 11-13 postgre processes running. Either way, it's confirmed that max connections isn't the issue.
Moreover, yesterday, I increased work mem from 24MB to 128MB while lowering max connections to 40 and changing checkpoint settings to 128 along with checkpoint completion to 0.9.
Unfortunately it seems to me that the lag became more not less pronounced.
An interesting fact I noted was this. When I am experimenting with settings, I have process explorer open and yesterday I saw that my memory usage for the whole computer was down to 3.5 GB. With my old settings however memory usage hovered around 8 GB. When I was playing with PT3, it was even higher at 11 or 12GB*. Moreover, I didn't see any particular overload on CPU usage or IO. Although had a different pattern than before with intermittent spikes every now and again - I suspect because of the change in the checkpoint completion setting.
Now, it's my understanding that one large goal in making the database faster is to put as much if not all of the queries on your memory before they reach the disk where the transfer rate is far lower. Do you think that was I noticed on process explorer was an illustration of increased efficiency or I a failure to move my queries to memory?
For reference, here are the settings I ve changed:
shared_buffers = 512MB
temp_buffers = 64MB
work_mem = 128MB
maintenance_work_mem = 1024MB
wal_buffers = 16MB
checkpoint_segments = 128
random_page_cost = 2.0
effective_cache_size = 16384MB
autovacuum = on
max_connections=40
synchronous_commit=off (With the understanding that in event of power failure or crash, I will lose some transactions)
fsync = off (With the understanding that in event of power failure or crash, my database becomes corrupt)
I ll keep on experimenting till I get it right.
* I suspect the discrepancy in memory usage between PT3 and PT4 is because PT4 doesn't automatically load popup data like PT3 did.
