From: Oliver Crosby <ryusei@gmail.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Looking for tips
Date: Tue, 19 Jul 2005 14:21:22 -0400
Message-ID: <1efd553a050719112126b8d981@mail.gmail.com> (raw)
In-Reply-To: <42DD3D55.9020508@commandprompt.com>
References: <1efd553a05071910427b290203@mail.gmail.com>
<42DD3D55.9020508@commandprompt.com>
I was hoping to start with tuning postgres to match the hardware, but
in any case..
The queries are all simple insert or select statements on single tables.
Eg. select x from table where y=?; or insert into table (a, b, c)
values (?, ?, ?);
In the case of selects where it's a large table, there's an index on
the column being searched, so in terms of the example above, x is
either a pkey column or other related field, and y is a non-pkey
column.
I'm not sure what you mean by structure.
I tried explain analyse on the individual queries, but I'm not sure
what can be done to manipulate them when they don't do much.
My test environment has about 100k - 300k rows in each table, and for
production I'm expecting this to be in the order of 1M+.
The OS is Redhat Enterprise 3.
I'm using a time command when I call the scripts to get a total
running time from start to finish.
I don't know what we have for RAID, but I suspect it's just a single
10k or 15k rpm hdd.
------------------------------------------------------------------------------------------------------------------------
I'll try your recommendations for shared_buffers and
effective_cache_size. Thanks John!
We're trying to improve performance on a log processing script to the
point where it can be run as close as possible to realtime. A lot of
what gets inserted depends on what's already in the db, and it runs
item-by-item... so unfortunately I can't take advantage of copy.
We tried dropping indices, copying data in, then rebuilding. It works
great for a bulk import, but the processing script went a lot slower
without them. (Each insert is preceeded by a local cache check and
then a db search to see if an ID already exists for an item.)
We have no foreign keys at the moment. Would they help?
On 7/19/05, Joshua D. Drake <jd@commandprompt.com> wrote:
> Oliver Crosby wrote:
> > Hi,
> > I'm running Postgres 7.4.6 on a dedicated server with about 1.5gigs of ram.
> > Running scripts locally, it takes about 1.5x longer than mysql, and the
> > load on the server is only about 21%.
>
> What queries?
> What is your structure?
> Have you tried explain analyze?
> How many rows in the table?
> Which OS?
> How are you testing the speed?
> What type of RAID?
>
>
>
> --
> Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
> PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ryusei@gmail.com, jd@commandprompt.com
Subject: Re: Looking for tips
In-Reply-To: <1efd553a050719112126b8d981@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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