pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNewbie needs help with space issue
6+ messages / 5 participants
[nested] [flat]
* Newbie needs help with space issue
@ 2020-04-21 23:40 Jesse Josserand <jesse.josserand@gmail.com>
2020-04-21 23:49 ` Re: Newbie needs help with space issue John Scalia <jayknowsunix@gmail.com>
2020-04-22 03:51 ` Re: Newbie needs help with space issue raf <raf@raf.org>
0 siblings, 2 replies; 6+ messages in thread
From: Jesse Josserand @ 2020-04-21 23:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I apologize in advance for bothering you, but I've not been able to get
answers from the community by searches I've done, nor have I figured out
how to post a question there.
I'm working for a gov't entity here in Mississippi and while I have
extensive Oracle and MySQL experience, as well as Linux admin experience
(which is what I was hired for), I've been given PostgreSQL DBA
responsibilities since the last DBA quit abruptly (probably due to poor
architecture and documentation here -- another thing I'm resolving).
I'm having serious space issues in a production environment and psql
commands and queries are not helping me isolate it nor determine the root
cause... possibly due to my naivete.
Would you be so kind as to give me some insight as to how to best determine
what is growing to fast and why? I have a 20G disk that recently was
increased to 40G and am now in less than 3 months almost out of space there
again.
Yours truly,
Jesse Josserand, CTO
IT Web Services, LLC <https://itwebservicesllc.com/; and
Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/;
Direct Phone: 760-566-7866
Business Phone/Fax: 855-236-5550
Owner, Sr. Consultant, Webmaster
Josserand Ace ConsultinG™ (JacG™) <https://josserandace.consulting/;
800-698-1030,
SalesRepsNow.Com <https://salesrepsnow.com/; (888-272-8885), and
World Profit Club Network Marketing Alliance
<https://worldprofitclubassociates.com/; (WPCNMA)® (800-980-8233).
All Divisions of ITWSLLC™
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Newbie needs help with space issue
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
@ 2020-04-21 23:49 ` John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: John Scalia @ 2020-04-21 23:49 UTC (permalink / raw)
To: Jesse Josserand <jesse.josserand@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
It’s rather difficult to determine what your problem might be without some details about your environment, but if you database is growing so rapidly, there’s a possibility that the former DBA might have turned off autovacuuming and your’re now experiencing db bloat. Find your postgresql.conf file and look in it for the word autovacuum and see if it is set to off. If so, turn it back on. Your database will not reclaim the disk space, but if you can spare some downtime, a “vacuum full” can do wonders. Be advised that vacuum full locks tables for it’s exclusive use, so you do not want to use it during regular hours.
But that’s about all I can suggest without more details.
—
Jay
Sent from my iPad
> On Apr 21, 2020, at 7:40 PM, Jesse Josserand <jesse.josserand@gmail.com> wrote:
>
>
> I apologize in advance for bothering you, but I've not been able to get answers from the community by searches I've done, nor have I figured out how to post a question there.
>
> I'm working for a gov't entity here in Mississippi and while I have extensive Oracle and MySQL experience, as well as Linux admin experience (which is what I was hired for), I've been given PostgreSQL DBA responsibilities since the last DBA quit abruptly (probably due to poor architecture and documentation here -- another thing I'm resolving).
>
> I'm having serious space issues in a production environment and psql commands and queries are not helping me isolate it nor determine the root cause... possibly due to my naivete.
>
> Would you be so kind as to give me some insight as to how to best determine what is growing to fast and why? I have a 20G disk that recently was increased to 40G and am now in less than 3 months almost out of space there again.
>
> Yours truly,
> Jesse Josserand, CTO
> IT Web Services, LLC and
> Ace Consulting Solutions, Inc.
> Direct Phone: 760-566-7866
> Business Phone/Fax: 855-236-5550
> Owner, Sr. Consultant, Webmaster
> Josserand Ace ConsultinG™ (JacG™) 800-698-1030,
> SalesRepsNow.Com (888-272-8885), and
> World Profit Club Network Marketing Alliance (WPCNMA)® (800-980-8233).
> All Divisions of ITWSLLC™
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Newbie needs help with space issue
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
@ 2020-04-22 03:51 ` raf <raf@raf.org>
2020-04-22 04:25 ` Re: Newbie needs help with space issue Priancka Chatz <pc9926@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: raf @ 2020-04-22 03:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Jesse Josserand wrote:
> I apologize in advance for bothering you, but I've not been able to get
> answers from the community by searches I've done, nor have I figured out
> how to post a question there.
>
> I'm working for a gov't entity here in Mississippi and while I have
> extensive Oracle and MySQL experience, as well as Linux admin experience
> (which is what I was hired for), I've been given PostgreSQL DBA
> responsibilities since the last DBA quit abruptly (probably due to poor
> architecture and documentation here -- another thing I'm resolving).
>
> I'm having serious space issues in a production environment and psql
> commands and queries are not helping me isolate it nor determine the root
> cause... possibly due to my naivete.
>
> Would you be so kind as to give me some insight as to how to best determine
> what is growing to fast and why? I have a 20G disk that recently was
> increased to 40G and am now in less than 3 months almost out of space there
> again.
>
> Yours truly,
> Jesse Josserand, CTO
> IT Web Services, LLC <https://itwebservicesllc.com/; and
> Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/;
Hi,
Here's a query to list individual table sizes:
select
t.tablename as "object",
pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as "data",
pg_size_pretty(pg_total_relation_size(cast(t.tablename as text))) as "total"
from
pg_tables t
where
t.schemaname = 'public'
order by
pg_total_relation_size(cast(t.tablename as text)) desc
I think the last column includes index sizes.
Running this repeatedly over time should show what's growing.
cheers,
raf
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Newbie needs help with space issue
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
2020-04-22 03:51 ` Re: Newbie needs help with space issue raf <raf@raf.org>
@ 2020-04-22 04:25 ` Priancka Chatz <pc9926@gmail.com>
2020-04-22 07:23 ` Re: Newbie needs help with space issue androxkentaki . <androxkentaki@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Priancka Chatz @ 2020-04-22 04:25 UTC (permalink / raw)
To: raf <raf@raf.org>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
Also you might want to check the logging settings and log file sizes if
logging is enabled. Sometimes due to some issues in database, the number of
log file entries can be a lot leading to large log files.
Regards,
Priyanka
On Wed, 22 Apr 2020 at 9:21 AM, raf <raf@raf.org> wrote:
> Jesse Josserand wrote:
>
> > I apologize in advance for bothering you, but I've not been able to get
> > answers from the community by searches I've done, nor have I figured out
> > how to post a question there.
> >
> > I'm working for a gov't entity here in Mississippi and while I have
> > extensive Oracle and MySQL experience, as well as Linux admin experience
> > (which is what I was hired for), I've been given PostgreSQL DBA
> > responsibilities since the last DBA quit abruptly (probably due to poor
> > architecture and documentation here -- another thing I'm resolving).
> >
> > I'm having serious space issues in a production environment and psql
> > commands and queries are not helping me isolate it nor determine the root
> > cause... possibly due to my naivete.
> >
> > Would you be so kind as to give me some insight as to how to best
> determine
> > what is growing to fast and why? I have a 20G disk that recently was
> > increased to 40G and am now in less than 3 months almost out of space
> there
> > again.
> >
> > Yours truly,
> > Jesse Josserand, CTO
> > IT Web Services, LLC <https://itwebservicesllc.com/; and
> > Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/;
>
> Hi,
>
> Here's a query to list individual table sizes:
>
> select
> t.tablename as "object",
> pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as
> "data",
> pg_size_pretty(pg_total_relation_size(cast(t.tablename as text)))
> as "total"
> from
> pg_tables t
> where
> t.schemaname = 'public'
> order by
> pg_total_relation_size(cast(t.tablename as text)) desc
>
> I think the last column includes index sizes.
>
> Running this repeatedly over time should show what's growing.
>
> cheers,
> raf
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Newbie needs help with space issue
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
2020-04-22 03:51 ` Re: Newbie needs help with space issue raf <raf@raf.org>
2020-04-22 04:25 ` Re: Newbie needs help with space issue Priancka Chatz <pc9926@gmail.com>
@ 2020-04-22 07:23 ` androxkentaki . <androxkentaki@gmail.com>
2020-04-22 07:52 ` Re: Newbie needs help with space issue Priancka Chatz <pc9926@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: androxkentaki . @ 2020-04-22 07:23 UTC (permalink / raw)
To: Priancka Chatz <pc9926@gmail.com>; +Cc: raf <raf@raf.org>; pgsql-admin@lists.postgresql.org
this sql for table size ends with error that some of relation was not found.
So this is better one:
select
t.schemaname ,
t.tablename as object
,pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
,pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
from
pg_tables t
where 1=1
--and t.schemaname = 'public'
--and t.schemaname <> 'public'
order by pg_total_relation_size(schemaname || '.' || tablename) desc;
śr., 22 kwi 2020 o 06:25 Priancka Chatz <pc9926@gmail.com> napisał(a):
> Hi,
>
> Also you might want to check the logging settings and log file sizes if
> logging is enabled. Sometimes due to some issues in database, the number of
> log file entries can be a lot leading to large log files.
>
> Regards,
> Priyanka
>
> On Wed, 22 Apr 2020 at 9:21 AM, raf <raf@raf.org> wrote:
>
>> Jesse Josserand wrote:
>>
>> > I apologize in advance for bothering you, but I've not been able to get
>> > answers from the community by searches I've done, nor have I figured out
>> > how to post a question there.
>> >
>> > I'm working for a gov't entity here in Mississippi and while I have
>> > extensive Oracle and MySQL experience, as well as Linux admin experience
>> > (which is what I was hired for), I've been given PostgreSQL DBA
>> > responsibilities since the last DBA quit abruptly (probably due to poor
>> > architecture and documentation here -- another thing I'm resolving).
>> >
>> > I'm having serious space issues in a production environment and psql
>> > commands and queries are not helping me isolate it nor determine the
>> root
>> > cause... possibly due to my naivete.
>> >
>> > Would you be so kind as to give me some insight as to how to best
>> determine
>> > what is growing to fast and why? I have a 20G disk that recently was
>> > increased to 40G and am now in less than 3 months almost out of space
>> there
>> > again.
>> >
>> > Yours truly,
>> > Jesse Josserand, CTO
>> > IT Web Services, LLC <https://itwebservicesllc.com/; and
>> > Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/;
>>
>> Hi,
>>
>> Here's a query to list individual table sizes:
>>
>> select
>> t.tablename as "object",
>> pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as
>> "data",
>> pg_size_pretty(pg_total_relation_size(cast(t.tablename as text)))
>> as "total"
>> from
>> pg_tables t
>> where
>> t.schemaname = 'public'
>> order by
>> pg_total_relation_size(cast(t.tablename as text)) desc
>>
>> I think the last column includes index sizes.
>>
>> Running this repeatedly over time should show what's growing.
>>
>> cheers,
>> raf
>>
>>
>>
>>
--
Z wyrazami szacunku
Andrzej Gerasimuk
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Newbie needs help with space issue
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
2020-04-22 03:51 ` Re: Newbie needs help with space issue raf <raf@raf.org>
2020-04-22 04:25 ` Re: Newbie needs help with space issue Priancka Chatz <pc9926@gmail.com>
2020-04-22 07:23 ` Re: Newbie needs help with space issue androxkentaki . <androxkentaki@gmail.com>
@ 2020-04-22 07:52 ` Priancka Chatz <pc9926@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Priancka Chatz @ 2020-04-22 07:52 UTC (permalink / raw)
To: androxkentaki . <androxkentaki@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, raf <raf@raf.org>
If you have the superuser login, you can get the individual table sizes by
running
\dt+ *.*
On Wed, 22 Apr 2020 at 12:53 PM, androxkentaki . <androxkentaki@gmail.com>
wrote:
> this sql for table size ends with error that some of relation was not
> found.
> So this is better one:
>
>
> select
> t.schemaname ,
> t.tablename as object
> ,pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
> ,pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
>
> from
> pg_tables t
> where 1=1
> --and t.schemaname = 'public'
> --and t.schemaname <> 'public'
> order by pg_total_relation_size(schemaname || '.' || tablename) desc;
>
> śr., 22 kwi 2020 o 06:25 Priancka Chatz <pc9926@gmail.com> napisał(a):
>
>> Hi,
>>
>> Also you might want to check the logging settings and log file sizes if
>> logging is enabled. Sometimes due to some issues in database, the number of
>> log file entries can be a lot leading to large log files.
>>
>> Regards,
>> Priyanka
>>
>> On Wed, 22 Apr 2020 at 9:21 AM, raf <raf@raf.org> wrote:
>>
>>> Jesse Josserand wrote:
>>>
>>> > I apologize in advance for bothering you, but I've not been able to get
>>> > answers from the community by searches I've done, nor have I figured
>>> out
>>> > how to post a question there.
>>> >
>>> > I'm working for a gov't entity here in Mississippi and while I have
>>> > extensive Oracle and MySQL experience, as well as Linux admin
>>> experience
>>> > (which is what I was hired for), I've been given PostgreSQL DBA
>>> > responsibilities since the last DBA quit abruptly (probably due to poor
>>> > architecture and documentation here -- another thing I'm resolving).
>>> >
>>> > I'm having serious space issues in a production environment and psql
>>> > commands and queries are not helping me isolate it nor determine the
>>> root
>>> > cause... possibly due to my naivete.
>>> >
>>> > Would you be so kind as to give me some insight as to how to best
>>> determine
>>> > what is growing to fast and why? I have a 20G disk that recently was
>>> > increased to 40G and am now in less than 3 months almost out of space
>>> there
>>> > again.
>>> >
>>> > Yours truly,
>>> > Jesse Josserand, CTO
>>> > IT Web Services, LLC <https://itwebservicesllc.com/; and
>>> > Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/
>>> >
>>>
>>> Hi,
>>>
>>> Here's a query to list individual table sizes:
>>>
>>> select
>>> t.tablename as "object",
>>> pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as
>>> "data",
>>> pg_size_pretty(pg_total_relation_size(cast(t.tablename as
>>> text))) as "total"
>>> from
>>> pg_tables t
>>> where
>>> t.schemaname = 'public'
>>> order by
>>> pg_total_relation_size(cast(t.tablename as text)) desc
>>>
>>> I think the last column includes index sizes.
>>>
>>> Running this repeatedly over time should show what's growing.
>>>
>>> cheers,
>>> raf
>>>
>>>
>>>
>>>
>
> --
> Z wyrazami szacunku
> Andrzej Gerasimuk
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-04-22 07:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-21 23:40 Newbie needs help with space issue Jesse Josserand <jesse.josserand@gmail.com>
2020-04-21 23:49 ` John Scalia <jayknowsunix@gmail.com>
2020-04-22 03:51 ` raf <raf@raf.org>
2020-04-22 04:25 ` Priancka Chatz <pc9926@gmail.com>
2020-04-22 07:23 ` androxkentaki . <androxkentaki@gmail.com>
2020-04-22 07:52 ` Priancka Chatz <pc9926@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