vacuum analyse waiting
vacuum analyse waiting
От:
Nicolas Aubert <aubertnicolas11@gmail.com>
Дата:
Hello,
I have several questions regarding the use of postgres.
The database is used for zabbix supervision application. It is heavily loaded (1500 values per second).
I can not get the vacuum daily analyse, process vacuum goes into Waiting mode.
Is it possible to make tuning to force the vacuum analyse to pass?
Or do I do app down?
su - postgres -c "vacuumdb -z -e -v -d zabbix"
Thank you in advance.
Nicolas Aubert
I have several questions regarding the use of postgres.
The database is used for zabbix supervision application. It is heavily loaded (1500 values per second).
I can not get the vacuum daily analyse, process vacuum goes into Waiting mode.
Is it possible to make tuning to force the vacuum analyse to pass?
Or do I do app down?
su - postgres -c "vacuumdb -z -e -v -d zabbix"
Thank you in advance.
Nicolas Aubert
Re: vacuum analyse waiting
От:
hubert depesz lubaczewski <depesz@depesz.com>
Дата:
On Mon, Mar 21, 2016 at 10:20:02AM +0100, Nicolas Aubert wrote: > The database is used for zabbix supervision application. It is heavily > loaded (1500 values per second). > > I can not get the vacuum daily analyse, process vacuum goes into Waiting > mode. If it's waiting, then check why. pg_locks has all the important information. Best regards, depesz
Re: vacuum analyse waiting
От:
Nicolas Aubert <aubertnicolas11@gmail.com>
Дата:
the vacuum process is the 22510
here is what I found in pg_locks
virtualtransaction | pid | mode | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
3/376480 | 22510 | ExclusiveLock | t |
| | | | | 3/376480 | 22510 | ShareUpdateExclusiveLock | f | f
here is what I found in pg_locks
virtualtransaction | pid | mode | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
3/376480 | 22510 | ExclusiveLock | t |
| | | | | 3/376480 | 22510 | ShareUpdateExclusiveLock | f | f
2016-03-21 12:02 GMT+01:00 hubert depesz lubaczewski <depesz@depesz.com>:
On Mon, Mar 21, 2016 at 10:20:02AM +0100, Nicolas Aubert wrote:
> The database is used for zabbix supervision application. It is heavily
> loaded (1500 values per second).
>
> I can not get the vacuum daily analyse, process vacuum goes into Waiting
> mode.
If it's waiting, then check why. pg_locks has all the important
information.
Best regards,
depesz
Re: vacuum analyse waiting
От:
hubert depesz lubaczewski <depesz@depesz.com>
Дата:
On Mon, Mar 21, 2016 at 04:26:40PM +0100, Nicolas Aubert wrote: > the vacuum process is the 22510 > here is what I found in pg_locks > > > virtualtransaction | pid | mode | granted | fastpath > ------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+---------- > > 3/376480 | 22510 | ExclusiveLock | t | > | | | | | > 3/376480 | 22510 | ShareUpdateExclusiveLock | f | f > I'm sure there are more columns in pg_locks. What you need to find is the rows with pid of vacuum, that have granted = 'false', and see what the lock is on (the columns you ignored). Then you check what has conflicting locks on the required things. Best regards, depesz
Re: vacuum analyse waiting
От:
bricklen <bricklen@gmail.com>
Дата:
On Mon, Mar 21, 2016 at 8:26 AM, Nicolas Aubert <aubertnicolas11@gmail.com> wrote:
the vacuum process is the 22510
here is what I found in pg_locks
virtualtransaction | pid | mode | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
3/376480 | 22510 | ExclusiveLock | t |
| | | | | 3/376480 | 22510 | ShareUpdateExclusiveLock | f | f
Have a look at the lock dependency queries at https://wiki.postgresql.org/wiki/Lock_dependency_information for more information about what is holding the locks.
Re: vacuum analyse waiting
От:
Nicolas Aubert <aubertnicolas11@gmail.com>
Дата:
I'm a little lost in solving this problem.
The process vacuum waiting to disappeared. I do not think it is completed successfully.
How to verify that the vacuum passes successfully?
How to prevent it happening in modewaiting?
The process vacuum waiting to disappeared. I do not think it is completed successfully.
How to verify that the vacuum passes successfully?
How to prevent it happening in modewaiting?
2016-03-21 16:37 GMT+01:00 bricklen <bricklen@gmail.com>:
On Mon, Mar 21, 2016 at 8:26 AM, Nicolas Aubert <aubertnicolas11@gmail.com> wrote:the vacuum process is the 22510
here is what I found in pg_locks
virtualtransaction | pid | mode | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
3/376480 | 22510 | ExclusiveLock | t |
| | | | | 3/376480 | 22510 | ShareUpdateExclusiveLock | f | fHave a look at the lock dependency queries at https://wiki.postgresql.org/wiki/Lock_dependency_information for more information about what is holding the locks.
Re: vacuum analyse waiting
От:
Nicolas Aubert <aubertnicolas11@gmail.com>
Дата:
Hello,
Here are the contents of the table pg_locks when the vacuum is in waiting mode.
13382 PID number :
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------------+---------+----------
relation | 22800 | 11187 | | | | | | | | 63/673898 | 23929 | AccessShareLock | t | t
virtualxid | | | | | 63/673898 | | | | | 63/673898 | 23929 | ExclusiveLock | t | t
virtualxid | | | | | 65/10576 | | | | | 65/10576 | 13832 | ExclusiveLock | t | t
relation | 22800 | 100247 | | | | | | | | 61/345 | 1219 | RowExclusiveLock | t | t
virtualxid | | | | | 61/345 | | | | | 61/345 | 1219 | ExclusiveLock | t | t
relation | 22800 | 26143 | | | | | | | | 2/397 | 636 | RowExclusiveLock | t | t
relation | 22800 | 25219 | | | | | | | | 2/397 | 636 | RowExclusiveLock | t | t
virtualxid | | | | | 2/397 | | | | | 2/397 | 636 | ExclusiveLock | t | t
relation | 22800 | 26139 | | | | | | | | 13/133 | 502 | RowExclusiveLock | t | t
relation | 22800 | 25218 | | | | | | | | 13/133 | 502 | RowExclusiveLock | t | t
virtualxid | | | | | 13/133 | | | | | 13/133 | 502 | ExclusiveLock | t | t
relation | 22800 | 23663 | | | | | | | | 2/397 | 636 | ShareUpdateExclusiveLock | t | f
relation | 22800 | 23657 | | | | | | | | 65/10576 | 13832 | ShareUpdateExclusiveLock | f | f
relation | 22800 | 100238 | | | | | | | | 61/345 | 1219 | ShareUpdateExclusiveLock | t | f
relation | 22800 | 23657 | | | | | | | | 13/133 | 502 | ShareUpdateExclusiveLock | t | f
Here are the contents of the table pg_locks when the vacuum is in waiting mode.
13382 PID number :
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------------+---------+----------
relation | 22800 | 11187 | | | | | | | | 63/673898 | 23929 | AccessShareLock | t | t
virtualxid | | | | | 63/673898 | | | | | 63/673898 | 23929 | ExclusiveLock | t | t
virtualxid | | | | | 65/10576 | | | | | 65/10576 | 13832 | ExclusiveLock | t | t
relation | 22800 | 100247 | | | | | | | | 61/345 | 1219 | RowExclusiveLock | t | t
virtualxid | | | | | 61/345 | | | | | 61/345 | 1219 | ExclusiveLock | t | t
relation | 22800 | 26143 | | | | | | | | 2/397 | 636 | RowExclusiveLock | t | t
relation | 22800 | 25219 | | | | | | | | 2/397 | 636 | RowExclusiveLock | t | t
virtualxid | | | | | 2/397 | | | | | 2/397 | 636 | ExclusiveLock | t | t
relation | 22800 | 26139 | | | | | | | | 13/133 | 502 | RowExclusiveLock | t | t
relation | 22800 | 25218 | | | | | | | | 13/133 | 502 | RowExclusiveLock | t | t
virtualxid | | | | | 13/133 | | | | | 13/133 | 502 | ExclusiveLock | t | t
relation | 22800 | 23663 | | | | | | | | 2/397 | 636 | ShareUpdateExclusiveLock | t | f
relation | 22800 | 23657 | | | | | | | | 65/10576 | 13832 | ShareUpdateExclusiveLock | f | f
relation | 22800 | 100238 | | | | | | | | 61/345 | 1219 | ShareUpdateExclusiveLock | t | f
relation | 22800 | 23657 | | | | | | | | 13/133 | 502 | ShareUpdateExclusiveLock | t | f
2016-03-22 7:35 GMT+01:00 Nicolas Aubert <aubertnicolas11@gmail.com>:
I'm a little lost in solving this problem.
The process vacuum waiting to disappeared. I do not think it is completed successfully.
How to verify that the vacuum passes successfully?
How to prevent it happening in modewaiting?2016-03-21 16:37 GMT+01:00 bricklen <bricklen@gmail.com>:On Mon, Mar 21, 2016 at 8:26 AM, Nicolas Aubert <aubertnicolas11@gmail.com> wrote:the vacuum process is the 22510
here is what I found in pg_locks
virtualtransaction | pid | mode | granted | fastpath
------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
3/376480 | 22510 | ExclusiveLock | t |
| | | | | 3/376480 | 22510 | ShareUpdateExclusiveLock | f | fHave a look at the lock dependency queries at https://wiki.postgresql.org/wiki/Lock_dependency_information for more information about what is holding the locks.