Re: [PERFORM] Selects query stats?

2006-05-24 Thread Robin Ericsson 

On 5/23/06, Dan Gorman <[EMAIL PROTECTED]> wrote:


In any other DB (oracle, mysql) I know how many queries (selects) per second
the database is executing. How do I get this
number out of postgres?


Mysql does AFAIR only count the number of queries and then uses the
"seconds since startup" to estimate the number of queries per second.
If your server is hammered with queries 1 hour a day it's not giving
you a fair result.


--
   regards,
   Robin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[PERFORM] query problem

2004-10-13 Thread Robin Ericsson 
Hi,

I sent this to general earlier but I was redirected to performance.

The query have been running ok for quite some time, but after I did a
vacuum on the database, it's very very slow. This IN-query is only 2
ids. Before the problem that in was a subselect-query returning around
6-7 ids. The tables included in the query are described in database.txt.

status=# select count(id) from data;
  count
-
 1577621
(1 row)

status=# select count(data_id) from data_values;
  count
-
 9680931
(1 row)

I did run a new explain analyze on the query and found the attached
result. The obvious problem I see is a full index scan in
"idx_dv_data_id". I tried dropping and adding the index again, thats why
is't called "idx_data_values_data_id" in the dump.

status=# EXPLAIN ANALYZE
status-# SELECT
status-# data.entered,
status-# data.machine_id,
status-# datatemplate_intervals.template_id,
status-# data_values.value
status-# FROM
status-# data, data_values, datatemplate_intervals
status-# WHERE
status-# datatemplate_intervals.id = data_values.template_id AND
status-# data_values.data_id = data.id AND
status-# data.machine_id IN (2,3) AND
status-# current_timestamp::timestamp - interval '60 seconds' <
data.entered;



Regards,
Robin


-- 
Robin Ericsson <[EMAIL PROTECTED]>
Profecta HB
 Hash Join  (cost=28646.01..274260.15 rows=555706 width=24) (actual 
time=102323.087..102323.196 rows=5 loops=1)
   Hash Cond: ("outer".template_id = "inner".id)
   ->  Merge Join  (cost=28644.09..265922.62 rows=555706 width=24) (actual 
time=102322.632..102322.709 rows=5 loops=1)
 Merge Cond: ("outer".data_id = "inner".id)
 ->  Index Scan using idx_dv_data_id on data_values  (cost=0.00..205034.19 
rows=9580032 width=16) (actual time=17.503..86263.130 
rows=9596747 loops=1)
 ->  Sort  (cost=28644.09..28870.83 rows=90697 width=16) (actual 
time=0.829..0.835 rows=1 loops=1)
   Sort Key: data.id
   ->  Index Scan using idx_d_entered on data  (cost=0.00..20202.81 
rows=90697 width=16) (actual time=0.146..0.185 rows=1 loops=1)
 Index Cond: 'now'::text)::timestamp(6) with time 
zone)::timestamp without time zone - '00:01:00'::interval) < entered)
 Filter: ((machine_id = 2) OR (machine_id = 3))
   ->  Hash  (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382 rows=0 
loops=1)
 ->  Seq Scan on datatemplate_intervals  (cost=0.00..1.74 rows=74 width=8) 
(actual time=0.024..0.250 rows=74 loops=1)
 Total runtime: 102323.491 ms
(13 rows)

status=# \d data
  Table "public.data"
   Column   |Type | Modifiers
+-+
 id | integer | not null default nextval('data_seq'::text)
 updated| timestamp without time zone |
 entered| timestamp without time zone |
 machine_id | integer |
 datas  | character varying(512)[]|
Indexes:
"data_pkey" primary key, btree (id)
"idx_d_entered" btree (entered)
"idx_d_machine_id" btree (machine_id)
Foreign-key constraints:
"machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id)
Triggers:
data_datestamp BEFORE INSERT OR UPDATE ON data FOR EACH ROW EXECUTE PROCEDURE 
datestamp_e()

status=# \d data_values
  Table "public.data_values"
   Column|Type | Modifiers
-+-+---
 updated | timestamp without time zone |
 entered | timestamp without time zone |
 data_id | integer |
 template_id | integer |
 value   | character varying(512)  |
Indexes:
"idx_data_values_data_id" btree (data_id)
"idx_dv_template_id" btree (template_id)
Foreign-key constraints:
"data_id" FOREIGN KEY (data_id) REFERENCES data(id)
"template_id" FOREIGN KEY (template_id) REFERENCES datatemplate_intervals(id)
Triggers:
data_values_datestamp BEFORE INSERT OR UPDATE ON data_values FOR EACH ROW EXECUTE 
PROCEDURE 
datestamp_e()

status=# \d datatemplate_intervals
  Table "public.datatemplate_intervals"
   Column|Type |  Modifiers 
-+-+--
 id  | integer | not null default 
nextval('datatemplate_intervals_seq'::text)
 updated | timestamp without time zone |
 entered | timestamp without time zone |
 machine

Re: [PERFORM] query problem

2004-10-13 Thread Robin Ericsson 
On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote:
> Robin Ericsson <[EMAIL PROTECTED]> writes:
> > I sent this to general earlier but I was redirected to performance.
> 
> Actually, I think I suggested that you consult the pgsql-performance
> archives, where this type of problem has been hashed out before.
> See for instance this thread:
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00169.php
> particularly
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00175.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00184.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00185.php
> which show three different ways of getting the planner to do something
> sane with an index range bound like "now() - interval".

Using exact timestamp makes the query go back as it should in speed (see
explain below). However I still have the problem using a stored
procedure or even using the "ago"-example from above.




regards,
Robin

status=# explain analyse
status-# SELECT
status-# data.entered,
status-# data.machine_id,
status-# datatemplate_intervals.template_id,
status-# data_values.value
status-# FROM
status-# data, data_values, datatemplate_intervals
status-# WHERE
status-# datatemplate_intervals.id =
data_values.template_id AND
status-# data_values.data_id = data.id AND
status-# data.machine_id IN (SELECT machine_id FROM
machine_group_xref WHERE group_id = 1) AND
status-# '2004-10-13 17:47:36.902062' < data.entered
status-# ;

QUERY PLAN
--
 Hash Join  (cost=3.09..481.28 rows=777 width=24) (actual
time=0.637..1.804 rows=57 loops=1)
   Hash Cond: ("outer".template_id = "inner".id)
   ->  Nested Loop  (cost=1.17..467.71 rows=776 width=24) (actual
time=0.212..1.012 rows=57 loops=1)
 ->  Hash IN Join  (cost=1.17..9.56 rows=146 width=16) (actual
time=0.165..0.265 rows=9 loops=1)
   Hash Cond: ("outer".machine_id = "inner".machine_id)
   ->  Index Scan using idx_d_entered on data
(cost=0.00..6.14 rows=159 width=16) (actual time=0.051..0.097 rows=10
loops=1)
 Index Cond: ('2004-10-13
17:47:36.902062'::timestamp without time zone < entered)
   ->  Hash  (cost=1.14..1.14 rows=11 width=4) (actual
time=0.076..0.076 rows=0 loops=1)
 ->  Seq Scan on machine_group_xref
(cost=0.00..1.14 rows=11 width=4) (actual time=0.017..0.054 rows=11
loops=1)
   Filter: (group_id = 1)
 ->  Index Scan using idx_data_values_data_id on data_values
(cost=0.00..3.07 rows=5 width=16) (actual time=0.018..0.047 rows=6
loops=9)
   Index Cond: (data_values.data_id = "outer".id)
   ->  Hash  (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382
rows=0 loops=1)
 ->  Seq Scan on datatemplate_intervals  (cost=0.00..1.74
rows=74 width=8) (actual time=0.024..0.248 rows=74 loops=1)
 Total runtime: 2.145 ms
(15 rows)



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[Fwd: Re: [GENERAL] [PERFORM] query problem]

2004-10-13 Thread Robin Ericsson 
Sent this to wrong list.

 Forwarded Message 
From: Robin Ericsson <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] [PERFORM] query problem
Date: Wed, 13 Oct 2004 18:27:20 +0200
On Wed, 2004-10-13 at 18:01 +0200, Robin Ericsson wrote:
> Using exact timestamp makes the query go back as it should in speed (see
> explain below). However I still have the problem using a stored
> procedure or even using the "ago"-example from above.

Well, changing ago() to use timestamp without time zone it goes ok in
the query. This query now takes ~2ms.

SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref
WHERE group_id = 1) AND
ago('60 seconds') < data.entered

Using it in this procedure.
select * from get_current_machine_status('60 seconds', 1);
takes ~100s. Maybe there's some obvious wrong I do about it?

CREATE TYPE public.mstatus_holder AS
   (entered timestamp,
machine_id int4,
template_id int4,
value varchar);
CREATE OR REPLACE FUNCTION public.get_current_machine_status(interval,
int4)
  RETURNS SETOF mstatus_holder AS
'
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref WHERE
group_id = $2) AND
ago($1) < data.entered
'
  LANGUAGE 'sql' VOLATILE;


Regards,
Robin



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
-- 
Robin Ericsson <[EMAIL PROTECTED]>
Profecta HB


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

   http://archives.postgresql.org

Re: [PERFORM] Alternatives to Dell?

2004-12-02 Thread Robin Ericsson 
On Wed, 2004-12-01 at 14:24 -0800, Josh Berkus wrote:
> Folks,
> 
> A lot of people have been having a devilish time with Dell hardware lately.  
> It seems like the quality control just isn't there on the Dell servers.
> 
> Thing is, some companies are required to use 1st-tier or at least 2nd-tier 
> vendors for hardware; they won't home-build.   For those people, what vendors 
> do others on this list recommend?   What have been your good/bad experiences?


We use a bunch of HP ProLiant DL360 and DL380 without any problems at
all. 



regards,
Robin


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings