[PERFORM] Large Table With Only a Few Rows

[Nik]

I have a table that has only a few records in it at the time, and they
get deleted every few seconds and new records are inserted. Table never
has more than 5-10 records in it.

However, I noticed a deteriorating performance in deletes and inserts
on it. So I performed vacuum analyze on it three times (twice in a row,
and once two days later). In the statistics it says that the table size
is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even
though the table has only 5-10 rows in it it. I was wondering how can I
reclaim all this space and improve the performance?

Here are the outputs of my vacuum sessions:

--02/24/06 4:30PM--
INFO:  vacuuming "incidents.php_sessions"
INFO:  index "php_sessions_pkey" now contains 16 row versions in 17151
pages
DETAIL:  878643 index row versions were removed.
16967 index pages have been deleted, 8597 are currently reusable.
CPU 3.35s/3.67u sec elapsed 25.96 sec.
INFO:  "php_sessions": removed 878689 row versions in 107418 pages
DETAIL:  CPU 17.53s/11.23u sec elapsed 88.22 sec.
INFO:  "php_sessions": found 878689 removable, 14 nonremovable row
versions in 110472 pages
DETAIL:  10 dead row versions cannot be removed yet.
There were 87817 unused item pointers.
0 pages are entirely empty.
CPU 23.87s/16.57u sec elapsed 124.54 sec.
INFO:  vacuuming "pg_toast.pg_toast_47206"
INFO:  index "pg_toast_47206_index" now contains 550 row versions in
11927 pages
DETAIL:  1415130 index row versions were removed.
11901 index pages have been deleted, 6522 are currently reusable.
CPU 1.45s/2.15u sec elapsed 20.62 sec.
INFO:  "pg_toast_47206": removed 1415130 row versions in 353787 pages
DETAIL:  CPU 56.92s/32.12u sec elapsed 592.18 sec.
INFO:  "pg_toast_47206": found 1415130 removable, 114 nonremovable row
versions in 353815 pages
DETAIL:  114 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 87.42s/43.06u sec elapsed 939.62 sec.
INFO:  analyzing "incidents.php_sessions"
INFO:  "php_sessions": scanned 3000 of 110479 pages, containing 0 live
rows and 16 dead rows; 0 rows in sample, 0 estimated total rows

Total query runtime: 1064249 ms.


--02/24/06 5:00PM--
INFO:  vacuuming "incidents.php_sessions"
INFO:  index "php_sessions_pkey" now contains 4 row versions in 17151
pages
DETAIL:  783 index row versions were removed.
17137 index pages have been deleted, 17129 are currently reusable.
CPU 0.31s/0.20u sec elapsed 13.89 sec.
INFO:  "php_sessions": removed 784 row versions in 87 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  "php_sessions": found 784 removable, 3 nonremovable row versions
in 110479 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 966202 unused item pointers.
0 pages are entirely empty.
CPU 1.21s/0.79u sec elapsed 15.82 sec.
INFO:  vacuuming "pg_toast.pg_toast_47206"
INFO:  index "pg_toast_47206_index" now contains 310 row versions in
11927 pages
DETAIL:  1830 index row versions were removed.
11922 index pages have been deleted, 11915 are currently reusable.
CPU 0.18s/0.12u sec elapsed 11.39 sec.
INFO:  "pg_toast_47206": removed 1830 row versions in 465 pages
DETAIL:  CPU 0.12s/0.04u sec elapsed 0.25 sec.
INFO:  "pg_toast_47206": found 1830 removable, 30 nonremovable row
versions in 354141 pages
DETAIL:  20 dead row versions cannot be removed yet.
There were 1414680 unused item pointers.
0 pages are entirely empty.
CPU 16.07s/4.46u sec elapsed 200.87 sec.
INFO:  "pg_toast_47206": truncated 354141 to 312 pages
DETAIL:  CPU 8.32s/2.57u sec elapsed 699.85 sec.
INFO:  analyzing "incidents.php_sessions"
INFO:  "php_sessions": scanned 3000 of 110479 pages, containing 0 live
rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

Total query runtime: 924084 ms.


--02/26/06 9:30AM--
INFO:  vacuuming "incidents.php_sessions"
INFO:  index "php_sessions_pkey" now contains 1 row versions in 17151
pages
DETAIL:  46336 index row versions were removed.
17140 index pages have been deleted, 16709 are currently reusable.
CPU 0.20s/0.18u sec elapsed 13.96 sec.
INFO:  "php_sessions": removed 46343 row versions in 4492 pages
DETAIL:  CPU 0.25s/0.31u sec elapsed 2.42 sec.
INFO:  "php_sessions": found 46343 removable, 1 nonremovable row
versions in 110479 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 948998 unused item pointers.
0 pages are entirely empty.
CPU 1.07s/0.90u sec elapsed 17.45 sec.
INFO:  vacuuming "pg_toast.pg_toast_47206"
INFO:  index "pg_toast_47206_index" now contains 50 row versions in
11927 pages
DETAIL:  125250 index row versions were removed.
11923 index pages have been deleted, 11446 are currently reusable.
CPU 0.25s/0.12u sec elapsed 11.79 sec.
INFO:  "pg_toast_47206": removed 125250 row versions in 31316 pages
DETAIL:  CPU 2.35s/1.79u sec elapsed 15.68 sec.
INFO:  "pg_toast_47206": found 125250 removable, 32 nonremovable row

[PERFORM] Find how much memory is postgres using

[Nik Tek]

Hi,

Could someone tell m how to measure postgres memory usage.
Is there a pg_* view to measure?

Thank you
NikT

Re: [PERFORM] Find how much memory is postgres using

[Nik Tek]

--For MSSQL
select
(select cntr_value
from sys.dm_os_performance_counters
where object_name like '%Memory Manager%' and counter_name like
'Maximum Workspace Memory (KB)%') as Maximum_Workspace_Memory_KB,
(select cntr_value
from sys.dm_os_performance_counters
where object_name like '%Memory Manager%' and counter_name like
'Target Server Memory (KB)%') as Target_Server_Memory_KB,
(select cntr_value
from sys.dm_os_performance_counters
where object_name like '%Memory Manager%' and counter_name like
'Maximum Workspace Memory (KB)%') * 100.0
 /
(select cntr_value
from sys.dm_os_performance_counters
where object_name like '%Memory Manager%' and counter_name like
'Target Server Memory (KB)%')  as Ratio

-- Oracle
SELECT sum(bytes)/1024/1024
FROM v$sgastat;

Thank you
Nik



On Mon, Apr 8, 2013 at 3:18 AM, hubert depesz lubaczewski  wrote:

> On Sun, Apr 07, 2013 at 09:27:42PM -0700, Nik Tek wrote:
> > Thank you Depesz!
> > But I have a naive question, why isn't a straight forword approach for
> > postgres, unlike Oracle or MSSQL?
>
> No idea. And how do you get memory usage in Oracle or MSSQL?
>
> Best regards,
>
> depesz
>
>

Re: [PERFORM] Find how much memory is postgres using

[Nik Tek]

Hi Depesz,

--Here is better one for Oracle by sga/pga.
SELECT DECODE (GROUPING (nm), 1, 'total', nm) nm,
   ROUND (SUM (val / 1024 / 1024)) MB
  FROM (SELECT 'sga' nm, SUM (VALUE) val FROM v$sga
UNION ALL
SELECT 'pga', SUM (VALUE)
  FROM v$sysstat
WHERE name = 'session pga memory')
GROUP BY ROLLUP (nm);

Sure, I will take up the task, will send you the script once it is ready,
so you can bless it. :)

Regards
Nik




On Tue, Apr 9, 2013 at 11:34 AM, hubert depesz lubaczewski <
dep...@depesz.com> wrote:

> On Tue, Apr 09, 2013 at 11:24:22AM -0700, Nik Tek wrote:
> > --For MSSQL
> > select
> ...
> > -- Oracle
> ...
>
> Well, the answer is simple - in Microsoft and Oracle, someone wrote such
> views/functions. In Pg - not. You are welcome to provide a patch,
> though :)
>
> Best regards,
>
> depesz
>
>

[PERFORM] Postgres log(pg_logs) have lots of message

[Nik Tek]

Hi,

Could some please explain what these warnings mean in postgres.
I see these messages a lot when automatic vacuum runs.

  1 tm:2013-04-10 11:39:20.074 UTC db: pid:13766 LOG:  automatic vacuum
of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate
scan
  1 tm:2013-04-10 11:40:22.849 UTC db: pid:14286 LOG:  automatic vacuum
of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate
scan
  1 tm:2013-04-10 11:41:17.500 UTC db: pid:14491 LOG:  automatic vacuum
of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate
scan

Thank you
Nik

Re: [PERFORM] [ADMIN] Postgres log(pg_logs) have lots of message

[Nik Tek]

Hi Bambi,

Thank you the prompt reply.

This table is very volatile, lot of inserts/updates happen on this
tables(atleast 20~30 inserts/min).
When auto vacuum tries to run on this table, I get this warning.

Is there a way, I force it to happen, because the table/indexes statistics
are becoming stale very quickly.

Thank you
Nik

[PERFORM] Recommended Swap space

[Nik Tek]

Hi,

What is the recommended swap space for postgres or 9.0.11 or 9.2.3 on
linux(3.0.58-0.6)
RAM on the hox is 32GB.

Thank you
Nik