(2013/09/09 8:19), Tomas Vondra wrote:
On 8.9.2013 23:04, Jeff Janes wrote:
On Tue, Sep 3, 2013 at 10:09 PM, Satoshi Nagayasu <sn...@uptime.jp>
wrote:
Hi,
(2013/09/04 13:07), Alvaro Herrera wrote:
Satoshi Nagayasu wrote:
As you may know, this file could be handreds of MB in size,
because pgstat.stat holds all access statistics in each
database, and it needs to read/write an entire pgstat.stat
frequently.
As a result, pgstat.stat often generates massive I/O operation,
particularly when having a large number of tables in the
database.
We already changed it:
commit 187492b6c2e8cafc5b39063ca3b67846e8155d24 Author: Alvaro
Herrera <alvhe...@alvh.no-ip.org> Date: Mon Feb 18 17:56:08
2013 -0300
Split pgstat file in smaller pieces
Thanks for the comments. I forgot to mention that.
Yes, we have already split single pgstat.stat file into several
pieces.
However, we still need to read/write large amount of statistics
data when we have a large number of tables in single database or
multiple databases being accessed. Right?
Do you have a test case for measuring this? I vaguely remember from
when I was testing the split patch, that I thought that after that
improvement the load that was left was so low that there was little
point in optimizing it further.
This is actually a pretty good point. Creating a synthetic test case is
quite simple - just create 1.000.000 tables in a single database, but
I'm wondering if it's actually realistic. Do we have a real-world
example where the current "one stat file per db" is not enough?
I have several assumptions for that.
- Single shared database contains thousands of customers.
- Each customer has hundreds of tables and indexes.
- Customers are separated by schemas (namespaces) in single database.
- Application server uses connection pooling for performance reason.
- Workload (locality in the table access) can not be predicted.
Looks reasonable?
The reason why I worked on the split patch is that our application is
slightly crazy and creates a lot of tables (+ indexes) on the fly, and
as we have up to a thousand databases on each host, we often ended up
with a huge stat file.
Splitting the stat file improved that considerably, although that's
partially because we have the stats on a tmpfs, so I/O is not a problem,
and the CPU overhead is negligible thanks to splitting the stats per
database.
I agree that splitting a single large database into several pieces,
like thousands of tiny databases, could be an option in some cases.
However, what I intend here is eliminating those limitations on
database design.
In fact, when considering connection pooling, splitting a database
is not a good idea, because AFAIK, many connection poolers manage
connections per database.
So, I'd like to support 100k tables in single database.
Any comments?
Regards,
But AFAIK there are operating systems where creating a filesystem in RAM
is not that simple - e.g. Windows. In such cases even a moderate number
of objects may be a significant issue I/O-wise. But then again, I can't
really think of reasonable a system creating that many objects in a
single database (except for e.g. a shared database using schemas instead
of databases).
Tomas
--
Satoshi Nagayasu <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
