On 26.06.2018 09:48, Ideriha, Takeshi wrote:
Hi, hackers!
My customer created hundreds of thousands of partition tables and tried to
select data from hundreds of applications,
which resulted in enormous consumption of memory because it consumed # of
backend multiplied by # of local memory (ex. 100 backends X 1GB = 100GB).
Relation caches are loaded on each backend local memory.
To address this issue I'm trying to move meta caches like catcache or relcache
into shared memory.
This topic seems to have been discussed several times.
For instance this thread:
https://www.postgresql.org/message-id/CA%2BTgmobjDw_SWsxyJwT9z-YOwWv0ietuQx5fb%3DWEYdDfvCbzGQ%40mail.gmail.com
In my understanding, it discussed moving catcache and relcache to shared memory
rather than current local backend memory,
and is most concerned with performance overhead.
Robert Haas wrote:
I think it would be interested for somebody to build a prototype here
that ignores all the problems but the first and uses some
straightforward, relatively unoptimized locking strategy for the first
problem. Then benchmark it. If the results show that the idea has
legs, then we can try to figure out what a real implementation would
look like.
(One possible approach: use Thomas Munro's DHT stuff to build the shared cache.)
I'm inspired by this comment and now developing a prototype (please see
attached),
but I haven't yet put cache structure on shared memory.
Instead, I put dummy data on shared memory which is initialized at startup,
and then acquire/release lock just before/after searching/creating catcache
entry.
I haven't considered relcache and catcachelist either.
It is difficult for me to do everything at one time with right direction.
So I'm trying to make small prototype and see what I'm walking on the proper
way.
I tested pgbench to compare master branch with my patch.
0) Environment
- RHEL 7.4
- 16 cores
- 128 GB memory
1) Initialized with pgbench -i -s10
2) benchmarked 3 times for each conditions and got the average result of TPS.
|master branch | prototype |
proto/master (%)
------------------------------------------------------------------------------------
pgbench -c48 -T60 -Msimple -S | 131297 |130541 |101%
pgbench -c48 -T60 -Msimple | 4956 |4965 |95%
pgbench -c48 -T60 -Mprepared -S |129688 |132538 |97%
pgbench -c48 -T60 -Mprepared |5113 |4615 |84%
This result seems to show except for prepared protocol with "not only
SELECT" it didn't make much difference.
What do you think about it?
Before I dig deeper, I want to hear your thoughts.
Best regards,
Takeshi Ideriha
Hi,
I really think that we need to move to global caches (and especially
catalog caches) in Postgres.
Modern NUMA servers may have hundreds of cores and to be able to utilize
all of them, we may need to start large number (hundreds) of backends.
Memory overhead of local cache multiplied by 1000 can be quite significant.
But I am not sure that just using RW lock will be enough replace local
cache with global.
I am quite skeptical concerning performance results you have provided.
Once dataset completely fits in memory (which is true in your case),
select-only pgbench with prepared statements should be about two times
faster,
than without prepared statements. And in your case performance with
prepared statements is even worser.
I wonder if you have repeated each measurement multiple time, to make
sure that it is not just a fluctuation.
Also which postgresql configuration you have used. If it is default
postgresql.conf with 128Mb shared buffers size,
then you are measuring time of disk access and catalog cache is not
relevant for performance in this case.
Below are result I got with pgbench scale 100 (with scale 10 results are
slightly better) at my desktop with just 16Gb of RAM and 4 ccore.:
|master branch | prototype |
proto/master (%)
------------------------------------------------------------------------------------
pgbench -c10 -T60 -Msimple -S | 187189 |182123 |97%
pgbench -c10 -T60 -Msimple | 15495 |15112 |97%
pgbench -c10 -T60 -Mprepared -S | 98273 |92810 |94%
pgbench -c10 -T60 -Mprepared | 25796 |25169 |97%
As you see there are no surprises here: negative effect of shared cache
is the largest for the case of non-prepared selects
(because selects themselves are much faster than updates and during
compilation we have to access relations multiple times).
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
