Thread: 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.
Attachment
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.
Attachment
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.