Обсуждение: Statistics collection question

A couple of questions about the "most_common_vals" stuff in pg_stats
for a high traffic table:

1. Can I tell the stats collector to collect only values of a column
where a certain regex is matched? It is currently collecting the 500
values where most of them are values that I don't want, so it's
polluted with unwanted vals and therefore useless.

2. Secondly, for a unique column in the table, will the
"most_common_vals" always be -1? I guess this could make sense, but I
was wondering if the stats collector could somehow collect at least
1000 unique values to improve at least some performance.

TIA!

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> A couple of questions about the "most_common_vals" stuff in pg_stats
> for a high traffic table:

> 1. Can I tell the stats collector to collect only values of a column
> where a certain regex is matched?

Not directly, but you could set up a partial index defined that way,
and ANALYZE would collect stats on the index contents.  Whether the
planner could actually do anything with the information is another
story; I suspect you're wasting your time with this idea.

> 2. Secondly, for a unique column in the table, will the
> "most_common_vals" always be -1? I guess this could make sense, but I
> was wondering if the stats collector could somehow collect at least
> 1000 unique values to improve at least some performance.

most_common_vals will (and should) be empty if there aren't actually any
common values, but aren't you getting a histogram?  Exactly what
performance do you think will be improved?

            regards, tom lane

On 03/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:

> most_common_vals will (and should) be empty if there aren't actually any
> common values, but aren't you getting a histogram?  Exactly what
> performance do you think will be improved?


Lots of posts here in reponse to performance question have the
recommendation "increase the stats on that column". From whatever
succint reading is made available on the postgres site, I gather that
this aids the planner in getting some info about some of the data. Am
I missing something here, or totally off-base?

The issue is that I don't quite get why MySQL can fetch one indexed
row (i.e., SQL that ends with a very simple "WHERE indexed_column =
'constant' ") in a matter of milliseconds, but PgSQL is taking 5 to 6
seconds on an average at least for the first time. I use RAPTOR 15K
drives, they're not SCSI but they're not exactly "cheap disks" either.
And I have 4GB RAM. The explain select  shows that index is being
used!

TIA.

Phoenix Kiula escribió:
> On 03/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
>
> > most_common_vals will (and should) be empty if there aren't actually any
> > common values, but aren't you getting a histogram?  Exactly what
> > performance do you think will be improved?
>
>
> Lots of posts here in reponse to performance question have the
> recommendation "increase the stats on that column". From whatever
> succint reading is made available on the postgres site, I gather that
> this aids the planner in getting some info about some of the data. Am
> I missing something here, or totally off-base?
>
> The issue is that I don't quite get why MySQL can fetch one indexed
> row (i.e., SQL that ends with a very simple "WHERE indexed_column =
> 'constant' ") in a matter of milliseconds, but PgSQL is taking 5 to 6
> seconds on an average at least for the first time. I use RAPTOR 15K
> drives, they're not SCSI but they're not exactly "cheap disks" either.
> And I have 4GB RAM. The explain select  shows that index is being
> used!

Let's see the explain output?  I doubt your games with stats have
anything to do with it.  Maybe it is having to scan a lot of dead tuples
or something like that.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Phoenix Kiula wrote:
> Lots of posts here in reponse to performance question have the
> recommendation "increase the stats on that column". From whatever
> succint reading is made available on the postgres site, I gather that
> this aids the planner in getting some info about some of the data. Am
> I missing something here, or totally off-base?

As I understand it it's a sample of how the data is distributed.
Probably it's based on statistical mathematics that specifies a minimum
size for a representive sample of a given data set. It boils down to:
"If you want to know how many people like vanilla ice cream, how many
people do you need to ask their preference?".

> The issue is that I don't quite get why MySQL can fetch one indexed
> row (i.e., SQL that ends with a very simple "WHERE indexed_column =
> 'constant' ") in a matter of milliseconds, but PgSQL is taking 5 to 6
> seconds on an average at least for the first time. I use RAPTOR 15K
> drives, they're not SCSI but they're not exactly "cheap disks" either.
> And I have 4GB RAM. The explain select  shows that index is being
> used!

That's definitely not normal. I have a smallish table here containing
2.5 million records, and querying for one with a specific index takes
141 micro(!) seconds. The hardware involved is a dual opteron with 4G,
in a xen domain; I don't know what disks are used, but I doubt they're
raptors.

So something is wrong with your setup, that much is obvious. I sincerely
doubt that postgres is to blame here.

You did check that you're not connecting through the internet and
getting a DNS timeout?

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On 03/09/07, Alban Hertroys <alban@magproductions.nl> wrote:
> Phoenix Kiula wrote:
> As I understand it it's a sample of how the data is distributed.
> Probably it's based on statistical mathematics that specifies a minimum
> size for a representive sample of a given data set. It boils down to:
> "If you want to know how many people like vanilla ice cream, how many
> people do you need to ask their preference?".


Thanks for this explanation. So I should set the statistics on my
indexed column to 10 or so? I made it 1000 this morning, trying to see
how it would affect performance.



> That's definitely not normal. I have a smallish table here containing
> 2.5 million records, and querying for one with a specific index takes
> 141 micro(!) seconds. The hardware involved is a dual opteron with 4G,
> in a xen domain; I don't know what disks are used, but I doubt they're
> raptors.
>
> So something is wrong with your setup, that much is obvious. I sincerely
> doubt that postgres is to blame here.
>
> You did check that you're not connecting through the internet and
> getting a DNS timeout?



I am getting these times from the postgres log (pglog). I have setup
the minimum query time as 5000 (ms). Here is an except from my
log...which is constantly updated with more and more of these!

Here's an excerpt from  the log. It looks abysmal!!


-------------------

LOG:  duration: 85865.904 ms  statement: select t_info, dstats, id
from trades where t_alias = '1q8bf' and status = 'Y'
LOG:  duration: 83859.505 ms  statement: select t_info, dstats, id
from trades where t_alias = '1a7iv' and status = 'Y'
LOG:  duration: 71922.423 ms  statement: select t_info, dstats, id
from trades where t_alias = 'bvu' and status = 'Y'
LOG:  duration: 74924.741 ms  statement: select t_info, dstats, id
from trades where t_alias = 'nt3g' and status = 'Y'
LOG:  duration: 82471.036 ms  statement: select t_info, dstats, id
from trades where t_alias = '15p8m' and status = 'Y'
LOG:  duration: 90015.410 ms  statement: select t_info, dstats, id
from trades where t_alias = 'pkfi' and status = 'Y'
LOG:  duration: 72713.815 ms  statement: select t_info, dstats, id
from trades where t_alias = 'evdi' and status = 'Y'
LOG:  duration: 88054.444 ms  statement: select t_info, dstats, id
from trades where t_alias = '1a8zj' and status = 'Y'
LOG:  duration: 94502.678 ms  statement: select t_info, dstats, id
from trades where t_alias = '1d188' and status = 'Y'
LOG:  duration: 82178.724 ms  statement: select t_info, dstats, id
from trades where t_alias = 'q8zu' and status = 'Y'
LOG:  duration: 107030.741 ms  statement: select t_info, dstats, id
from trades where t_alias = 'jnzu' and status = 'Y'
LOG:  duration: 87634.723 ms  statement: select t_info, dstats, id
from trades where t_alias = 'tav9' and status = 'Y'
LOG:  duration: 104271.695 ms  statement: select t_info, dstats, id
from trades where t_alias = '37tk7' and status = 'Y'
LOG:  duration: 88726.671 ms  statement: select t_info, dstats, id
from trades where t_alias = 'tavc' and status = 'Y'
LOG:  duration: 74710.120 ms  statement: select t_info, dstats, id
from trades where t_alias = '1q8zu' and status = 'Y'
LOG:  duration: 93100.863 ms  statement: select t_info, dstats, id
from trades where t_alias = '1ovmc' and status = 'Y'
LOG:  duration: 83659.489 ms  statement: select t_info, dstats, id
from trades where t_alias = '1p9ub' and status = 'Y'
LOG:  duration: 71963.413 ms  statement: select t_info, dstats, id
from trades where t_alias = '9awlia' and status = 'Y'
LOG:  duration: 83569.602 ms  statement: select t_info, dstats, id
from trades where t_alias = '2yeza' and status = 'Y'
LOG:  duration: 93473.282 ms  statement: select t_info, dstats, id
from trades where t_alias = '17huv' and status = 'Y'

-----------------------


By way of an explanation, the T_INFO is a text column, DSTATS is
char(1), and ID is the bigint primary key. Status can be 'Y' or 'N',
so I have not included it in the index (not selective enough) but
T_ALIAS is the unique index.

The EXPLAIN ANALYZE output is as follows:


MYUSER=# explain analyze select t_info, dstats, id from trades where
t_alias = '17huv' and status = 'Y';

                                                         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using trades_unique_t_alias on trades  (cost=0.00..3.41
rows=1 width=110) (actual time=0.100..0.104 rows=1 loops=1)
   Index Cond: ((t_alias)::text = '17huv'::text)
   Filter: (status = 'Y'::bpchar)
 Total runtime: 0.166 ms
(4 rows)

Time: 2.990 ms




And my postgresql.conf is looking like this:



max_connections              = 350
shared_buffers               = 21000    # Not much more than
20k...http://www.revsys.com/writings/postgresql-performance.html
effective_cache_size         = 128000
max_fsm_relations            = 100
max_fsm_pages                = 150000
work_mem                     = 16000    #
http://www.revsys.com/writings/postgresql-performance.html
temp_buffers                 = 4096
authentication_timeout       = 10s
ssl                          = off
autovacuum                   = on
vacuum_cost_delay            = 20
stats_start_collector        = on
stats_row_level              = on
autovacuum_vacuum_threshold  = 300
autovacuum_analyze_threshold = 100
wal_buffers                  = 64
checkpoint_segments          = 128
checkpoint_timeout           = 900
fsync                        = on
maintenance_work_mem         = 128MB
enable_indexscan             = on
enable_bitmapscan            = off
####random_page_cost             = 1.5


Any thoughts? I tried a "random_page_cost" of 1.5 and 2 -- I
understand that keeping it at 1.5 would enable most of the data to be
in memory and I have 4GB of RAM -- but this made some of the queries
abysmally slow.

--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> LOG:  duration: 93473.282 ms  statement: select t_info, dstats, id
> from trades where t_alias = '17huv' and status = 'Y'
>
> -----------------------
>
>  Index Scan using trades_unique_t_alias on trades  (cost=0.00..3.41

> Time: 2.990 ms


Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
statement times?

Regards,
Richard Broersma Jr.

On 04/09/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> --- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> > LOG:  duration: 93473.282 ms  statement: select t_info, dstats, id
> > from trades where t_alias = '17huv' and status = 'Y'
> >
> > -----------------------
> >
> >  Index Scan using trades_unique_t_alias on trades  (cost=0.00..3.41
>
> > Time: 2.990 ms
>
>
> Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
> statement times?
>



Because the statement has been executed and is in the cache.

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> On 04/09/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>> Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
>> statement times?

> Because the statement has been executed and is in the cache.

That answer is way too flippant.  In particular it doesn't explain your
repeated 80sec queries --- you should have enough memory in that thing
to be caching a fair amount of your data.

I'm wondering about some transaction taking exclusive lock on the table
and sitting on it for a minute or so, and also about network problems
delaying transmission of data to the client.

            regards, tom lane

On 04/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> > On 04/09/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >> Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
> >> statement times?
>
> > Because the statement has been executed and is in the cache.
>
> That answer is way too flippant.  In particular it doesn't explain your
> repeated 80sec queries --- you should have enough memory in that thing
> to be caching a fair amount of your data.
>
> I'm wondering about some transaction taking exclusive lock on the table
> and sitting on it for a minute or so, and also about network problems
> delaying transmission of data to the client.
>


How can I check what is causing the lack? When I restart pgsql it goes
away. The log is empty for a day or too (I'm only logging errors or
slow queries) and the queries are super fast, but after a day it
starts filling up with abysmally slow queries, even on simple queries
with the WHERE clauses that have only one constant on the indexed
column!

As for "network problems delaying transmission of data" -- not sure
what this means. MySQL is super fast on the very same system. Does
pgsql require anything different?

Basically, what I am missing is some info on actually tweaking the
postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and
Exim (email server) on the same dedicated hosting server. I don't mind
if Postgres hogs 2GB of memory, but I need to know how to tweak it. I
have made about eight posts on this list with my entire
postgresql.conf posted in. I have read and re-read the manual and
devoured as many google-groups archives of this list as I possibly
can. I am looking at plenty of catalogue and stats tables (a utility
that makes compiles all of it and presents the system's missteps and
guidelines may be useful, ala "Tuning Primer" script from MySQL camp)
but I am not sure where to begin!

Would appreciate any help. Why do indexed queries take so much time?
It's a simple DB with "10 relations" including tables and indexes.
Simple inserts and updates, about 5000 a day, but non-trivial
concurrent selects (about 45 million a day). Works fine when I
restart, but a day later all goes cattywumpus.

TIA!

Sounds like index bloat to me ... lots of updates of indexed columns = lots of extra dead index entries. Since IIRC PostgreSQL indexes (indicii?) don't store information about the "liveness" of the referenced rows, indexed reads would have to sort through a lot of dead wood to find the few live indexed entries.

If you can, try to schedule a few minutes of down time every N hours and reindex the effected tables, followed by a vacuum/analyze to reclaim dead space and update stats maybe ? Admittedly hard but perhaps easier to have 5-10 minutes of down time regularly rather than very slow queries for hours on end. If this works even as a temporary solution it might point the way to a better long term fix.

It sounds as if you have too many services on one server -- the contentions of each for memory and disk I/O would worry me a lot. I tend to like having dedicated DB servers except for certain light-weight development environments.

And stop trying to make PostgreSQL into MySQL, or vice versa. Different engines, different regimes. Not translatable me thinks.

Just sodden thoughts ... sorry for top posting (challenged email tool).

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Phoenix Kiula
Sent: Tue 9/4/2007 1:07 AM
To: Tom Lane
Cc: Richard Broersma Jr; Alban Hertroys; Postgres General
Subject: Re: [GENERAL] Statistics collection question

On 04/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> > On 04/09/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >> Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
> >> statement times?
>
> > Because the statement has been executed and is in the cache.
>
> That answer is way too flippant.  In particular it doesn't explain your
> repeated 80sec queries --- you should have enough memory in that thing
> to be caching a fair amount of your data.
>
> I'm wondering about some transaction taking exclusive lock on the table
> and sitting on it for a minute or so, and also about network problems
> delaying transmission of data to the client.
>


How can I check what is causing the lack? When I restart pgsql it goes
away. The log is empty for a day or too (I'm only logging errors or
slow queries) and the queries are super fast, but after a day it
starts filling up with abysmally slow queries, even on simple queries
with the WHERE clauses that have only one constant on the indexed
column!

As for "network problems delaying transmission of data" -- not sure
what this means. MySQL is super fast on the very same system. Does
pgsql require anything different?

Basically, what I am missing is some info on actually tweaking the
postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and
Exim (email server) on the same dedicated hosting server. I don't mind
if Postgres hogs 2GB of memory, but I need to know how to tweak it. I
have made about eight posts on this list with my entire
postgresql.conf posted in. I have read and re-read the manual and
devoured as many google-groups archives of this list as I possibly
can. I am looking at plenty of catalogue and stats tables (a utility
that makes compiles all of it and presents the system's missteps and
guidelines may be useful, ala "Tuning Primer" script from MySQL camp)
but I am not sure where to begin!

Would appreciate any help. Why do indexed queries take so much time?
It's a simple DB with "10 relations" including tables and indexes.
Simple inserts and updates, about 5000 a day, but non-trivial
concurrent selects (about 45 million a day). Works fine when I
restart, but a day later all goes cattywumpus.

TIA!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

On Tue, Sep 04, 2007 at 03:07:41PM +0800, Phoenix Kiula wrote:
> How can I check what is causing the lack? When I restart pgsql it goes
> away. The log is empty for a day or too (I'm only logging errors or
> slow queries) and the queries are super fast, but after a day it
> starts filling up with abysmally slow queries, even on simple queries
> with the WHERE clauses that have only one constant on the indexed
> column!

Check you're not running VACUUM FULL anywhere and post the (complete)
output of VACUUM VERBOSE as superuser. That way we can rule out
index/table bloat.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Phoenix Kiula wrote:
> On 04/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm wondering about some transaction taking exclusive lock on the table
>> and sitting on it for a minute or so, and also about network problems
>> delaying transmission of data to the client.
>>
> How can I check what is causing the lack? When I restart pgsql it goes

You mean the lock?

You can check for active locks querying pg_locks

> away. The log is empty for a day or too (I'm only logging errors or
> slow queries) and the queries are super fast, but after a day it
> starts filling up with abysmally slow queries, even on simple queries
> with the WHERE clauses that have only one constant on the indexed
> column!

That's new information that we could have used earlier, as it means that
postgres does pick the right plan (at least initially) and things like
network and dns apparently work.

Was the explain analyze you sent from the super fast periods or from a
slow period? It'd be interesting to see a query plan of a problematic query.

I suppose if you try one of your super fast queries it is slow once
other queries slow down too? I ask, because I expect that query to not
be in the cache at that moment, so it could be a good candidate for an
explain analyze.

> Basically, what I am missing is some info on actually tweaking the
> postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and
> Exim (email server) on the same dedicated hosting server. I don't mind
> if Postgres hogs 2GB of memory, but I need to know how to tweak it. I
> have made about eight posts on this list with my entire
> postgresql.conf posted in. I have read and re-read the manual and

Yes, but you gave us conflicting information. Only now it is clear what
your problem is.

> that makes compiles all of it and presents the system's missteps and
> guidelines may be useful, ala "Tuning Primer" script from MySQL camp)
> but I am not sure where to begin!

I've seen pgadmin III doing quite a nice job at that. Haven't really
used it myself, I usually prefer the command line.

> Would appreciate any help. Why do indexed queries take so much time?
> It's a simple DB with "10 relations" including tables and indexes.
> Simple inserts and updates, about 5000 a day, but non-trivial

It looks like your indexes get bloated. Do you vacuum enough?
It'd be a good idea to at least analyze the tables involved in those
inserts regularly.

If you do those inserts in a batch, be sure to call ANALYZE after
commiting that batch. That helps quite a bit.

Besides that... How are those disks configured? You didn't put them in a
raid-5 array I hope? That wouldn't explain the above problem, but it
would slow things down (such has been mentioned on this list a few
times) and may thus be exaggerating the problem.

Good luck!

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> Basically, what I am missing is some info on actually tweaking the
> postgresql.conf to suit my system.

No, that's *not* what you're missing.  I'm not sure what the problem
is in your system, but I'm pretty sure that everything you have
frantically been tweaking is unrelated if not outright
counterproductive.  You need to stop tweaking and start some methodical
evidence-gathering to figure out what the problem actually is.

Here are some things I would suggest trying:

1. Do a VACUUM VERBOSE when the system is fast, and save the output.
When the system is slow, do another VACUUM VERBOSE, and compare file
sizes to see if anything seems markedly bloated.  (It might be less
labor-intensive to copy pg_class.relname, reltuples, relpages columns
into another table for safekeeping after the first VACUUM, and use SQL
queries to look for markedly different sizes after the second VACUUM.)

2. Set up a task to dump the results of
    select * from pg_locks, pg_stat_activity where pid = procpid
into a log file every few seconds.  Compare what you see when things
are fast with when they are slow.  In particular you should fairly
easily be able to tell if the slow queries are waiting long for locks.

3. Log the output of "vmstat 1" over time, compare fast and slow
periods.

            regards, tom lane

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> Would appreciate any help. Why do indexed queries take so much time?
> It's a simple DB with "10 relations" including tables and indexes.
> Simple inserts and updates, about 5000 a day, but non-trivial
> concurrent selects (about 45 million a day). Works fine when I
> restart, but a day later all goes cattywumpus.

BTW, just to be perfectly clear: all you do is stop and restart the
postmaster (using what commands exactly?), and everything is fast again?
That's sufficiently unheard-of that I want to be entirely sure we
understood you correctly.

            regards, tom lane

On Sep 4, 10:54 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "Phoenix Kiula" <phoenix.ki...@gmail.com> writes:
> > Would appreciate any help. Why do indexed queries take so much time?
> > It's a simple DB with "10 relations" including tables and indexes.
> > Simple inserts and updates, about 5000 a day, but non-trivial
> > concurrent selects (about 45 million a day). Works fine when I
> > restart, but a day later all goes cattywumpus.
>
> BTW, just to be perfectly clear: all you do is stop and restart the
> postmaster (using what commands exactly?), and everything is fast again?
> That's sufficiently unheard-of that I want to be entirely sure we
> understood you correctly.




Yes, I noticed starting the postgres database again had an effect of
speed. But this does not seem to be working anymore so I suppose
something else needs fixing.

When I do a "select * from pg_locks", some of them show up as
"Exclusive Lock". This I suppose means that the whole table is locked,
right? How can I find from the "transaction id" which precise SQL
statement is taking this time? I do not have anything that should!
Simple SELECT, INSERT and UPDATE stuff in our fairly straightforward
application, and I hope that autovacuum and auto-analyze do not take
up this exclusive locks?

Ref: output of the select from pg_locks --



=# select * from pg_locks;
-[ RECORD 1 ]-+----------------
locktype      | transactionid
database      |
relation      |
page          |
tuple         |
transactionid | 47999900
classid       |
objid         |
objsubid      |
transaction   | 47999900
pid           | 21989
mode          | ExclusiveLock
granted       | t

-[ RECORD 2 ]-+----------------
locktype      | relation
database      | 41249
relation      | 10328
page          |
tuple         |
transactionid |
classid       |
objid         |
objsubid      |
transaction   | 47999900
pid           | 21989
mode          | AccessShareLock
granted       | t

On Mon, Sep 10, 2007 at 07:05:54PM -0000, phoenix.kiula@gmail.com wrote:
> When I do a "select * from pg_locks", some of them show up as
> "Exclusive Lock". This I suppose means that the whole table is locked,
> right? How can I find from the "transaction id" which precise SQL
> statement is taking this time? I do not have anything that should!
> Simple SELECT, INSERT and UPDATE stuff in our fairly straightforward
> application, and I hope that autovacuum and auto-analyze do not take
> up this exclusive locks?

Note: the example ExclusiveLock you showed is merely the transaction
holding an exclusive lock on itself. As you can see, there is no
database or relation mentioned, so it's not locking anything else. It
has a shared lock on a table, but that's normal.

For more info the activity, try "select * from pg_stat_activity;"

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Well first question: how can I check if autovacuum is working?


On 04/09/2007, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> > Basically, what I am missing is some info on actually tweaking the
> > postgresql.conf to suit my system.
>
> No, that's *not* what you're missing.  I'm not sure what the problem
> is in your system, but I'm pretty sure that everything you have
> frantically been tweaking is unrelated if not outright
> counterproductive.  You need to stop tweaking and start some methodical
> evidence-gathering to figure out what the problem actually is.
>
> Here are some things I would suggest trying:
>
> 1. Do a VACUUM VERBOSE when the system is fast, and save the output.
> When the system is slow, do another VACUUM VERBOSE, and compare file
> sizes to see if anything seems markedly bloated.  (It might be less
> labor-intensive to copy pg_class.relname, reltuples, relpages columns
> into another table for safekeeping after the first VACUUM, and use SQL
> queries to look for markedly different sizes after the second VACUUM.)


Did this. Saved the files as text files. Did not find much difference
for the tables and indexes stuff. Number of pages required overall
remains the same, by and large. Do I also need to compare the
"pg_toast" type stuff?



> 2. Set up a task to dump the results of
>         select * from pg_locks, pg_stat_activity where pid = procpid
> into a log file every few seconds.  Compare what you see when things
> are fast with when they are slow.  In particular you should fairly
> easily be able to tell if the slow queries are waiting long for locks.



Yes, did. Saved them into four different tables (scores1, scores2,
....where scores1 represents a time when queries were superfast,
scores4 when it was pathetically slow). Then joined them all, two at a
time, to track differences. The only four rows that are different
across these four tables are related to my two major tables:

#  select scores4.relname, scores4.reltuples, scores4.relpages,
scores1.relpages from scores4
left join scores1 on scores4.relname = scores1.relname where
scores4.relpages <> scores1.relpages
;


       relname        |  reltuples  | relpages | relpages
----------------------+-------------+----------+----------
 idx_trads_userid     | 2.82735e+06 |    11652 |    11644
 idx_trads_modifydate | 2.82735e+06 |     7760 |     7744
 tradcount            |      201349 |     1391 |     1388
 trads_alias_key      | 2.82735e+06 |    16172 |    16135
(6 rows)

Time: 2.073 ms


What do I make from this? From what I observe, some of the indexes
have a few more values and a few more pages thereof. This is exactly
how it should be, right? This is from a small database.




> 3. Log the output of "vmstat 1" over time, compare fast and slow
> periods.
>


Following is the vmstat from slow time:


 ~ > vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0 12  14136  15608   5208 3554516    0    0   200   140    8     7  2  1 86 12
 0 14  14136  17208   5200 3552964    0    0     0    52 1137   372  0  0 23 77
 0 15  14136  17336   5204 3551140    0    0     0    60 1085   237  0  0 10 89
 0 16  14136  16832   5204 3551140    0    0    64     0 1108   323  0  0 25 75
 0 15  14136  15872   5204 3551140    0    0     0     0 1066   242  0  0 25 75
 0 16  14136  17360   5196 3546468    0    0   492   304 1144   570  1  1 29 69
 0 17  14152  17744   5192 3542816    0   48     0   188 1127   169  1  0 25 74
 0 10  14172  23312   5216 3540432    0    0   528   292 1244   453  0  1 25 74
 2  3  14064  15888   5276 3550148    0    0  6644   964 1192   427  1  1 65 33
 0  2  13840  16656   5232 3548596    0    0 24708    60 1413   882  1  2 75 23


Not sure how to read this. We're on 4GB RAM.

Thanks.