Skip site navigation (1) Skip section navigation (2)

Re: Abnormal performance difference between Postgres and MySQL

From: Farhan Husain <russoue(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Abnormal performance difference between Postgres and MySQL
Date: 2009-02-25 19:05:20
Message-ID: 3df32b6d0902251105n359cd198y3bfda1113ab1d6fb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Feb 25, 2009 at 12:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Just start up psql and type:
>
> show work_mem;
>
> (You could look in the config file too I suppose.)
>
> ...Robert
>
> On Wed, Feb 25, 2009 at 1:53 PM, Farhan Husain <russoue(at)gmail(dot)com> wrote:
> >
> >
> > On Wed, Feb 25, 2009 at 12:49 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> >>
> >> You still haven't answered the work_mem question, and you probably
> >> want to copy the list, rather than just sending this to me.
> >>
> >> ...Robert
> >>
> >> On Wed, Feb 25, 2009 at 1:34 PM, Farhan Husain <russoue(at)gmail(dot)com>
> wrote:
> >> >
> >> >
> >> > On Tue, Feb 24, 2009 at 8:21 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> >> > wrote:
> >> >>
> >> >> >> Can you please elaborate a bit?
> >> >> >
> >> >> > I thought that A0.Prop would ignore the composite index created on
> >> >> > the
> >> >> > columns subj and prop but this does not seem to be the case.
> >> >>
> >> >> Yeah, I think you're barking up the wrong tree here.  I think Tom had
> >> >> the correct diagnosis - what do you get from "show work_mem"?
> >> >>
> >> >> What kind of machine are you running this on?  If it's a UNIX-ish
> >> >> machine, what do you get from "free -m"and "uname -a"?
> >> >>
> >> >> ...Robert
> >> >
> >> > Here is the machine info:
> >> >
> >> > Machine: SunOS 5.10 Generic_127111-11 sun4u sparc SUNW, Sun-Fire-880
> >> > Memory: 4 GB
> >> > Number of physical processors: 2
> >> >
> >> >
> >> > --
> >> > Mohammad Farhan Husain
> >> > Research Assistant
> >> > Department of Computer Science
> >> > Erik Jonsson School of Engineering and Computer Science
> >> > University of Texas at Dallas
> >> >
> >
> > Did you mean the work_mem field in the config file?
> >
> >
> > --
> > Mohammad Farhan Husain
> > Research Assistant
> > Department of Computer Science
> > Erik Jonsson School of Engineering and Computer Science
> > University of Texas at Dallas
> >
>

I did it, it does not show anything. Here is what I have got from the config
file:


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 32MB                   # min 128kB or max_connections*16kB
                                        # (change requires restart)
temp_buffers = 1024MB                   # min 800kB
#max_prepared_transactions = 5          # can be 0 or more
                                        # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 1792MB                               # min 64kB
#maintenance_work_mem = 16MB            # min 1MB
#max_stack_depth = 32MB                 # min 100kB

# - Free Space Map -

max_fsm_pages = 204800                  # min max_fsm_relations*16, 6 bytes
each
                                        # (change requires restart)
#max_fsm_relations = 1000               # min 100, ~70 bytes each
                                        # (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000           # min 25
                                        # (change requires restart)
#shared_preload_libraries = ''          # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0                  # 0-1000 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200                # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multipler on buffers
scanned/round


Please note that this (1792MB) is the highest that I could set for work_mem.
-- 
Mohammad Farhan Husain
Research Assistant
Department of Computer Science
Erik Jonsson School of Engineering and Computer Science
University of Texas at Dallas

In response to

Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2009-02-25 19:23:07
Subject: Re: PostgreSQL block size for SSD RAID setup?
Previous:From: Robert HaasDate: 2009-02-25 18:58:34
Subject: Re: Abnormal performance difference between Postgres and MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group