Thread: Help with count(*)

Help with count(*)

From
Rajesh Kumar Mallah
Date:

Hi ,

my database seems to be taking too long for a select count(*)
i think there are lot of dead rows. I do a vacuum full it improves
bu again the performance drops in a short while ,
can anyone please tell me if anything worng with my fsm settings
current fsm=55099264 (not sure how i calculated it)

Regds
Mallah

tradein_clients=# SELECT count(*) from data_bank.profiles ;

+--------+
| count  |
+--------+
| 123065 |
+--------+
(1 row)

Time: 49756.969 ms
tradein_clients=#
tradein_clients=#
tradein_clients=# VACUUM full verbose analyze  data_bank.profiles ;
INFO:  vacuuming "data_bank.profiles"

INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
DETAIL:  246130 dead row versions cannot be removed yet.
Nonremovable row versions range from 136 to 2036 bytes long.
There were 427579 unused item pointers.
Total free space (including removable row versions) is 178536020 bytes.
15934 pages are or will become empty, including 0 at the end of the table.
38112 pages containing 178196624 free bytes are potential move destinations.
CPU 1.51s/0.63u sec elapsed 23.52 sec.
INFO:  index "profiles_pincode" now contains 369195 row versions in 3353 pages
DETAIL:  0 index row versions were removed.
379 index pages have been deleted, 379 are currently reusable.
CPU 0.20s/0.24u sec elapsed 22.73 sec.
INFO:  index "profiles_city" now contains 369195 row versions in 3411 pages
DETAIL:  0 index row versions were removed.
1030 index pages have been deleted, 1030 are currently reusable.
CPU 0.17s/0.21u sec elapsed 20.67 sec.
INFO:  index "profiles_branch" now contains 369195 row versions in 2209 pages
DETAIL:  0 index row versions were removed.
783 index pages have been deleted, 783 are currently reusable.
CPU 0.07s/0.14u sec elapsed 6.38 sec.
INFO:  index "profiles_area_code" now contains 369195 row versions in 2606 pages
DETAIL:  0 index row versions were removed.
856 index pages have been deleted, 856 are currently reusable.
CPU 0.11s/0.17u sec elapsed 19.62 sec.
INFO:  index "profiles_source" now contains 369195 row versions in 3137 pages
DETAIL:  0 index row versions were removed.
1199 index pages have been deleted, 1199 are currently reusable.
CPU 0.14s/0.12u sec elapsed 9.95 sec.
INFO:  index "co_name_index_idx" now contains 368742 row versions in 3945 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.19s/0.69u sec elapsed 11.56 sec.
INFO:  index "address_index_idx" now contains 368898 row versions in 4828 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.61u sec elapsed 9.17 sec.
INFO:  index "profiles_exp_cat" now contains 153954 row versions in 2168 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.25u sec elapsed 3.14 sec.
INFO:  index "profiles_imp_cat" now contains 73476 row versions in 1030 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.11u sec elapsed 8.73 sec.
INFO:  index "profiles_manu_cat" now contains 86534 row versions in 1193 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.13u sec elapsed 1.44 sec.
INFO:  index "profiles_serv_cat" now contains 19256 row versions in 267 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.25 sec.
INFO:  index "profiles_pid" now contains 369195 row versions in 812 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.12u sec elapsed 0.41 sec.
INFO:  index "profiles_pending_branch_id" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "profiles": moved 0 row versions, truncated 43423 to 43423 pages
DETAIL:  CPU 1.76s/3.01u sec elapsed 60.39 sec.
INFO:  vacuuming "pg_toast.pg_toast_39873340"
INFO:  "pg_toast_39873340": found 0 removable, 65 nonremovable row versions in 15 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 47 to 2034 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 17672 bytes.
0 pages are or will become empty, including 0 at the end of the table.
14 pages containing 17636 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.77 sec.
INFO:  index "pg_toast_39873340_index" now contains 65 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.46 sec.
INFO:  "pg_toast_39873340": moved 0 row versions, truncated 15 to 15 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "data_bank.profiles"
INFO:  "profiles": 43423 pages, 123065 rows sampled, 123065 estimated total rows
VACUUM
Time: 246989.138 ms
tradein_clients=# SELECT count(*) from data_bank.profiles ;
+--------+
| count  |
+--------+
| 123065 |
+--------+
(1 row)

Time: 4978.725 ms
tradein_clients=#

IMPORVED but still not very good.

Regds
Mallah.




Re: Help with count(*)

From
Shridhar Daithankar
Date:
On Friday 14 November 2003 12:51, Rajesh Kumar Mallah wrote:
> Hi ,
>
> my database seems to be taking too long for a select count(*)
> i think there are lot of dead rows. I do a vacuum full it improves
> bu again the performance drops in a short while ,
> can anyone please tell me if anything worng with my fsm settings
> current fsm=55099264 (not sure how i calculated it)

If you don't need exact count, you can use statistics. Just analyze frequently
and you will get the statistics.

and I didn't exact;y understand this in the text.

INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in
43423 pages
DETAIL:  246130 dead row versions cannot be removed yet.

Is there a transaction holoding up large amount of stuff?

 Shridhar


Re: Help with count(*)

From
Christopher Browne
Date:
Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
> INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
> DETAIL:  246130 dead row versions cannot be removed yet.
> Nonremovable row versions range from 136 to 2036 bytes long.

It seems as though you have a transaction open that is holding onto a
whole lot of old rows.

I have seen this happen somewhat-invisibly when a JDBC connection
manager opens transactions for each connection, and then no processing
happens to use those connections for a long time.  The open
transactions prevent vacuums from doing any good...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/multiplexor.html
"Waving away a cloud of smoke, I  look up, and am blinded by a bright,
white light.  It's God. No,  not Richard Stallman, or  Linus Torvalds,
but God. In a booming voice, He  says: "THIS IS A SIGN. USE LINUX, THE
FREE Unix SYSTEM FOR THE 386." -- Matt Welsh

Re: Help with count(*)

From
Hannu Krosing
Date:
Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
> Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
> > INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
> > DETAIL:  246130 dead row versions cannot be removed yet.
> > Nonremovable row versions range from 136 to 2036 bytes long.
>
> It seems as though you have a transaction open that is holding onto a
> whole lot of old rows.
>
> I have seen this happen somewhat-invisibly when a JDBC connection
> manager opens transactions for each connection, and then no processing
> happens to use those connections for a long time.  The open
> transactions prevent vacuums from doing any good...

Can't the backend be made to delay the "real" start of transaction until
the first query gets executed ?

------------
Hannu


Re: Help with count(*)

From
Tom Lane
Date:
Hannu Krosing <hannu@tm.ee> writes:
> Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>> I have seen this happen somewhat-invisibly when a JDBC connection
>> manager opens transactions for each connection, and then no processing
>> happens to use those connections for a long time.  The open
>> transactions prevent vacuums from doing any good...

> Can't the backend be made to delay the "real" start of transaction until
> the first query gets executed ?

That is on the TODO list.  I looked at it briefly towards the end of the
7.4 development cycle, and decided that it was nontrivial and I didn't
have time to make it happen before beta started.  I don't recall why it
didn't seem trivial.

            regards, tom lane

Re: Help with count(*)

From
Will LaShell
Date:
Hannu Krosing wrote:

>Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>
>
>>Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
>>
>>
>>>INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
>>>DETAIL:  246130 dead row versions cannot be removed yet.
>>>Nonremovable row versions range from 136 to 2036 bytes long.
>>>
>>>
>>It seems as though you have a transaction open that is holding onto a
>>whole lot of old rows.
>>
>>I have seen this happen somewhat-invisibly when a JDBC connection
>>manager opens transactions for each connection, and then no processing
>>happens to use those connections for a long time.  The open
>>transactions prevent vacuums from doing any good...
>>
>>
>
>Can't the backend be made to delay the "real" start of transaction until
>the first query gets executed ?
>
>

That seems counter intuitive doesn't it?  Why write more code in the
server when the client is the thing that has the problem?

Will


Re: Help with count(*)

From
Christopher Browne
Date:
After a long battle with technology, hannu@tm.ee (Hannu Krosing), an earthling, wrote:
> Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>> Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
>> > INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
>> > DETAIL:  246130 dead row versions cannot be removed yet.
>> > Nonremovable row versions range from 136 to 2036 bytes long.
>>
>> It seems as though you have a transaction open that is holding onto a
>> whole lot of old rows.
>>
>> I have seen this happen somewhat-invisibly when a JDBC connection
>> manager opens transactions for each connection, and then no processing
>> happens to use those connections for a long time.  The open
>> transactions prevent vacuums from doing any good...
>
> Can't the backend be made to delay the "real" start of transaction until
> the first query gets executed ?

One would hope so.  Some time when I have the Round Tuits, I ought to
take a browse of the connection pool code to notice if there's
anything to notice.

The thing that I keep imagining would be a slick idea would be to have
a thread periodically go through once for however many connections the
pool permits and fire a short transaction through every
otherwise-unoccupied connection in the pool, in effect, doing a sort
of "vacuum" of the connections.  I don't get very favorable reactions
when I suggest that, though...
--
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://cbbrowne.com/info/sgml.html
Rules  of  the  Evil Overlord  #80.  "If  my  weakest troops  fail  to
eliminate a  hero, I will send  out my best troops  instead of wasting
time with progressively stronger ones  as he gets closer and closer to
my fortress." <http://www.eviloverlord.com/>

Re: Help with count(*)

From
Tom Lane
Date:
Will LaShell <will@lashell.net> writes:
> Hannu Krosing wrote:
>> Can't the backend be made to delay the "real" start of transaction until
>> the first query gets executed ?

> That seems counter intuitive doesn't it?  Why write more code in the
> server when the client is the thing that has the problem?

Because there are a lot of clients with the same problem :-(

A more principled argument is that we already postpone the setting of
the transaction snapshot until the first query arrives within the
transaction.  In a very real sense, the setting of the snapshot *is*
the start of the transaction.  So it would make sense if incidental
stuff like VACUUM also thought that the transaction hadn't started
until the first query arrives.  (I believe the previous discussion
also agreed that we wanted to postpone the freezing of now(), which
currently also happens at BEGIN rather than the first command after
BEGIN.)

            regards, tom lane

Re: Help with count(*)

From
Andrew Sullivan
Date:
On Fri, Nov 14, 2003 at 02:16:56PM -0500, Christopher Browne wrote:
> otherwise-unoccupied connection in the pool, in effect, doing a sort
> of "vacuum" of the connections.  I don't get very favorable reactions
> when I suggest that, though...

Because it's a kludge on top of another kludge, perhaps?  ;-)  This
needs to be fixed properly, not through an ungraceful series of
workarounds.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Help with count(*)

From
Dennis Bjorklund
Date:
On Fri, 14 Nov 2003, Tom Lane wrote:

> I believe the previous discussion also agreed that we wanted to postpone
> the freezing of now(), which currently also happens at BEGIN rather than
> the first command after BEGIN.

Or should that happen at the first call to now()?

/me should ge back and try to find this previous discussion.

--
/Dennis


Re: Help with count(*)

From
Neil Conway
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:
> (I believe the previous discussion also agreed that we wanted to
> postpone the freezing of now(), which currently also happens at
> BEGIN rather than the first command after BEGIN.)

That doesn't make sense to me: from a user's perspective, the "start
of the transaction" is when the BEGIN is issued, regardless of any
tricks we may play in the backend.

Making now() return the time the current transaction started is
reasonably logical; making now() return "the time when the first
command after the BEGIN in the current transaction was issued" makes a
lot less sense to me.

-Neil


Re: Help with count(*)

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> (I believe the previous discussion also agreed that we wanted to
>> postpone the freezing of now(), which currently also happens at
>> BEGIN rather than the first command after BEGIN.)

> That doesn't make sense to me: from a user's perspective, the "start
> of the transaction" is when the BEGIN is issued, regardless of any
> tricks we may play in the backend.

That's defensible when the user issued the BEGIN himself.  When the
BEGIN is coming from some interface library's autocommit logic, it's
a lot less defensible.  If you consult the archives, you will find
actual user complaints about "why is now() returning a very old time?"
that we traced to use of interface layers that handle "commit()" by
issuing "COMMIT; BEGIN;".

When BEGIN actually is issued by live application logic, I'd expect it
to be followed immediately by some kind of command --- so the user would
be unable to tell the difference in practice.

Hannu moved this thread to -hackers, please follow up there if you want
to discuss it more.

            regards, tom lane