With these settings auto vacuuming of all tables became more often (as was
expected), but not of big tables with lots of inserts (and with absolutely no
updates). I've done a cron-script that does "vacuum analyze" for all databases
which runs every hour. And it seems that right now I don't have
Vladimir,
pgbouncer works with pl/proxy in transaction pooling mode. A wide spread
phrase that statement mode is for plproxy does not mean any limitations for
transaction pooling mode until you have atocommit on client. Anyway, try to
reduce connections.
try to set your autovacuum a bit more aggr
Vladimir,
pgbouncer works with pl/proxy in transaction pooling mode. A wide spread
phrase that statement mode is for plproxy does not mean any limitations for
transaction pooling mode until you have atocommit on client. Anyway, try to
reduce connections.
try to set your autovacuum a bit more aggr
13.02.2014, в 13:29, Ilya Kosmodemiansky написал(а):
> Vladimir,
>
> And, any effect on your problem?
It worked without problems longer than previous configuration but repeated
again several minutes ago :(
>
> On Thu, Feb 13, 2014 at 9:35 AM, Бородин Владимир wrote:
>> I have limited max c
On Thu, Feb 13, 2014 at 11:23 AM, Бородин Владимир wrote:
> Cool. How much ram do you have on your server and what is the size of the
> database?
It has 200 GiB of memory for 100 GB database at that time. We had migrated
the database from MySQL, that was the reason of overmuch resources. I do
not
Vladimir,
And, any effect on your problem?
On Thu, Feb 13, 2014 at 9:35 AM, Бородин Владимир wrote:
> I have limited max connections to 1000, reduced shared buffers to 8G and
> restarted postgres.
1000 is still to much in most cases. With pgbouncer in transaction
pooling mode normaly pool size
Cool. How much ram do you have on your server and what is the size of the
database?
My settings of 32 GB was set due to recommendation of 25% from here [1]. Right
now I am going to investigate the content of buffer cache with pg_buffercache
and maybe OS page cache with recently released pg_stat
2014-02-12 18:59, Бородин Владимир :
> I have read several topics [1, 2, 3, 4] with similar problems but haven't
> find a good solution. Below is some more diagnostics.
I reported the second one. The diagnostics was very similar to yours.
I think a lot people experienced this problem with big serv
I have limited max connections to 1000, reduced shared buffers to 8G and
restarted postgres.
The logs say that checkpoint finishes in 2,5 minutes (as expected due to
default checkpoint_completion_target = 0.5) with no IO spikes so I don't want
to increase checkpoint_timeout of checkpoint_segme
On Wed, Feb 12, 2014 at 8:57 PM, Бородин Владимир wrote:
>
> Yes, this is legacy, I will fix it. We had lots of inactive connections but
> right now we use pgbouncer for this. When the workload is normal we have some
> kind of 80-120 backends. Less than 10 of them are in active state. Having
>
Yes, this is legacy, I will fix it. We had lots of inactive connections but
right now we use pgbouncer for this. When the workload is normal we have some
kind of 80-120 backends. Less than 10 of them are in active state. Having
problem with locks we get lots of sessions (sometimes more than 1000
another thing which is arguable - concurrency degree. How many of your
max_connections = 4000 are actually running? 4000 definitely looks like an
overkill and they could be a serious source of concurrency, especially then
you have had barrier enabled and software raid.
Plus for 32Gb of shared buf
Oh, I haven't thought about barriers, sorry. Although I use soft raid without
batteries I have turned barriers off on one cluster shard to try.
root@rpopdb01e ~ # mount | fgrep data
/dev/md2 on /var/lib/pgsql/9.3/data type ext4 (rw,noatime,nodiratime)
root@rpopdb01e ~ # mount -o remount,nobarrier
My question was actually about barrier option, by default it is enabled on
RHEL6/ext4 and could cause serious bottleneck on io before disks are actually
involved. What says mount without arguments?
> On Feb 12, 2014, at 18:43, Бородин Владимир wrote:
>
> root@rpopdb01e ~ # fgrep data /etc/fst
root@rpopdb01e ~ # fgrep data /etc/fstab
UUID=f815fd3f-e4e4-43a6-a6a1-bce1203db3e0 /var/lib/pgsql/9.3/data ext4
noatime,nodiratime 0 1
root@rpopdb01e ~ #
According to iostat the disks are not the bottleneck.
12.02.2014, в 21:30, Ilya Kosmodemiansky написал(а):
> Hi Vladimir,
>
> Just in case:
Hi Vladimir,
Just in case: how is your ext4 mount?
Best regards,
Ilya
> On Feb 12, 2014, at 17:59, Бородин Владимир wrote:
>
> Hi all.
>
> Today I have started getting errors like below in logs (seems that I have not
> changed anything for last week). When it happens the db gets lots of
>
Hi all.
Today I have started getting errors like below in logs (seems that I have not
changed anything for last week). When it happens the db gets lots of
connections in state active, eats 100% cpu and clients get errors (due to
timeout).
2014-02-12 15:44:24.562
MSK,"rpop","rpopdb_p6",30061,
17 matches
Mail list logo
