Re: Size of the table is growing abnormally in my database.
There's a handful of hidden columns like Xmin and Xmax per row that you're not accounting for, header info per page, reserve space, free space... The physical size on disk is reasonable. Otherwise you can reduce the number of rows by cleaning up and moving out old data, reduce the width of each row by getting rid of any unused columns or switching to narrower data types, or drop unused indexes. If none of that works for you then you're going to have to adjust your disk budget. On Sun, Aug 26, 2018, 12:37 AM Raghavendra Rao J S V < raghavendra...@gmail.com> wrote: > Thank you very much for your prompt response. > > Please guide me below things. > > How to check rows got corrupted? > > How to check table got corrupted? > > How to check which row is occupied more space in the table? > > Is this expected? > > [image: image.png] > > On Sun, 26 Aug 2018 at 09:46, Adrian Klaver > wrote: > >> On 08/25/2018 08:36 PM, Raghavendra Rao J S V wrote: >> > Hi All, >> > >> > One of our database size is 50gb. Out of it one of the table has >> > 149444622 records. Size of that table is 14GB and its indexes size is >> 16GB. >> > Total size of the table and its indexes are 30GB. I have perfomred the >> > below steps on that table. >> > >> > reindex table table_name; >> > >> > vacuum full verbose analyze on table_name; >> > >> > But still the size of the table and its indexes size are not reduced. >> > Please guid me. How to proceed further. >> >> Rough approximation: >> >> 14,000,000,000 / 150,000,000 = 93 bytes/record. >> >> I am not seeing an issue. If you want to reduce the size of the table >> remove rows. >> >> >> > >> > Structure of the table as below. >> > >> > Column | Type | Modifiers | Storage | >> > Stats target | Description >> > >> -+--+---+-+--+- >> > col1| bigint | | plain | | >> > col2 | double precision | | plain | | >> > col3| double precision | | plain | | >> > col4| double precision | | plain | | >> > col5| double precision | | plain | | >> > col6date| date | | plain | | >> > tkey | integer | | plain | | >> > cid | integer | | plain | | >> > rtypeid | integer | | plain | | >> > rid | integer | | plain | | >> > ckey | bigint | | plain | | >> > Indexes: >> > "idx_tab_cid" btree (cid) >> > "idx_tab_ckey" btree (ckey) >> > "idx_tab_col6date" btree (col6date) >> > "idx_tab_rid" btree (rid) >> > "idx_tab_rtype_id" btree (rtypid) >> > "idx_tab_tkey" btree (tkey) >> > >> > >> > -- >> > Regards, >> > Raghavendra Rao J S V >> > Mobile- 8861161425 >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > > > -- > Regards, > Raghavendra Rao J S V > Mobile- 8861161425 >
Re: pg_sample
sudo yum install perl-CPAN Also do a "yum search perl-" and you should find most, if not all what you need natively packaged for your particular Linux distro. You're better off just sticking with the pre-packaged perl modules unless you specifically need something special. You'll find more modules if you also enable the EPEL yum repo by setting enabled=1 in the first section of /etc/yum.repos.d/epel.conf and rerunning that yum search command. On Sun, Aug 26, 2018, 2:20 PM Naveen Dabas wrote: > Sir i'm getting error in both commands > > [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install Bundle::DBI' > Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). > BEGIN failed--compilation aborted. > [root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install DBD::Pg' > Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 > /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl > /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .). > BEGIN failed--compilation aborted. > > > > operating system =CentOS Linux release 7.5.1804 (Core) > > thanks > > > On Fri, Aug 24, 2018 at 6:16 PM, Abhinav Mehta > wrote: > >> Solution, execute this on your linux terminal - >> >> $ perl -MCPAN -e 'install Bundle::DBI' >> $ perl -MCPAN -e 'install DBD::Pg' >> >> > On 24-Aug-2018, at 6:13 PM, Ravi Krishna wrote: >> > >> >> >> >> sir have taken pg_sample >> >> Now i want to run pg_sample with credential but i'm getting this error >> >> >> >> Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 >> /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl >> /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at >> ./pg_sample line 192. >> >> BEGIN failed--compilation aborted at ./pg_sample line 192. >> > >> > As is clear from the message, you need to install Perl DBI/DBD first. >> > >> > >> >> > > > -- > -- > With Regards > Naveen Dabas > Ph. 9017298370 > > > *Important Disclaimer:* Information contained in this email is for the > recipient primarily addressed to. If you are not the primary recipient or > are not supposed to receive this email, you are advised to kindly delete > the email or the thread and notify of the error. The logo is a registered > and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not > use it without authorization.
Re: Out of Memory
My first two guesses are ulimit or numa. numactl --hardware will show your how many nodes your box has and if you're exhausting any of them. On Thu, Sep 20, 2018, 6:11 PM greigwise wrote: > Hello, I'm running postgres 9.6.10 on Centos 7. Seeing the occasional out > of memory error trying to run a query. In the logs I see something like > this: > > Grand total: 462104832 bytes in 795 blocks; 142439136 free (819860 chunks); > 319665696 used > 2018-09-20 18:08:01 UTC 5ba3e1a2.7a8a dbname ERROR: out of memory > 2018-09-20 18:08:01 UTC 5ba3e1a2.7a8a dbname DETAIL: Failed on > request > of size 2016. > > If I have 142439136 free, then why am I failing on a request of size 2016? > > Am I misunderstanding here? > > Thanks, > Greig Wise > > > > -- > Sent from: > http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > >
