Thread: Help with count(*)
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.
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
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
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
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
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
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/>
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
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
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
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
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