Re: [PERFORM] Queries Per Second (QPS)

[Guillaume Lelarge]

Le 26 sept. 2015 6:26 PM, "Adam Scott"  a écrit :
>
> How do we measure queries per second (QPS), not transactions per second,
in PostgreSQL without turning on full logging which has a performance
penalty and can soak up lots of disk space?
>

The only way I can think of is to write an extension that will execute some
code at the end of the execution of a query.

Note that this might get tricky. Do you want to count any query? Such as
those in explicit transactions and those in plpgsql functions? People might
not see this your way, which may explain why I don't know of any such
extension.

> We are using 8.4, but I'm interested in any version as well.
>

Re: [PERFORM] Queries Per Second (QPS)

[Guillaume Lelarge]

Le 27 sept. 2015 8:02 AM, "Guillaume Lelarge" <guilla...@lelarge.info> a
écrit :
>
> Le 26 sept. 2015 6:26 PM, "Adam Scott" <adam.c.sc...@gmail.com> a écrit :
> >
> > How do we measure queries per second (QPS), not transactions per
second, in PostgreSQL without turning on full logging which has a
performance penalty and can soak up lots of disk space?
> >
>
> The only way I can think of is to write an extension that will execute
some code at the end of the execution of a query.
>
> Note that this might get tricky. Do you want to count any query? Such as
those in explicit transactions and those in plpgsql functions? People might
not see this your way, which may explain why I don't know of any such
extension.
>

Thinking about this, such an extension already exists. It's
pg_stat_statements. You need to sum the count column of the
pg_stat_statements from time to time. The difference between two sums will
be your number of queries.

> > We are using 8.4, but I'm interested in any version as well.
> >

Re: [PERFORM] Insert vs Update

[Guillaume Lelarge]

Le 15 juil. 2015 11:16 PM, David G. Johnston david.g.johns...@gmail.com
a écrit :

 On Wed, Jul 15, 2015 at 4:53 PM, Michael Nolan htf...@gmail.com wrote:

 On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco robert.difa...@gmail.com
wrote:


 Thanks David, my example was a big simplification, but I appreciate
your guidance. The different event types have differing amounts of related
data. Query speed on this schema is not important, it's really the write
speed that matters. So I was just wondering given the INSERT or UPDATE
approach (with no indexed data being changed) if one is likely to be
substantially faster than the other.


 As I understand how ACID compliance is done, updating a record will
require updating any indexes for that record, even if the index keys are
not changing.  That's because any pending transactions still need to be
able to find the 'old' data, while new transactions need to be able to find
the 'new' data.  And ACID also means an update is essentially a
delete-and-insert.


 ​I might be a bit pedantic here but what you describe is a byproduct of
the specific​ implementation that PostgreSQL uses to affect Consistency
(the C in ACID) as opposed to a forgone outcome in being ACID compliant.

 http://www.postgresql.org/docs/9.4/static/mvcc-intro.html

 I'm out of my comfort zone here but the HOT optimization is designed to
leverage the fact that an update to a row that does not affect indexed
values is able to leave the index alone and instead during index lookup the
index points to the old tuple, notices that there is a chain present, and
walks that chain to find the currently active tuple.


That's true as long as the old and new tuples are stored in the same block.

 In short, if the only index is a PK an update of the row can avoid
touching that index.

 I mentioned that going from NULL to Not NULL may disrupt this but I'm
thinking I may have mis-spoken.

 Also, with separate tables the amount of data to write is going to be
less because you'd have fewer columns on the affected tables.

 While an update is a delete+insert a delete is mostly just a bit-flip
action - at least mid-transaction.  Depending on volume, though, the
periodic impact of vaccuming may want to be taken into consideration.

-- 
Guillaume

Re: [PERFORM] QUERY PLANNER - Indexe mono column VS composite Index

[Guillaume Lelarge]

2015-07-09 22:34 GMT+02:00 Nicolas Paris nipari...@gmail.com:

 Hello,

 My 9.4 database is used as datawharehouse. I can't change the queries
 generated.

 first index  : INDEX COL (A,B,C,D,E)


 In case of query based on COL A,  the query planner sometimes go to a seq
 scan instead of using the first composite index.

 The solution is to add a second indexe (redondant)
 second index : INDEX COL (A)

 In case of query based on COL A, B, C, D, (without E) as well, it doesn't
 uses the first index and prefers a seq scan.

 I could create a third indexe :
 first index  : INDEX COL (A,B,C,D)

 But I hope there is an other solution for that (table is huge).

 It seems that the malus for using composite indexes is high.

 Question is : is there a way to make the composite index more attractive
 to query planner ? (idealy equivalent to mono column indexe)


There's no way we can answer that without seeing actual queries and query
plans.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [PERFORM] CREATE INDEX uses INDEX ?

[Guillaume Lelarge]

2015-03-08 10:04 GMT+01:00 Nicolas Paris nipari...@gmail.com:

 Thanks.

 Then,
 Is it a good idea to run multiple instance of create index on tableX on
 the same time ? Or is it better to do it sequentially ?
 In other words : Can one seq scan on a table  benefit to multiple create
 index stmt on that table ?


It usually is a good idea to parallelize index creation. That's one of the
good things that pg_restore does since the 8.4 release.

Nicolas PARIS

 2015-03-07 12:56 GMT+01:00 Guillaume Lelarge guilla...@lelarge.info:

 Le 7 mars 2015 11:32, Nicolas Paris nipari...@gmail.com a écrit :
 
  Hello,
 
  I wonder if the process of index creation can benefit from other
 indexes.
 

 It cannot.

  EG: Creating a partial index with predicat based on a boolean column,
 will use an hypothetic index on that boolean column or always use a seq
 scan on all rows ?
 

 Nope, it always does a seqscan.

  Goal is to create partial indexes faster.
 
  Explain command does not work with Create index.
 

 You cannot use EXPLAIN on most DDL commands.





-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [PERFORM] CREATE INDEX uses INDEX ?

[Guillaume Lelarge]

Le 7 mars 2015 11:32, Nicolas Paris nipari...@gmail.com a écrit :

 Hello,

 I wonder if the process of index creation can benefit from other indexes.


It cannot.

 EG: Creating a partial index with predicat based on a boolean column,
will use an hypothetic index on that boolean column or always use a seq
scan on all rows ?


Nope, it always does a seqscan.

 Goal is to create partial indexes faster.

 Explain command does not work with Create index.


You cannot use EXPLAIN on most DDL commands.

Re: [PERFORM] log_temp_files (integer), tuning work_mem

[Guillaume Lelarge]

Hi,

Le 5 nov. 2014 22:34, Tory M Blue tmb...@gmail.com a écrit :

 log_temp_files (integer)
 Controls logging of temporary file names and sizes. Temporary files can
be created for sorts, hashes, and temporary query results. A log entry is
made for each temporary file when it is deleted. A value of zero logs all
temporary file information

 so I've set this to;

 log_temp_files = 0  # log temporary files equal or
larger

 Reloaded the config and still have not seen a creation or deletion of a
log file. Is this still valid in 9.3 and do I need to change anything else?


Still works (though only shows creation, not deletion).

 I've got duration queries spitting out;

 2014-11-05 12:11:32 PST rvtempdb postgres [local] 31338 2014-11-05
12:11:32.257 PSTLOG:  duration: 1609.707 ms  statement: COPY adgroups
(adgroup_id, name, status, campaign_id, create_date, modify_date) FROM
stdin;

 So logging is working.

 I'm set to info ;

 log_min_messages = info

 So what would be the cause of not seeing anything ,and how can one turn
work_mem without seeing these entries?


My best guess would be your queries are happy enough with your current
work_mem setting.

With the default value, an easy enough example that shows such a message is:

create table t1(id integer);
insert into t1 select generate_series (1,100);
select * from t1 order by id;

With the last query, you should get a temporary file log message in your
log file.

Re: [PERFORM] Current query of the PL/pgsql procedure.

[Guillaume Lelarge]

On Mon, 2013-12-16 at 11:42 +, Yuri Levinsky wrote:
  Dear Depesz,
 This is very problematic solution: I have to change whole!!! my code to put 
 appropriate comment with query text before any query execution. In addition I 
 would like to know current execution plan, that seems to be impossible. This 
 is very hard limitation let's say. In case of production issue I'll just 
 unable to do it: the issue already happening, I can't stop procedure and 
 start code change.
 James,
 I saw your reply: I see the function is running, it's just not clear that 
 exactly and how this function doing. 
 

This blog post
(http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions)
 can probably help you profiling your PL/pgsql functions without modifying them.

I'm interested in any comments you can have on the log_functions hook
function.

Regards.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow query: bitmap scan troubles

[Guillaume Lelarge]

On Tue, 2012-12-04 at 15:42 -0800, Jeff Janes wrote:
 On Tue, Dec 4, 2012 at 10:03 AM,  postgre...@foo.me.uk wrote:
 [...]
 
  Is there some nice bit of literature somewhere that explains what sort of
  costs are associated with the different types of lookup?
 
 I've heard good things about Greg Smith's book, but I don't know if it
 covers this particular thing.
 
 Otherwise, I don't know of a good single place which is a tutorial
 rather than a reference (or the code itself)
 

Greg's book is awesome. It really gives a lot of
informations/tips/whatever on performances. I mostly remember all the
informations about hardware, OS, PostgreSQL configuration, and such. Not
much on the EXPLAIN part.

On the EXPLAIN part, you may have better luck with some slides available
here and there.

Robert Haas gave a talk on the query planner at pgCon 2010. The audio
feed of Robert Haas talk is available with this file:
http://www.pgcon.org/2010/audio/15%20The%20PostgreSQL%20Query%
20Planner.mp3

You can also find the slides on
https://sites.google.com/site/robertmhaas/presentations

You can also read the Explaining the Postgres Query Optimizer talk
written by Bruce Momjian. It's available there :
http://momjian.us/main/presentations/internals.html

And finally, you can grab my slides over here:
http://www.dalibo.org/_media/understanding_explain.pdf. You have more
than slides. I tried to put a lot of informations in there.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] REINDEX not working for wastedspace

[Guillaume Lelarge]

On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
 I am using Postgresql 9.0.1.
 
 Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
 the following result for a table:
 
 -[ RECORD 1 ]+---
 current_database | crm
 schemaname   | public
 tablename| _attachments
 tbloat   | 0.9
 wastedbytes  | 0
 iname| attachments_description_type_attachmentsid_idx
 ibloat   | 2.3
 wastedibytes | 5439488
 -[ RECORD 2 ]+---
 current_database | crm
 schemaname   | public
 tablename| _attachments
 tbloat   | 0.9
 wastedbytes  | 0
 iname| attachments_attachmentsid_idx
 ibloat   | 0.2
 wastedibytes | 0
 -[ RECORD 3 ]+---
 current_database | crm
 schemaname   | public
 tablename| _attachments
 tbloat   | 0.9
 wastedbytes  | 0
 iname| _attachments_pkey
 ibloat   | 0.2
 wastedibytes | 0
 
 I REINDEXED  both the indexes and table, but I did not find any change in
 wastedspace or wastedispace.
 Could you please tell me why?

REINDEX only rebuilds indexes. And you'll obviously have a bit of lost
space because of the FILLFACTOR value (90% on indexes IIRC).


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to track number of connections and hosts to Postgres cluster

[Guillaume Lelarge]

On Wed, 2011-08-24 at 13:05 +0530, Venkat Balaji wrote:
 Hello Everyone,
 
 I am working on an alert script to track the number of connections with the
 host IPs to the Postgres cluster.
 
 1. I need all the host IPs making a connection to Postgres Cluster (even for
 a fraction of second).

You should set log_connections to on.

 2. I would also want to track number of IDLE connections, IDLE IN
 TRANSACTION connections and length of the connections as well.
 

IDLE and IDLE in transactions are the kind of informations you get in
pg_stat_activity.

Length of connections, you can get it with log_disconnections.

 I would be making use of pg_stat_activity and also thought of enabling
 logging the host ips in the db server log files which seems to be expensive
 for me (in terms of IO and logfile size).
 

Using pg_stat_activity won't get you really small connections. You need
log_connections for that, and log_disconnections for the duration of
connections. So you'll have to work on a tool that could get some
informations with queries on pg_stat_activity, and that could read
PostgreSQL log files.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to track number of connections and hosts to Postgres cluster

[Guillaume Lelarge]

On Wed, 2011-08-24 at 16:51 +0530, Venkat Balaji wrote:
 But, the information vanishes if the application logs off.
 

That's why you need a tool to track this.

 I am looking for an alternative to track the total amount of the connections
 with the host IPs through a Cron job.
 

If you only want the number of connections, you can check_postgres.
 What could be the frequency of cron ?
 

I don't think you can go below one second.

 I know the best is using log_connections and log_disconnections parameters,
 but, information logged would be too high and is also IO intensive.
 

Sure. But if you want connection duration, that's the only way.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Memory usage of auto-vacuum

[Guillaume Lelarge]

Hi,

On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote:
 [...]
 We are running  a PostgreSQL 8.4 database, with  two tables containing a
 lot ( 1 million) moderatly  small rows. It contains some btree indexes,
 and one of the two tables contains a gin full-text index.
 
 We noticed  that the  autovacuum process  tend to use  a lot  of memory,
 bumping the postgres process near 1Gb while it's running.
 

Well, it could be its own memory (see maintenance_work_mem), or shared
memory. So, it's hard to say if it's really an issue or not.

BTW, how much memory do you have on this server? what values are used
for shared_buffers and maintenance_work_mem?

 I looked in  the documentations, but I didn't find  the information : do
 you know  how to estimate the  memory required for the  autovacuum if we
 increase the number of rows ? Is it linear ? Logarithmic ? 
 

It should use up to maintenance_work_mem. Depends on how much memory you
set on this parameter.

 Also, is  there a way  to reduce that  memory usage ?

Reduce maintenance_work_mem. Of course, if you do that, VACUUM could
take a lot longer to execute.

  Would  running the
 autovacuum more frequently lower its memory usage ?
 

Yes.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Memory usage of auto-vacuum

[Guillaume Lelarge]

On Sat, 2011-07-09 at 10:43 +0200, Gael Le Mignot wrote:
 Hello Guillaume!
 
 Sat, 09 Jul 2011 10:33:03 +0200, you wrote: 
 
   Hi,
   On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote:
   [...]
   We are running  a PostgreSQL 8.4 database, with  two tables containing a
   lot ( 1 million) moderatly  small rows. It contains some btree indexes,
   and one of the two tables contains a gin full-text index.
   
   We noticed  that the  autovacuum process  tend to use  a lot  of memory,
   bumping the postgres process near 1Gb while it's running.
   
 
   Well, it could be its own memory (see maintenance_work_mem), or shared
   memory. So, it's hard to say if it's really an issue or not.
 
   BTW, how much memory do you have on this server? what values are used
   for shared_buffers and maintenance_work_mem?
 
 maintenance_work_mem is at 16Mb, shared_buffers at 24Mb.
 

IOW, default values.

 The server currently has  2Gb, we'll add more to it (it's  a VM), but we
 would like to be able to make  an estimate on how much memory it'll need
 for a  given rate of  INSERT into the  table, so we can  estimate future
 costs.
 
   I looked in  the documentations, but I didn't find  the information : do
   you know  how to estimate the  memory required for the  autovacuum if we
   increase the number of rows ? Is it linear ? Logarithmic ? 
   
 
   It should use up to maintenance_work_mem. Depends on how much memory you
   set on this parameter.
 
 So, it shouldn't  depend on data size ?

Nope, it shouldn't.

  Is  there a fixed multiplicative
 factor between maintenance_work_mem and the memory actually used ?
 

1 :)

   Also, is  there a way  to reduce that  memory usage ?
 
   Reduce maintenance_work_mem. Of course, if you do that, VACUUM could
   take a lot longer to execute.
 
   Would running the autovacuum more frequently lower its memory usage ?
   
 
   Yes.
 
 Thanks, we'll try that.
 

I don't quite understand how you can get up to 1GB used by your process.
According to your configuration, and unless I'm wrong, it shouldn't take
more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find
this number?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Memory usage of auto-vacuum

[Guillaume Lelarge]

On Sat, 2011-07-09 at 11:00 +0200, Gael Le Mignot wrote:
 Hello Guillaume!
 
 Sat, 09 Jul 2011 10:53:14 +0200, you wrote: 
 
   I don't quite understand how you can get up to 1GB used by your process.
   According to your configuration, and unless I'm wrong, it shouldn't take
   more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find
   this number?
 
 Looking at  top we  saw the postgres  process growing and  growing and
 then shrinking  back, and doing  a select * from  pg_stat_activity; in
 parallel of the growing we found only the vacuum analyze query running. 
 

There is not only one postgres process. So you first need to be sure
that it's the one that executes the autovacuum.

 But maybe  we drawn the conclusion  too quickly, I'll  try disabling the
 auto vacuum to see if we really get rid of the problem doing it.
 

Disabling the autovacuum is usually a bad idea. You'll have to execute
VACUUM/ANALYZE via cron, which could get hard to configure.

BTW, what's your PostgreSQL release? I assume at least 8.3 since you're
using FTS?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [GENERAL] DELETE taking too much memory

[Guillaume Lelarge]

On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
 Hi,
 
 I have a delete query taking 7.2G of ram (and counting) but I do not
 understant why so much memory is necessary. The server has 12G, and
 I'm afraid it'll go into swap. Using postgres 8.3.14.
 
 I'm purging some old data from table t1, which should cascade-delete
 referencing rows in t2. Here's an anonymized rundown :
 
 
 # \d t1
  Table public.t1
   Column   |Type | Modifiers
 ---+-+-
  t1id  | integer | not null default
 nextval('t1_t1id_seq'::regclass)
 (...snip...)
 Indexes:
 message_pkey PRIMARY KEY, btree (id)
 (...snip...)
 
 # \d t2
Table public.t2
  Column  |Type |Modifiers
 -+-+-
  t2id| integer | not null default
 nextval('t2_t2id_seq'::regclass)
  t1id| integer | not null
  foo | integer | not null
  bar | timestamp without time zone | not null default now()
 Indexes:
 t2_pkey PRIMARY KEY, btree (t2id)
 t2_bar_key btree (bar)
 t2_t1id_key btree (t1id)
 Foreign-key constraints:
 t2_t1id_fkey FOREIGN KEY (t1id) REFERENCES t1(t1id) ON UPDATE
 RESTRICT ON DELETE CASCADE
 
 # explain delete from t1 where t1id in (select t1id from t2 where
 foo=0 and bar  '20101101');
QUERY PLAN
 -
  Nested Loop  (cost=5088742.39..6705282.32 rows=30849 width=6)
-  HashAggregate  (cost=5088742.39..5089050.88 rows=30849 width=4)
  -  Index Scan using t2_bar_key on t2  (cost=0.00..5035501.50
 rows=21296354 width=4)
Index Cond: (bar  '2010-11-01 00:00:00'::timestamp
 without time zone)
Filter: (foo = 0)
-  Index Scan using t1_pkey on t1  (cost=0.00..52.38 rows=1 width=10)
  Index Cond: (t1.t1id = t2.t1id)
 (7 rows)
 
 
 Note that the estimate of 30849 rows is way off : there should be
 around 55M rows deleted from t1, and 2-3 times as much from t2.
 
 When looking at the plan, I can easily imagine that data gets
 accumulated below the nestedloop (thus using all that memory), but why
 isn't each entry freed once one row has been deleted from t1 ? That
 entry isn't going to be found again in t1 or in t2, so why keep it
 around ?
 
 Is there a better way to write this query ? Would postgres 8.4/9.0
 handle things better ?
 

Do you have any DELETE triggers in t1 and/or t2?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Where does data in pg_stat_user_tables come from?

[Guillaume Lelarge]

Le 16/09/2010 20:39, Josh Berkus a écrit :
 
 It's been pure nonsense in this thread.  Please show an example of
 what's not working.
 
 1) Init a postgresql 8.3 with autovacuum disabled.
 
 2) Load a backup of a database into that PostgreSQL.
 
 3) Check pg_stat_user_tables.  n_live_tup for all tables will be 0.
 
 4) VACUUM ANALYZE the whole database.
 
 5) n_live_tup will *still* be 0.  Whereas reltuples in pg_class will be
 reasonable accurate.
 

Did all your steps (except the fourth one). Works great (meaning
n_live_tup is updated as it should be).

I have to agree with Alvarro, this is complete nonsense. VACUUM ANALYZE
doesn't change the pg_stat_*_tables columns value, the stats collector does.

If your n_live_tup didn't get updated, I'm quite sure you have
track_counts to off in your postgresql.conf file.

 Um ... it updates the last_autovacuum and last_autoanalyze columns,
 but the others are not its responsibility.
 
 Right. I'm contending that ANALYZE *should* update those columns.

The postgres process executing ANALYZE surely sent this information to
the stats collector (once again, if track_counts is on). Tried it
tonight, works great too.

 Current behavior is unintuitive and makes the stats in
 pg_stat_user_tables almost useless, since you can never get even
 approximately a coherent snapshot of data for all tables.
 

Get a look at your track_count setting.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Are Indices automatically generated for primary keys?

[Guillaume Lelarge]

Le 18/08/2010 17:23, Thom Brown a écrit :
 On 18 August 2010 17:06, Justin Graf jus...@magwerks.com wrote:
 On 8/18/2010 9:15 AM, Clemens Eisserer wrote:
 Hi,


 they are generated automatically.

 Thanks depesz!
 The reason why I asked was because pgAdmin doesn't display the
 automatically created indices, which confused me.

 Thanks, Clemens

 PGAdmin caches all database layout locally, the tree view can get very
 stale.  So refresh the treeview with either F5 or right click an item in
 the treeview click refresh to rebuild the list.

 
 I don't think PgAdmin will display indexes created by primary keys,
 only if indisprimary is false.
 

pgAdmin doesn't display indexes for primary keys and unique constraints.
These objects are already displayed in the constraints nodes. The fact
that they use an index to enforce the constraints is an implementation
detail.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How much memory is PostgreSQL using

[Guillaume Lelarge]

Le 02/04/2010 22:10, Campbell, Lance a écrit :
 Greg,
 Thanks for your help.
 
 1) How does the number of buffers provided by pg_buffercache compare to
 memory (buffers * X = Y meg)?  

1 buffer is 8 KB.

 2) Is there a way to tell how many total buffers I have available/max?

With pg_buffercache, yes.

SELECT count(*)
FROM pg_buffercache
WHERE relfilenode IS NOT NULL;

should give you the number of non-empty buffers.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Big index sizes

[Guillaume Lelarge]

Laszlo Nagy a écrit :
 We have serveral table where the index size is much bigger than the
 table size.
 
 Example:
 
 select count(*) from product_price -- 2234244
 
 Table size: 400 MB
 Index size: 600 MB
 
 After executing reindex table product_price, index size reduced to 269MB.
 
 I believe this affects performance.
 
 Vacuuming a table does not rebuild the indexes, am I right?

Neither VACUUM nor VACUUM FULL rebuild the indexes. CLUSTER and REINDEX do.

 I'm not sure
 if I need to do this manually, or is this the result of another problem?

You need to do this manually.

 (For example, too many open transactions, frequent updates?)
 

Regards.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Backup strategies

[Guillaume Lelarge]

Ivan Voras a écrit :
 Jesper Krogh wrote:
[...]
 It worked when I tested it, but I may just have been darn lucky.
 
 No, it should be ok - I just didn't catch up with the times :) At least
 that's my interpretation of this paragraph in documentation:
 
 Some backup tools that you might wish to use emit warnings or errors
 if the files they are trying to copy change while the copy proceeds.
 This situation is normal, and not an error, when taking a base backup of
 an active database; so you need to ensure that you can distinguish
 complaints of this sort from real errors...
 
 It looks like PostgreSQL freezes the state of the data directory in
 this case (and new data goes only to the transaction log - pg_xlog),
 which effectively creates an application-level snapshot. Good to know.
 

Nope. Even files in data directory change. That's why the documentation
warns against tools that emit errors for files that change during the copy.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] too many clog files

[Guillaume Lelarge]

Greg Smith a écrit :
 [...]
 - When, or in what case is  a new clog file produced?
 
 Every 32K transactions.

Are you sure about this?

y clog files get up to 262144 bytes. Which means 100 transactions'
status: 262144 bytes are 2Mb (mega bits), so if a status is 2 bits, it
holds 1M transactions' status).

AFAICT, 32K transactions' status are available on a single (8KB) page.

Or am I wrong?


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] logging options...

[Guillaume Lelarge]

Jessica Richard a écrit :
 for a short test purpose, I would like to see what queries are running
 and how long each of them takes.by reconfiguring postgres.conf on
 the server level.
 
 log_statement = 'all'  is giving me the query statements.. but I don't
 know where I can turn timing on just like what I can run from the
 command line \timing'to measure how long each of the queries takes
 to finish...
 

Either you configure log_statement to all, ddl or mod and log_duration
to on, either you configure log_min_duration_statement to 0.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Database size Vs performance degradation

[Guillaume Lelarge]

Dave North a écrit :

[...]

I'd suggest re-tuning as follows:

1) Increase shared_buffers to 10,000, test.  Things should be 
a bit faster.


2) Increase checkpoint_segments to 30, test.  What you want 
to watch for here whether there are periods where the server 
seems to freeze for a couple of seconds.  That's a 
checkpoint spike.  If this happens, reduce 
checkpoint_segments to some sort of middle ground; some 
people never get above 10 before it's a problem.


3) Increase shared_buffers in larger chunks, as long as you 
don't see any problematic spikes you might usefully keep 
going until it's set to at least 100,000 before improvements 
level off.


Do you happen to know if these are reload or restart tunable
parameters?  I think I've read somewhere before that they are restart
parameters (assuming I've set SHMMAX high enough of course)



shared_buffers and checkpoint_segments both need a restart.

[...]
I have to say, I've learnt a whole load from you folks here this
morning...very enlightening.  I'm now moving on to your site Greg! :)



There's much to learn from Greg's site. I was kinda impressed by all the 
good articles in it.



--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimizing PostgreSQL for Windows

[Guillaume Lelarge]

Christian Rengstl a écrit :
 My OS is Windows 2003 with 4GB Ram and Xeon Duo with 3.2 GHz;
 shared_buffers is set to 32MB (as I read it should be fairly low on
 Windows) and work_mem is set to 2500MB, but nevertheless the query takes
 about 38 seconds to finish. The table table1 contains approx. 3
 million tuples and table2 approx. 500.000 tuples. If anyone could give
 an advice on either how to optimize the settings in postgresql.conf or
 anything else to make this query run faster, I really would appreciate.
 

32MB for shared_buffers seems really low to me but 2500MB for work_mem
seems awfully high. The highest I've seen for work_mem was something
like 128MB. I think the first thing you have to do is to really lower
work_mem. Something like 64MB seems a better bet at first.

Regards.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Table Size

[Guillaume Lelarge]

Richard Huxton a écrit :
 Gauri Kanekar wrote:
 Hi,

 Can anybody help me out to get following info of all the tables in a
 database.
 
 1. Have you read up on the information schema and system catalogues?
 http://www.postgresql.org/docs/8.2/static/catalogs.html
 http://www.postgresql.org/docs/8.2/static/catalogs.html
 
 
 table_len
 tuple_count
 tuple_len
 
 2. Not sure what the difference is between len and count here.
 

tuple_count is the number of live tuples. tuple_len is the length (in
bytes) for all live tuples.

 tuple_percent
 
 3. Or what this percent refers to.
 

tuple_percent is % of live tuple from all tuples in a table.

 dead_tuple_count
 dead_tuple_len
 dead_tuple_percent
 free_space
 free_percent
 
 4. You might find some of the stats tables useful too:
 http://www.postgresql.org/docs/8.2/static/monitoring-stats.html
 

Actually, these columns refer to the pgstattuple contrib module. This
contrib module must be installed on the server (how you install it
depends on your distro). Then, you have to create the functions on you
database :
  psql -f /path/to/pgstattuple.sql your_database

Right after that, you can query these columns :

test= \x
Expanded display is on.
test= SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]--+---
table_len  | 458752
tuple_count| 1470
tuple_len  | 438896
tuple_percent  | 95.67
dead_tuple_count   | 11
dead_tuple_len | 3157
dead_tuple_percent | 0.69
free_space | 8932
free_percent   | 1.95

Example from README.pgstattuple.

Regards.


-- 
Guillaume.
!-- http://abs.traduc.org/
 http://lfs.traduc.org/
 http://docs.postgresqlfr.org/ --

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org