pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum analyse waiting
8+ messages / 3 participants
[nested] [flat]
* vacuum analyse waiting
@ 2016-03-21 09:20 Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nicolas Aubert @ 2016-03-21 09:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
@ 2016-03-21 11:02 ` hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: hubert depesz lubaczewski @ 2016-03-21 11:02 UTC (permalink / raw)
To: Nicolas Aubert <aubertnicolas11@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
@ 2016-03-21 15:26 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 15:35 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:37 ` Re: vacuum analyse waiting bricklen <bricklen@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Nicolas Aubert @ 2016-03-21 15:26 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-admin
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
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
@ 2016-03-21 15:35 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 8+ messages in thread
From: hubert depesz lubaczewski @ 2016-03-21 15:35 UTC (permalink / raw)
To: Nicolas Aubert <aubertnicolas11@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
@ 2016-03-21 15:37 ` bricklen <bricklen@gmail.com>
2016-03-22 06:35 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: bricklen @ 2016-03-21 15:37 UTC (permalink / raw)
To: Nicolas Aubert <aubertnicolas11@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 15:37 ` Re: vacuum analyse waiting bricklen <bricklen@gmail.com>
@ 2016-03-22 06:35 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-24 06:44 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nicolas Aubert @ 2016-03-22 06:35 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
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 | 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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 15:37 ` Re: vacuum analyse waiting bricklen <bricklen@gmail.com>
2016-03-22 06:35 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
@ 2016-03-24 06:44 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-24 09:54 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nicolas Aubert @ 2016-03-24 06:44 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
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
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 | 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.
>>
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: vacuum analyse waiting
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` Re: vacuum analyse waiting hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 15:37 ` Re: vacuum analyse waiting bricklen <bricklen@gmail.com>
2016-03-22 06:35 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-24 06:44 ` Re: vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
@ 2016-03-24 09:54 ` Nicolas Aubert <aubertnicolas11@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nicolas Aubert @ 2016-03-24 09:54 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: pgsql-admin
Despite pid vacuum in waiting mode, it seems that the vacuum either success.
See attached document.
Is this the right method to validate the vacuum?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Attachments:
vacuum.txt (text/plain, 6.3K) download
relname | last_vacuum | last_analyze | last_autovacuum
------------------------------+-------------------------------+-------------------------------+-------------------------------
history_text_new_p2016_03_02 | 2016-03-21 15:57:52.176634+01 | 2016-03-21 15:57:52.866506+01 | 2016-03-23 07:11:05.520235+01
history_str_new_p2016_01_05 | 2016-03-21 15:31:19.735406+01 | 2016-03-21 15:31:20.078448+01 | 2016-03-22 19:32:31.485205+01
pg_toast_135608 | 2016-03-21 15:34:45.299189+01 | |
history_str_p2016_03_11 | 2016-03-21 20:47:31.069054+01 | 2016-03-21 20:47:36.768961+01 | 2016-03-21 20:26:24.177041+01
pg_toast_124240 | 2016-03-23 18:57:13.434521+01 | | 2016-03-22 05:48:41.500757+01
trends_new2_p2015_09 | 2016-03-21 15:23:34.592037+01 | 2016-03-21 15:23:34.970587+01 | 2016-03-24 09:31:42.891137+01
pg_toast_22801 | 2016-03-21 15:03:47.851727+01 | | 2016-03-19 10:05:25.851831+01
expressions | 2016-03-21 15:20:31.287872+01 | 2016-03-21 15:20:31.288247+01 | 2016-03-24 07:39:12.732038+01
history_text_new_p2016_01_30 | 2016-03-21 15:54:50.340523+01 | 2016-03-21 15:54:50.951863+01 | 2016-03-23 05:20:58.856945+01
pg_attrdef | 2016-03-21 16:00:37.578562+01 | 2016-03-21 16:00:37.779231+01 | 2016-03-23 08:45:15.484963+01
sql_packages | 2016-03-23 19:08:30.475584+01 | 2016-03-23 19:08:30.476042+01 | 2016-03-23 18:23:14.054282+01
history_new_p2015_12_25 | 2016-03-23 19:04:00.577701+01 | 2016-03-23 19:05:59.996862+01 | 2016-03-23 18:19:04.530534+01
history_new_p2016_03_17 | 2016-03-21 15:54:10.986789+01 | 2016-03-21 15:54:11.301913+01 | 2016-03-23 04:59:17.958202+01
history_new_p2016_01_14 | 2016-03-21 15:54:44.963979+01 | 2016-03-21 15:54:45.291196+01 | 2016-03-23 05:18:16.859938+01
pg_attribute | 2016-03-21 16:04:05.840571+01 | 2016-03-21 16:04:05.88003+01 | 2016-03-23 10:16:57.111555+01
history_new_p2016_02_17 | 2016-03-21 20:46:27.248283+01 | 2016-03-21 20:47:28.187102+01 | 2016-03-21 20:31:34.811134+01
history_log | 2016-03-21 15:35:44.429398+01 | 2016-03-21 15:35:44.429971+01 | 2016-03-22 21:49:55.23187+01
history_text_new_p2016_03_01 | 2016-03-21 15:59:02.22506+01 | 2016-03-21 15:59:02.875878+01 | 2016-03-23 07:55:34.210539+01
trends_new2_p2016_02 | 2016-03-23 19:26:00.971556+01 | 2016-03-23 19:29:04.694972+01 | 2016-03-23 18:57:55.943584+01
pg_toast_24020 | 2016-03-21 14:58:32.610645+01 | | 2016-03-22 06:40:25.226222+01
pg_depend | 2016-03-21 15:53:08.631541+01 | 2016-03-21 15:53:08.655009+01 | 2016-03-23 04:30:46.552792+01
history_new_p2016_03_04 | 2016-03-21 15:38:34.889304+01 | 2016-03-21 15:38:41.151393+01 | 2016-03-22 23:13:02.306256+01
history_new_p2016_01_18 | 2016-03-21 17:55:36.039886+01 | 2016-03-21 17:56:11.655502+01 | 2016-03-23 15:25:50.918361+01
history_new_p2016_02_02 | 2016-03-21 20:28:35.455541+01 | 2016-03-21 20:30:08.24278+01 | 2016-03-21 19:39:55.882965+01
pg_toast_99586 | 2016-03-21 15:52:23.756785+01 | | 2016-03-15 22:34:47.91171+01
history_text_new_p2016_01_19 | 2016-03-23 19:37:21.383001+01 | 2016-03-23 19:37:30.339333+01 | 2016-03-23 18:43:17.96266+01
pg_toast_22880 | 2016-03-21 15:03:47.857193+01 | | 2016-03-19 10:05:25.935864+01
slideshows | 2016-03-23 19:10:11.663115+01 | 2016-03-23 19:10:11.66336+01 | 2016-03-23 18:28:04.473768+01
trends_new2_p2015_04 | 2016-03-21 15:15:17.663149+01 | 2016-03-21 15:15:18.034864+01 | 2016-03-24 05:02:39.961919+01
history_str_new_p2016_02_06 | 2016-03-21 15:32:24.78511+01 | 2016-03-21 15:32:25.243645+01 | 2016-03-22 20:03:09.666436+01
sql_parts | 2016-03-23 19:10:38.746138+01 | 2016-03-23 19:10:38.74655+01 | 2016-03-23 18:28:25.753243+01
pg_toast_23528 | 2016-03-21 14:54:57.475843+01 | | 2016-03-22 06:40:25.129041+01
pg_type | 2016-03-23 18:57:09.921393+01 | 2016-03-23 18:57:10.010412+01 | 2016-03-23 17:17:52.739545+01
groups | 2016-03-21 15:24:46.561494+01 | 2016-03-21 15:24:46.562875+01 | 2016-03-24 10:16:22.613528+01
opmessage_usr | 2016-03-21 16:01:43.356722+01 | 2016-03-21 16:01:43.357027+01 | 2016-03-23 08:59:52.510952+01
history_str_new_p2015_12_29 | 2016-03-21 15:30:50.350948+01 | 2016-03-21 15:30:50.352226+01 | 2016-03-22 19:20:07.977134+01
history_log_new_p2016_01_25 | 2016-03-21 15:37:02.893913+01 | 2016-03-21 15:37:02.897295+01 | 2016-03-22 22:23:22.703504+01
media | 2016-03-23 19:25:13.020115+01 | 2016-03-23 19:25:13.020628+01 | 2016-03-23 18:39:03.167377+01
pg_toast_136753 | 2016-03-21 15:05:22.306026+01 | |
history_str_p2016_03_02 | 2016-03-21 16:00:18.364823+01 | 2016-03-21 16:00:18.798456+01 | 2016-03-23 08:34:49.854332+01
history_text_new_p2016_01_21 | 2016-03-21 14:59:29.797807+01 | 2016-03-21 14:59:32.585464+01 | 2016-03-23 22:33:25.094711+01
history_p2016_03_20 | 2016-03-23 18:55:54.061404+01 | 2016-03-23 18:57:09.709977+01 | 2016-03-23 18:04:29.296784+01
history_str_new_p2016_01_21 | 2016-03-21 15:32:06.547694+01 | 2016-03-21 15:32:07.044122+01 | 2016-03-22 19:54:37.004944+01
sysmap_url | 2016-03-21 14:54:57.486467+01 | 2016-03-21 14:54:57.486795+01 | 2016-03-23 19:54:31.792698+01
history_str_new_p2016_01_25 | 2016-03-21 15:30:38.829747+01 | 2016-03-21 15:30:39.299804+01 | 2016-03-22 19:15:54.396146+01
pg_toast_98896 | 2016-03-21 15:35:44.974559+01 | | 2016-03-15 22:34:47.690318+01
history_uint_old | 2016-03-21 14:51:57.1096+01 | 2016-03-21 14:54:45.557016+01 | 2016-03-23 14:05:41.691348+01
httpstepitem | 2016-03-23 19:11:08.515691+01 | 2016-03-23 19:11:08.770433+01 | 2016-03-23 18:31:06.026661+01
images | 2016-03-21 15:07:17.309836+01 | 2016-03-21 15:07:17.324922+01 | 2016-03-24 01:59:36.236597+01
end of thread, other threads:[~2016-03-24 09:54 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-21 09:20 vacuum analyse waiting Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 11:02 ` hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:26 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-21 15:35 ` hubert depesz lubaczewski <depesz@depesz.com>
2016-03-21 15:37 ` bricklen <bricklen@gmail.com>
2016-03-22 06:35 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-24 06:44 ` Nicolas Aubert <aubertnicolas11@gmail.com>
2016-03-24 09:54 ` Nicolas Aubert <aubertnicolas11@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox