Re: Abnormal performance difference between Postgres and MySQL - Mailing list pgsql-performance
| From | Farhan Husain |
|---|---|
| Subject | Re: Abnormal performance difference between Postgres and MySQL |
| Date | |
| Msg-id | 3df32b6d0902251429r78b5adecn28bd8bf20978409f@mail.gmail.com Whole thread Raw |
| In response to | Re: Abnormal performance difference between Postgres and MySQL ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
| Responses |
Re: Abnormal performance difference between Postgres
and MySQL
Re: Abnormal performance difference between Postgres and MySQL Re: Abnormal performance difference between Postgres and MySQL |
| List | pgsql-performance |
On Wed, Feb 25, 2009 at 4:10 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>>> Farhan Husain <russoue@gmail.com> wrote:> The machine postgres is running on has 4 GB of RAM.In addition to the other suggestions, you should be sure that
effective_cache_size is set to a reasonable value, which would
probably be somewhere in the neighborhood of '3GB'. This doesn't
affect actual RAM allocation, but gives the optimizer a rough idea how
much data is going to be kept in cache, between both the PostgreSQL
shared_memory setting and the OS cache. It can make better choices
with more accurate information.
-Kevin
Here is the latest output:
ingentadb=# EXPLAIN ANALYZE select A0.Subj, A2.Obj From jena_g1t1_stmt A0, jena_g1t1_stmt A1, jena_g1t1_stmt A2 Where A0.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf' AND A0.Obj='Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1' AND A0.GraphID=1 AND A0.Subj=A1.Subj AND A1.Prop='Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AND A1.Obj='Uv::http://metastore.ingenta.com/ns/structure/Article' AND A1.GraphID=1 AND A0.Subj=A2.Subj AND A2.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage' AND A2.GraphID=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=799852.37..812767.47 rows=733195 width=134) (actual time=5941553.710..5941569.192 rows=30 loops=1)
Merge Cond: ((a0.subj)::text = (a1.subj)::text)
-> Sort (cost=89884.41..89964.28 rows=31949 width=208) (actual time=243.711..243.731 rows=30 loops=1)
Sort Key: a0.subj
Sort Method: quicksort Memory: 24kB
-> Nested Loop (cost=0.00..84326.57 rows=31949 width=208) (actual time=171.255..232.765 rows=30 loops=1)
-> Index Scan using jena_g1t1_stmt_ixo on jena_g1t1_stmt a0 (cost=0.00..5428.34 rows=487 width=74) (actual time=96.735..97.070 rows=30 loops=1)
Index Cond: ((obj)::text = 'Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1'::text)
Filter: (((prop)::text = 'Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf'::text) AND (graphid = 1))
-> Index Scan using jena_g1t1_stmt_ixsp on jena_g1t1_stmt a2 (cost=0.00..161.37 rows=51 width=134) (actual time=4.513..4.518 rows=1 loops=30)
Index Cond: (((a2.subj)::text = (a0.subj)::text) AND ((a2.prop)::text = 'Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage'::text))
Filter: (a2.graphid = 1)
-> Materialize (cost=709967.96..723526.46 rows=1084680 width=74) (actual time=5941309.876..5941318.552 rows=31 loops=1)
-> Sort (cost=709967.96..712679.66 rows=1084680 width=74) (actual time=5941309.858..5941318.488 rows=31 loops=1)
Sort Key: a1.subj
Sort Method: external merge Disk: 282480kB
-> Seq Scan on jena_g1t1_stmt a1 (cost=0.00..456639.59 rows=1084680 width=74) (actual time=0.054..44604.597 rows=3192000 loops=1)
Filter: ((graphid = 1) AND ((prop)::text = 'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type'::text) AND ((obj)::text = 'Uv::http://metastore.ingenta.com/ns/structure/Article'::text))
Total runtime: 5941585.248 ms
(19 rows)
ingentadb=# show work_mem;
work_mem
----------
1MB
(1 row)
ingentadb=# show shared_buffers;
shared_buffers
----------------
32MB
(1 row)
ingentadb=# show temp_buffers;
temp_buffers
--------------
131072
(1 row)
The execution time has not improved. I am going to increase the shared_buffers now keeping the work_mem same.
--
Mohammad Farhan Husain
Research Assistant
Department of Computer Science
Erik Jonsson School of Engineering and Computer Science
University of Texas at Dallas
pgsql-performance by date: