pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSame query, same performance
5+ messages / 3 participants
[nested] [flat]
* Same query, same performance
@ 2003-01-23 02:26 alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
0 siblings, 2 replies; 5+ messages in thread
From: alexandre :: aldeia digital @ 2003-01-23 02:26 UTC (permalink / raw)
To: pgsql-performance
Hi all,
First, sorry for the long mail...
I have a system with 7 Million of records in 600 tables.
My actual production machine is: P4 1.6G, 3 IDE 7200, 1GB PC133
My new machine production is: Dual Xeon 2.0G HT, 1GB DDR266 ECC
3 SCSI with HW Raid 5
The postgresql.conf is the SAME in both systems and I test
with no other connections, only my local test.
shared_buffers = 80000
effective_cache_size = 60000
random_page_cost = 2.5
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0001
cpu_operator_cost = 0.00025
My question is:
If I execute the same query executed a lot of times, the
duration is praticaly the same in both systems ?
1) ! 1.185424 elapsed 1.090000 user 0.100000 system sec
2) ! 1.184415 elapsed 1.070000 user 0.120000 system sec
3) ! 1.185209 elapsed 1.100000 user 0.080000 system sec
If the disks is not read directly, the system must find
the rows in RAM. If it find in RAM, why so diffrents machines
have the times of execution and why the times does not down ???
The variations of query show bellow have the times pratically
equals and my system send thousands os this querys with a
thousands of 1.18 seconds... :(
Very thank´s
Alexandre
Query:
[postgres@host1 data]$ psql -c "explain SELECT T2.fi15emp05,
T2.fi15flagcf, T2.fi15codcf, T1.Fn06Emp07, T1.Fn06TipTit, T1.Fn06TitBan,
T1.Fn06Conta1, T1.Fn06NumTit, T1.Fn06Desdob, T1.Fn05CodPre, T1.Fn06eCli1,
T1.Fn06tCli1, T1.Fn06cCli1, T2.fi15nome FROM (FN06T T1 LEFT JOIN FI15T
T2 ON T2.fi15emp05 = T1.Fn06eCli1 AND T2.fi15flagcf = T1.Fn06tCli1 AND
T2.fi15codcf = T1.Fn06cCli1) WHERE ( T1.Fn06Emp07 = '1' AND
T1.Fn06TipTit = 'R' ) AND ( T1.Fn06TitBan = '002021001525
' ) ORDER BY T1.Fn06Emp07, T1.Fn06TipTit, T1.Fn06NumTit, T1.Fn06Desdob,
T1.Fn05CodPre, T1.Fn06eCli1, T1.Fn06tCli1, T1.Fn06cCli1" Pro13Z
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=25875.53..25875.53 rows=15 width=155)
Sort Key: t1.fn06emp07, t1.fn06tiptit, t1.fn06numtit, t1.fn06desdob,
t1.fn05codpre, t1.fn06ecli1, t1.fn06tcli1, t1.fn06ccli1
-> Nested Loop (cost=0.00..25875.50 rows=15 width=155)
-> Seq Scan on fn06t t1 (cost=0.00..25808.30 rows=15 width=95)
Filter: ((fn06emp07 = 1::smallint) AND (fn06tiptit =
'R'::bpchar) AND (fn06titban = '002021001525
'::bpchar))
-> Index Scan using fi15t_pkey on fi15t t2 (cost=0.00..4.33
rows=1 width=60)
Index Cond: ((t2.fi15emp05 = "outer".fn06ecli1) AND
(t2.fi15flagcf = "outer".fn06tcli1) AND (t2.fi15codcf =
"outer".fn06ccli1))
(7 rows)
*** AND FROM LOG when a execute the query:
2003-01-23 00:09:49 [3372] LOG: duration: 1.285900 sec
2003-01-23 00:09:49 [3372] LOG: QUERY STATISTICS
! system usage stats:
! 1.286001 elapsed 1.240000 user 0.040000 system sec
! [1.250000 user 0.040000 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 50526/130 [50693/372] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! buffer usage stats:
! Shared blocks: 0 read, 0 written, buffer hit
rate = 100.00%
! Local blocks: 0 read, 0 written, buffer hit
rate = 0.00%
! Direct blocks: 0 read, 0 written
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Same query, same performance
@ 2003-01-23 08:20 Tomasz Myrta <jasiek@klaster.net>
parent: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
1 sibling, 1 reply; 5+ messages in thread
From: Tomasz Myrta @ 2003-01-23 08:20 UTC (permalink / raw)
To: pgsql-performance
alexandre :: aldeia digital wrote:
>Hi all,
>
>First, sorry for the long mail...
>
>I have a system with 7 Million of records in 600 tables.
>My actual production machine is: P4 1.6G, 3 IDE 7200, 1GB PC133
>My new machine production is: Dual Xeon 2.0G HT, 1GB DDR266 ECC
>3 SCSI with HW Raid 5
>
>The postgresql.conf is the SAME in both systems and I test
>with no other connections, only my local test.
>
>shared_buffers = 80000
>effective_cache_size = 60000
>random_page_cost = 2.5
>cpu_tuple_cost = 0.001
>cpu_index_tuple_cost = 0.0001
>cpu_operator_cost = 0.00025
>
>My question is:
>
>If I execute the same query executed a lot of times, the
>duration is praticaly the same in both systems ?
>
>1) ! 1.185424 elapsed 1.090000 user 0.100000 system sec
>2) ! 1.184415 elapsed 1.070000 user 0.120000 system sec
>3) ! 1.185209 elapsed 1.100000 user 0.080000 system sec
>
>If the disks is not read directly, the system must find
>the rows in RAM. If it find in RAM, why so diffrents machines
>have the times of execution and why the times does not down ???
Here is your problem:
-> Seq Scan on fn06t t1 (cost=0.00..25808.30 rows=15 width=95)
Filter: ((fn06emp07 = 1::smallint) AND (fn06tiptit =
'R'::bpchar) AND (fn06titban = '002021001525
'::bpchar))
Problably system has to read from disk whole table fn06t each time, beacuse it
doesn't use index scan.
Do you have any indexes on table fn06t? How selective are conditions above
How big is this table? Can you use indexes on multiple fields on this table
- it should help, because conditions above return only 15 rows?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Same query, same performance
@ 2003-01-23 17:00 Josh Berkus <josh@agliodbs.com>
parent: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-01-23 17:00 UTC (permalink / raw)
To: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>; pgsql-performance
Alexandre,
> I have a system with 7 Million of records in 600 tables.
> My actual production machine is: P4 1.6G, 3 IDE 7200, 1GB PC133
> My new machine production is: Dual Xeon 2.0G HT, 1GB DDR266 ECC
> 3 SCSI with HW Raid 5
Well, first of all, those two systems are almost equivalent as far as
Postgres is concerned for simple queries. The extra processor power
will only help you with very complex queries. 3-disk RAID 5 is no
faster ... and sometimes slower ... than IDE for database purposes.
The only real boost to the Xeon is the faster RAM ... which may not
help you if your drive array is the bottleneck.
>
> The postgresql.conf is the SAME in both systems and I test
> with no other connections, only my local test.
>
> shared_buffers = 80000
> effective_cache_size = 60000
> random_page_cost = 2.5
> cpu_tuple_cost = 0.001
> cpu_index_tuple_cost = 0.0001
> cpu_operator_cost = 0.00025
Not that it affects the query below, but what about SORT_MEM?
> If I execute the same query executed a lot of times, the
> duration is praticaly the same in both systems ?
>
> 1) ! 1.185424 elapsed 1.090000 user 0.100000 system sec
> 2) ! 1.184415 elapsed 1.070000 user 0.120000 system sec
> 3) ! 1.185209 elapsed 1.100000 user 0.080000 system sec
>
> If the disks is not read directly, the system must find
> the rows in RAM. If it find in RAM, why so diffrents machines
> have the times of execution and why the times does not down ???
I'm pretty sure that PostgreSQL always checks on disk, even when the
same query is run repeatedly. Tom?
> [postgres@host1 data]$ psql -c "explain SELECT T2.fi15emp05,
> T2.fi15flagcf, T2.fi15codcf, T1.Fn06Emp07, T1.Fn06TipTit,
> T1.Fn06TitBan,
> T1.Fn06Conta1, T1.Fn06NumTit, T1.Fn06Desdob, T1.Fn05CodPre,
> T1.Fn06eCli1,
> T1.Fn06tCli1, T1.Fn06cCli1, T2.fi15nome FROM (FN06T T1 LEFT JOIN
> FI15T
> T2 ON T2.fi15emp05 = T1.Fn06eCli1 AND T2.fi15flagcf = T1.Fn06tCli1
> AND
> T2.fi15codcf = T1.Fn06cCli1) WHERE ( T1.Fn06Emp07 = '1' AND
> T1.Fn06TipTit = 'R' ) AND ( T1.Fn06TitBan = '002021001525
>
> ' ) ORDER BY T1.Fn06Emp07, T1.Fn06TipTit, T1.Fn06NumTit,
> T1.Fn06Desdob,
> T1.Fn05CodPre, T1.Fn06eCli1, T1.Fn06tCli1, T1.Fn06cCli1" Pro13Z
Actually, from your stats, Postgres is doing a pretty good job. 1.18
seconds to return 15 rows from a 7 million row table searching on not
Indexed columns? I don't think you have anything to complain about.
If you want less-than-1 second respose time: Add some indexes and keep
the tables VACUUMed so the indexes work. Particularly, add a
multi-column index on ( T1.Fn06Emp07, T1.Fn06TipTit, T1.Fn06TitBan )
If you want single-digit-msec response: Get a better disk set for
Postgres: I recommend dual-channel RAID 1 (n addition to indexing).
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Same query, same performance
@ 2003-01-23 19:49 alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 5+ messages in thread
From: alexandre :: aldeia digital @ 2003-01-23 19:49 UTC (permalink / raw)
To: pgsql-performance
Tomasz,
>>1) ! 1.185424 elapsed 1.090000 user 0.100000 system sec
>>2) ! 1.184415 elapsed 1.070000 user 0.120000 system sec
>>3) ! 1.185209 elapsed 1.100000 user 0.080000 system sec
>>
>>If the disks is not read directly, the system must find
>>the rows in RAM. If it find in RAM, why so diffrents machines
>>have the times of execution and why the times does not down ???
>
> Here is your problem:
> -> Seq Scan on fn06t t1 (cost=0.00..25808.30 rows=15 width=95)
> Filter: ((fn06emp07 = 1::smallint) AND (fn06tiptit =
> 'R'::bpchar) AND (fn06titban = '002021001525
> '::bpchar))
Really! I do not attemp that fn06t does not have an index
with fn06titban ... :)
Now, tehe time of the querys are < 0.02 sec on P4
and <0.05 on Xeon.
Very Thank´s
Alexandre,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Same query, same performance
@ 2003-01-23 20:31 alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: alexandre :: aldeia digital @ 2003-01-23 20:31 UTC (permalink / raw)
To: pgsql-performance
Josh,
> Alexandre,
>
>> I have a system with 7 Million of records in 600 tables.
>> My actual production machine is: P4 1.6G, 3 IDE 7200, 1GB PC133
>> My new machine production is: Dual Xeon 2.0G HT, 1GB DDR266 ECC
>> 3 SCSI with HW Raid 5
>
> Well, first of all, those two systems are almost equivalent as far as
> Postgres is concerned for simple queries. The extra processor power
> will only help you with very complex queries. 3-disk RAID 5 is no
> faster ... and sometimes slower ... than IDE for database purposes.
> The only real boost to the Xeon is the faster RAM ... which may not
> help you if your drive array is the bottleneck.
Today, I will add more one HD and I will make an RAID 10 ...
In next week i will report my tests to the list...
>
>>
>> The postgresql.conf is the SAME in both systems and I test
>> with no other connections, only my local test.
>>
>> shared_buffers = 80000
>> effective_cache_size = 60000
>> random_page_cost = 2.5
>> cpu_tuple_cost = 0.001
>> cpu_index_tuple_cost = 0.0001
>> cpu_operator_cost = 0.00025
>
> Not that it affects the query below, but what about SORT_MEM?
Sort_mem = 32000
> Actually, from your stats, Postgres is doing a pretty good job. 1.18
> seconds to return 15 rows from a 7 million row table searching on not
> Indexed columns? I don't think you have anything to complain about.
The table have 300000 tuples, the entire database have 7 million.
Tomazs answer the question: a missing index on fn06t ...
But the query time difference of the systems continue.
I will change the discs and tell to list after...
Thank´s Josh,
Alexandre
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-01-23 20:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-23 02:26 Same query, same performance alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
2003-01-23 08:20 ` Tomasz Myrta <jasiek@klaster.net>
2003-01-23 19:49 ` alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
2003-01-23 17:00 ` Josh Berkus <josh@agliodbs.com>
2003-01-23 20:31 ` alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox