pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto Vacuum question?
9+ messages / 5 participants
[nested] [flat]
* Auto Vacuum question?
@ 2007-06-04 11:15 ivan.hou@msa.hinet.net
0 siblings, 1 reply; 9+ messages in thread
From: ivan.hou@msa.hinet.net @ 2007-06-04 11:15 UTC (permalink / raw)
To: pgsql-general
i set the auto vacuum option to enable. but my database size(hard-disk
space) still increased from 420MB to 440MB in 8 hours. most of the
operations in this database are the "Select" query command, just few
of "Update or Insert".
why it can be increased so strongly?
after i executed the command " vaccumdb -f -z testdb", but the size
just decreased 1 or 2MB...
what's the problem?
how to know which command(select,insert....) takes the hurge loading
to database in this time?
computer environment:
System: Red Hat Enterprise 4
Postgresql Server: 8.0.13
count of client connected : about 100 pc
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Auto Vacuum question?
@ 2007-06-07 15:30 Andrew Sullivan <ajs@crankycanuck.ca>
parent: ivan.hou@msa.hinet.net
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2007-06-07 15:30 UTC (permalink / raw)
To: pgsql-general
On Mon, Jun 04, 2007 at 04:15:12AM -0700, ivan.hou@msa.hinet.net wrote:
> after i executed the command " vaccumdb -f -z testdb", but the size
> just decreased 1 or 2MB...
> what's the problem?
vacuumdb -f does a FULL vacuum, which is blocking and compacts the
tables. If it only compacted 1 or 2 M, then there are two
possibilities:
1. You really do have that much data. You haven't told us
anything about the data, what it looks like, how wide the tables are,
whether you have large objects &c., whether the character set is
multibyte. . .
2. You have at least one long-running transaction that is
perhaps doing nothing, but that is preventing VACUUM from recovering
space. What does ps -auxww | grep postgres (or something equivalant)
show you?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz
^ permalink raw reply [nested|flat] 9+ messages in thread
* auto vacuum question
@ 2023-08-28 20:06 Alan Stange <stange@rentec.com>
0 siblings, 1 reply; 9+ messages in thread
From: Alan Stange @ 2023-08-28 20:06 UTC (permalink / raw)
To: pgsql-general
All,
We recently changed the name of the superuser role in our database, and
then noticed some issues with the autovacuum processes. We are running
15.3, and had a login role, lets call it 'red', which had the superuser
attribute assigned to it. This was the original owner/creator of all
the database objects, and the login role ended up getting used in some
ways for which the superuser attribute was no longer appropriate.
So we elected to make a new role, lets call it 'reddba', which had
tightly controlled entitlements, and which also had the super user
attribute added to it. After a couple of weeks and a lot of testing, we
removed the superuser attribute from the original 'red' account. All
was working as expected.
After a bit we noticed some warnings in the postgresql server log file
of the form 'WARNING: skipping "tablexxx" --- only table of
database owner can vacuum it" where tablexxx is many of the table names
in our system.
We restored the superuser role to the original 'red' login role and
these messages went away.
We are not running any explicit vacuum's. As far as I can tell, these
warnings were comming from the autovacuum processes, and we did also
notice that some query stats became stale which I assume is related to
these same warnings.
So, I'm wondering how we can move the superuser role from role A to B,
so that the autovacuum process will still work? I googled around a
bit, but didn't come up with anything useful for this.
Thank you,
Alan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: auto vacuum question
@ 2023-08-28 20:11 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Alan Stange <stange@rentec.com>
0 siblings, 2 replies; 9+ messages in thread
From: Adrian Klaver @ 2023-08-28 20:11 UTC (permalink / raw)
To: Alan Stange <stange@rentec.com>; pgsql-general
On 8/28/23 13:06, Alan Stange wrote:
> All,
>
> We recently changed the name of the superuser role in our database, and
> then noticed some issues with the autovacuum processes. We are running
> 15.3, and had a login role, lets call it 'red', which had the superuser
> attribute assigned to it. This was the original owner/creator of all
> the database objects, and the login role ended up getting used in some
> ways for which the superuser attribute was no longer appropriate.
>
> So we elected to make a new role, lets call it 'reddba', which had
> tightly controlled entitlements, and which also had the super user
> attribute added to it. After a couple of weeks and a lot of testing, we
> removed the superuser attribute from the original 'red' account. All
> was working as expected.
>
> After a bit we noticed some warnings in the postgresql server log file
> of the form 'WARNING: skipping "tablexxx" --- only table of
> database owner can vacuum it" where tablexxx is many of the table names
> in our system.
Are you sure that is coming from autovacuum?
What are the log lines preceding the WARNING?
What is the complete warning line?
>
> We restored the superuser role to the original 'red' login role and
> these messages went away.
>
> We are not running any explicit vacuum's. As far as I can tell, these
> warnings were comming from the autovacuum processes, and we did also
> notice that some query stats became stale which I assume is related to
> these same warnings.
>
>
> So, I'm wondering how we can move the superuser role from role A to B,
> so that the autovacuum process will still work? I googled around a
> bit, but didn't come up with anything useful for this.
>
> Thank you,
>
> Alan
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERNET] Re: auto vacuum question
@ 2023-08-28 20:19 Alan Stange <stange@rentec.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 9+ messages in thread
From: Alan Stange @ 2023-08-28 20:19 UTC (permalink / raw)
To: pgsql-general
On 8/28/23 16:11, Adrian Klaver wrote:
> On 8/28/23 13:06, Alan Stange wrote:
>> All,
>>
>> We recently changed the name of the superuser role in our database, and
>> then noticed some issues with the autovacuum processes. We are running
>> 15.3, and had a login role, lets call it 'red', which had the superuser
>> attribute assigned to it. This was the original owner/creator of all
>> the database objects, and the login role ended up getting used in some
>> ways for which the superuser attribute was no longer appropriate.
>>
>> So we elected to make a new role, lets call it 'reddba', which had
>> tightly controlled entitlements, and which also had the super user
>> attribute added to it. After a couple of weeks and a lot of testing, we
>> removed the superuser attribute from the original 'red' account. All
>> was working as expected.
>>
>> After a bit we noticed some warnings in the postgresql server log file
>> of the form 'WARNING: skipping "tablexxx" --- only table of
>> database owner can vacuum it" where tablexxx is many of the table names
>> in our system.
> Are you sure that is coming from autovacuum?
>
> What are the log lines preceding the WARNING?
>
> What is the complete warning line?
Thank you for your quick response.
The prefix to the logfile lines are a datetime stamp and then a number,
2062375 in the example that I happen to be looking at now.
I am 99.9% that these log lines came from the autovacuum, as it went on
for days, whereas a regular vacuum on our multi-TB data base takes a
couple of hours. We don't have any explicit vacuum or analyze jobs
running or scheduled.
Alan
>
>> We restored the superuser role to the original 'red' login role and
>> these messages went away.
>>
>> We are not running any explicit vacuum's. As far as I can tell, these
>> warnings were comming from the autovacuum processes, and we did also
>> notice that some query stats became stale which I assume is related to
>> these same warnings.
>>
>>
>> So, I'm wondering how we can move the superuser role from role A to B,
>> so that the autovacuum process will still work? I googled around a
>> bit, but didn't come up with anything useful for this.
>>
>> Thank you,
>>
>> Alan
>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERNET] Re: auto vacuum question
@ 2023-08-28 20:48 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Alan Stange <stange@rentec.com>
0 siblings, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2023-08-28 20:48 UTC (permalink / raw)
To: Alan Stange <stange@rentec.com>; pgsql-general
On 8/28/23 13:19, Alan Stange wrote:
> On 8/28/23 16:11, Adrian Klaver wrote:
>> On 8/28/23 13:06, Alan Stange wrote:
>>> All,
>>>
>> Are you sure that is coming from autovacuum?
>>
>> What are the log lines preceding the WARNING?
>>
>> What is the complete warning line?
> Thank you for your quick response.
>
> The prefix to the logfile lines are a datetime stamp and then a number,
> 2062375 in the example that I happen to be looking at now.
>
> I am 99.9% that these log lines came from the autovacuum, as it went on
> for days, whereas a regular vacuum on our multi-TB data base takes a
> couple of hours. We don't have any explicit vacuum or analyze jobs
> running or scheduled.
I went back to the source for a refresher:
https://doxygen.postgresql.org/autovacuum_8c_source.html
The autovacuum system is structured in two different kinds of processes:
the autovacuum launcher and the autovacuum worker. The launcher is an
always-running process, started by the postmaster when the autovacuum
GUC parameter is set. The launcher schedules autovacuum workers to be
started when appropriate. The workers are the processes which execute
the actual vacuuming; they connect to a database as determined in the
launcher, and once connected they examine the catalogs to select the
tables to vacuum.
...
Connect to the selected database, specifying no particular user
I read that as the autovacuumn process runs directly from the backend
and not as any user. Therefore it should not be running into a user
permissions problem.
1) Is this the community version of Postgres?
2) Did you do any other changes when you altered the superuser?
3) Did the log lines before the Warning have anything useful?
4) Are you sure there is not some admin app running that is doing VACUUMs?
>
> Alan
>
>
>>
>>> We restored the superuser role to the original 'red' login role and
>>> these messages went away.
>>>
>>> We are not running any explicit vacuum's. As far as I can tell, these
>>> warnings were comming from the autovacuum processes, and we did also
>>> notice that some query stats became stale which I assume is related to
>>> these same warnings.
>>>
>>>
>>> So, I'm wondering how we can move the superuser role from role A to B,
>>> so that the autovacuum process will still work? I googled around a
>>> bit, but didn't come up with anything useful for this.
>>>
>>> Thank you,
>>>
>>> Alan
>>>
>>>
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: auto vacuum question
@ 2023-08-28 22:35 Jerry Sievers <gsievers19@comcast.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 9+ messages in thread
From: Jerry Sievers @ 2023-08-28 22:35 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Alan Stange <stange@rentec.com>; pgsql-general
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/28/23 13:06, Alan Stange wrote:
>
>> All,
>> We recently changed the name of the superuser role in our database,
My take on this, is that the *postmaster* user is perhaps the one that
the OP cut privileges on, and thus the launcher is (now) spawning
workers with less than full SU perms.
Just a guess.
FWIW
>> and
>> then noticed some issues with the autovacuum processes. We are running
>> 15.3, and had a login role, lets call it 'red', which had the superuser
>> attribute assigned to it. This was the original owner/creator of all
>> the database objects, and the login role ended up getting used in some
>> ways for which the superuser attribute was no longer appropriate.
>> So we elected to make a new role, lets call it 'reddba', which had
>> tightly controlled entitlements, and which also had the super user
>> attribute added to it. After a couple of weeks and a lot of testing, we
>> removed the superuser attribute from the original 'red' account. All
>> was working as expected.
>> After a bit we noticed some warnings in the postgresql server log
>> file
>> of the form 'WARNING: skipping "tablexxx" --- only table of
>> database owner can vacuum it" where tablexxx is many of the table names
>> in our system.
>
> Are you sure that is coming from autovacuum?
>
> What are the log lines preceding the WARNING?
>
> What is the complete warning line?
>
>> We restored the superuser role to the original 'red' login role and
>> these messages went away.
>> We are not running any explicit vacuum's. As far as I can tell,
>> these
>> warnings were comming from the autovacuum processes, and we did also
>> notice that some query stats became stale which I assume is related to
>> these same warnings.
>> So, I'm wondering how we can move the superuser role from role A to
>> B,
>> so that the autovacuum process will still work? I googled around a
>> bit, but didn't come up with anything useful for this.
>> Thank you,
>> Alan
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERNET] Re: auto vacuum question
@ 2023-08-29 20:09 Alan Stange <stange@rentec.com>
parent: Jerry Sievers <gsievers19@comcast.net>
0 siblings, 1 reply; 9+ messages in thread
From: Alan Stange @ 2023-08-29 20:09 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
On 8/28/23 18:35, Jerry Sievers wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>
>> On 8/28/23 13:06, Alan Stange wrote:
>>
>>> All,
>>> We recently changed the name of the superuser role in our database,
> My take on this, is that the *postmaster* user is perhaps the one that
> the OP cut privileges on, and thus the launcher is (now) spawning
> workers with less than full SU perms.
>
> Just a guess.
>
> FWIW
Hypothetically speaking, for a friend, how would one change the
superuser that was assigned in the initdb command? Your guess is
good, and it's clear now that the running database isn't accommodating
our removal of the superuser attribute from the original default role
created in the initdb command.
Thank you,
Alan
>
>>> and
>>> then noticed some issues with the autovacuum processes. We are running
>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>> attribute assigned to it. This was the original owner/creator of all
>>> the database objects, and the login role ended up getting used in some
>>> ways for which the superuser attribute was no longer appropriate.
>>> So we elected to make a new role, lets call it 'reddba', which had
>>> tightly controlled entitlements, and which also had the super user
>>> attribute added to it. After a couple of weeks and a lot of testing, we
>>> removed the superuser attribute from the original 'red' account. All
>>> was working as expected.
>>> After a bit we noticed some warnings in the postgresql server log
>>> file
>>> of the form 'WARNING: skipping "tablexxx" --- only table of
>>> database owner can vacuum it" where tablexxx is many of the table names
>>> in our system.
>> Are you sure that is coming from autovacuum?
>>
>> What are the log lines preceding the WARNING?
>>
>> What is the complete warning line?
>>
>>> We restored the superuser role to the original 'red' login role and
>>> these messages went away.
>>> We are not running any explicit vacuum's. As far as I can tell,
>>> these
>>> warnings were comming from the autovacuum processes, and we did also
>>> notice that some query stats became stale which I assume is related to
>>> these same warnings.
>>> So, I'm wondering how we can move the superuser role from role A to
>>> B,
>>> so that the autovacuum process will still work? I googled around a
>>> bit, but didn't come up with anything useful for this.
>>> Thank you,
>>> Alan
>>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERNET] Re: auto vacuum question
@ 2023-08-30 03:47 Jerry Sievers <gsievers19@comcast.net>
parent: Alan Stange <stange@rentec.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jerry Sievers @ 2023-08-30 03:47 UTC (permalink / raw)
To: Alan Stange <stange@rentec.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Alan Stange <stange@rentec.com> writes:
> On 8/28/23 18:35, Jerry Sievers wrote:
>
>> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>
>>> On 8/28/23 13:06, Alan Stange wrote:
>>>
>>>> All,
>>>> We recently changed the name of the superuser role in our database,
>> My take on this, is that the *postmaster* user is perhaps the one that
>> the OP cut privileges on, and thus the launcher is (now) spawning
>> workers with less than full SU perms.
>>
>> Just a guess.
>>
>> FWIW
>
> Hypothetically speaking, for a friend, how would one change the
> superuser that was assigned in the initdb command? Your guess is
> good, and it's clear now that the running database isn't accommodating
> our removal of the superuser attribute from the original default role
> created in the initdb command.
Most sites I"ve worked at do *not* use the initdb user for anything but
administrator usage and create one or more regular users/roles with
appropriate object ownership to individual DBs therein.
NOt uncommonly you might wish to have an *owner* role for deployment
(DDL) activities and a lesser role for application use
Doing so help minimize blast radius in case the application is
compromised.
There are a number of finer details in all of this but essentially,
leave alone the initdb/postmaster user except for DBA like tasks.
HTH
>
> Thank you,
>
> Alan
>
>
>>
>>>> and
>>>> then noticed some issues with the autovacuum processes. We are running
>>>> 15.3, and had a login role, lets call it 'red', which had the superuser
>>>> attribute assigned to it. This was the original owner/creator of all
>>>> the database objects, and the login role ended up getting used in some
>>>> ways for which the superuser attribute was no longer appropriate.
>>>> So we elected to make a new role, lets call it 'reddba', which had
>>>> tightly controlled entitlements, and which also had the super user
>>>> attribute added to it. After a couple of weeks and a lot of testing, we
>>>> removed the superuser attribute from the original 'red' account. All
>>>> was working as expected.
>>>> After a bit we noticed some warnings in the postgresql server log
>>>> file
>>>> of the form 'WARNING: skipping "tablexxx" --- only table of
>>>> database owner can vacuum it" where tablexxx is many of the table names
>>>> in our system.
>>> Are you sure that is coming from autovacuum?
>>>
>>> What are the log lines preceding the WARNING?
>>>
>>> What is the complete warning line?
>>>
>>>> We restored the superuser role to the original 'red' login role and
>>>> these messages went away.
>>>> We are not running any explicit vacuum's. As far as I can tell,
>>>> these
>>>> warnings were comming from the autovacuum processes, and we did also
>>>> notice that some query stats became stale which I assume is related to
>>>> these same warnings.
>>>> So, I'm wondering how we can move the superuser role from role A to
>>>> B,
>>>> so that the autovacuum process will still work? I googled around a
>>>> bit, but didn't come up with anything useful for this.
>>>> Thank you,
>>>> Alan
>>>>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-08-30 03:47 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-04 11:15 Auto Vacuum question? ivan.hou@msa.hinet.net
2007-06-07 15:30 ` Andrew Sullivan <ajs@crankycanuck.ca>
2023-08-28 20:06 auto vacuum question Alan Stange <stange@rentec.com>
2023-08-28 20:11 ` Re: auto vacuum question Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-28 20:19 ` Re: [INTERNET] Re: auto vacuum question Alan Stange <stange@rentec.com>
2023-08-28 20:48 ` Re: [INTERNET] Re: auto vacuum question Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-28 22:35 ` Re: auto vacuum question Jerry Sievers <gsievers19@comcast.net>
2023-08-29 20:09 ` Re: [INTERNET] Re: auto vacuum question Alan Stange <stange@rentec.com>
2023-08-30 03:47 ` Re: [INTERNET] Re: auto vacuum question Jerry Sievers <gsievers19@comcast.net>
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