Does the order of columns of varying size have any effect on
SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where
an integer primary key is listed first in the table and alternatively
listed after some large varchar or text columns? For example, is this
different performance-w
Thank you.
How about:
select c.relname, l.pid, l.mode, l.granted,
a.current_query
from pg_locks l, pg_class c, pg_stat_activity a
where
l.relation = c.oid
AND l.pid = a.procpid
order by l.granted, l.pid;
relname | pid |
mode | granted |
On Sun, 8 Aug 2004, Matt Clark wrote:
> > And this is exactly where the pgpool advantage lies.
> > Especially with the
> > TPC-W, the Apache is serving a mix of PHP (or whatever CGI
> > technique is
> > used) and static content like images. Since the 200+ Apache
> > kids serve
> > any of that cont
> Anyway, with fsync enabled using standard fsync(), I get roughly
300-400
> inserts per second. With fsync disabled, I get about 7000 inserts per
> second. When I re-enable fsync but use the open_sync option, I can get
> about 2500 inserts per second.
You are getting 300-400 inserts/sec with fsyn
hi,
Paul Serby wrote:
Can anyone give a good reference site/book for getting the most out of
your postgres server.
All I can find is contradicting theories on how to work out your settings.
This is what I followed to setup our db server that serves our web
applications.
http://www.phpbuilder.co
Having trouble with one table (see time to count records below!).
Fairly new to postgres so any help much appreciated.
It only contains 9,106 records - as you can see from:
select count(id) from project
count
9106
1 row(s)
Total runtime: 45,778.813 ms
There are only 3 fields:
id
integer
nex
>> Anyway, with fsync enabled using standard fsync(), I get roughly
> 300-400
>> inserts per second. With fsync disabled, I get about 7000 inserts per
>> second. When I re-enable fsync but use the open_sync option, I can get
>> about 2500 inserts per second.
>
> You are getting 300-400 inserts/sec
Paul Langard <[EMAIL PROTECTED]> writes:
> Having trouble with one table (see time to count records below!).
>
> Fairly new to postgres so any help much appreciated.
>
> It only contains 9,106 records - as you can see from:
>
>
> select count(id) from project
>
> count
> 9106
> 1 row(s)
> Total ru
> Squid also takes away the work of doing SSL (presuming you're running it
> on a different machine). Unfortunately it doesn't support HTTP/1.1 which
> means that most generated pages (those that don't set Content-length) end
> up forcing squid to close and then reopen the connection to the web
>
Hi All,
We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4, 2GB
ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!).
Our database is about 20GB on disk, we have some quite large tables - 2M
rows with TEXT fields in a sample table, accessed constantly. We average
about 4,000 - 5,
Paul,
Paul Langard wrote:
Having trouble with one table (see time to count records below!).
Fairly new to postgres so any help much appreciated.
It only contains 9,106 records - as you can see from:
select count(id) from project
*count
*9106
1 row(s)
Total runtime: 45,778.813 ms
... the database i
Josh Berkus wrote:
Does the order of columns of varying size have any effect on
SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where
an integer primary key is listed first in the table and alternatively
listed after some large varchar or text columns?
No, the "order" of the c
Paul Langard <[EMAIL PROTECTED]> writes:
> select count(id) from project
> count
> 9106
> 1 row(s)
> Total runtime: 45,778.813 ms
Yipes. The only explanation I can think of is tremendous table bloat.
What do you get from "vacuum verbose project" --- in particular, how
many pages in the table?
>
Bill,
> Does the order of columns of varying size have any effect on
> SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where
> an integer primary key is listed first in the table and alternatively
> listed after some large varchar or text columns?
No, the "order" of the columns i
Guys, just so you know:
OSDL did some testing and found Ext3 to be perhaps the worst FS for PostgreSQL
-- although this testing was with the default options. Ext3 involved an
almost 40% write performance penalty compared with Ext2, whereas the penalty
for ReiserFS and JFS was less than 10%.
Litao Wu <[EMAIL PROTECTED]> writes:
> How about:
> select c.relname, l.pid, l.mode, l.granted,
> a.current_query
> from pg_locks l, pg_class c, pg_stat_activity a
> where
> l.relation = c.oid
> AND l.pid = a.procpid
> order by l.granted, l.pid;
You can't join to pg_class without eliminating
Hi Rod,
Actually, we're already using a substantial caching system in code for
nearly all pages delivered - we've exhausted that option. Our system uses a
login/session table for about 1/8 of our page views (those visitors who are
logged in), and has tracking features. While I'd love to scrap th
On Tue, 2004-08-10 at 13:17, Jason Coene wrote:
> Hi All,
>
> We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4, 2GB
> ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!).
>
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a s
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a sample table, accessed constantly. We average
> about 4,000 - 5,000 queries per second - all from web traffic. As you can
99% is reads? and probably the same data over and over again? You migh
Hi,
I have a question on bulk checking, inserting into a table and
how best to use an index for performance.
The data I have to work with is a monthly CD Rom csv data dump of
300,000 property owners from one area/shire.
So every CD has 300,000 odd lines, each line of data which fills the
'property
Does that mean reindex is not needed
for PG version 7.4?
In what kind situations under PG 7.4,
reindex is worthwhile?
Thanks,
Here is doc from 7.3:
PostgreSQL is unable to reuse B-tree index pages in
certain cases. The problem is that if indexed rows are
deleted, those index pages can only be
If the bulk load has the possibility of duplicating data, then you need
to change methods. Try bulk loading into a temp table, index it like
the original, eliminate the dups and merge the tables.
It is also possible to do an insert from the temp table into the final
table like:
insert into or
Usualy any bulk load is faster with indexes dropped and the rebuilt ... failing that
(like you really need the indexes while loading, say into a "hot" table) be sure to
wrap all the SQL into one transaction (BEGIN;...COMMIT;) ... if any data failes it all
fails, which is usually easier to deal
Hi Jim,
Thanks for your time.
> If the bulk load has the possibility of duplicating data
Yes, each row will require either:
a) One SELECT + One INSERT
or
b) One SELECT + One UPDATE
I did think of using more than one table, ie. temp table.
As each month worth of data is added I expect to see
a chang
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Rudi
Starcevic) transmitted:
> A minute for your thoughts and/or suggestions would be great.
Could you give a more concrete example? E.g. - the DDL for the
table(s), most particularly.
At first guess, I think you're worryi
If it has to read a majority (or even a good percentage) of the rows in question a
sequential scan is probably faster ... and as Jim pointed out, a temp table can often
be a useful medium for getting speed in a load and then allowing you to clean/alter
data for a final (easy) push.
G
-Origi
Hi,
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Rudi
Starcevic) transmitted:
A minute for your thoughts and/or suggestions would be great.
Heh heh
Could you give a more concrete example? E.g. - the DDL for the
table(s), most particularly.
Thanks, I didn't add the
Greetings.
I have a question regarding performance of certain datatypes:
I have a field where I will store my clients phone numbers. I know that
this field will never exceed 15 characters, and I will store only
numbers here (no dashes, dots, etc...), so I was wondering:
Wich type is faster:
On Tue, 2004-08-10 at 23:42, Er GalvÃo Abbott wrote:
> Greetings.
>
> I have a question regarding performance of certain datatypes:
>
> I have a field where I will store my clients phone numbers. I know
> that this field will never exceed 15 characters, and I will store only
> numbers here (no da
We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4,
2GB
ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!).
Cheap solution while you look for another server :
Try to use something other than RAID5.
You have 4 disks, so you could use a striping+mirroring RAID which would
Numeric won't store that :
(+33) 4 01 23 45 67
On Wed, 11 Aug 2004 02:42:33 -0300, Er Galvão Abbott
<[EMAIL PROTECTED]> wrote:
Greetings.
I have a question regarding performance of certain datatypes:
I have a field where I will store my clients phone numbers. I know that
this
field wil
31 matches
Mail list logo
