Karen Hill wrote:
> I have 8.2 Beta 1 (Win32) on my home pc and offset was faster than
> fetching relative to the last key as measured by explain analyze. This
> was on a table with about 1,000 rows.
For such a small table the difference is probably irrelevant. Try with
several million rows.
"Merlin Moncure" wrote:
> > SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N;
>
> using offset to walk a table is extremely poor form because of:
> * poor performance
> * single user mentality
> * flat file mentality
>
> databases are lousy at this becuase they inheritly do not support
> abo
On 10/16/06, Alban Hertroys <[EMAIL PROTECTED]> wrote:
Merlin Moncure wrote:
> for server side browsing use cursors or a hybrid pl/pgqsl loop. for
> client side, browse fetching relative to the last key:
>
> select * from foo where p > p1 order by p limit k;
This does require some way for the cl
On Mon, 16 Oct 2006 11:05:33 +0200
Alban Hertroys <[EMAIL PROTECTED]> wrote:
> This does require some way for the client to keep a single
> transaction open. If this kind of query is performed by a web
> application (as is often the case), the "client" is the server side
> web script engine, and n
Merlin Moncure wrote:
On 10/13/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N;
using offset to walk a table is extremely poor form because of:
* poor performance
* single user mentality
* flat file mentality
databases are lousy at this bec
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Fri, Oct 13, 2006 at 01:35:51PM -0400, Tom Lane wrote:
>> looked reasonably robust --- ie, both safe and not full of unsupportable
>> assumptions about knowing exactly where everything actually is on the
>> disk platter. It'd still be interesting if
Merlin Moncure wrote:
> On 10/14/06, Chris Mair <[EMAIL PROTECTED]> wrote:
> >The interesting part is the graph that shows updates / sec real time
> >vs. running total of updates:
> >http://www.1006.org/misc/20061014_pgupdates_bench/results.png
>
> one small thing: the variances inside the trendli
On 10/14/06, Chris Mair <[EMAIL PROTECTED]> wrote:
The interesting part is the graph that shows updates / sec real time
vs. running total of updates:
http://www.1006.org/misc/20061014_pgupdates_bench/results.png
one small thing: the variances inside the trendline are caused by
using integer tim
On 10/14/06, Chris Mair <[EMAIL PROTECTED]> wrote:
Ok,
I did some tests at last on this using the above 3 suggestions.
I ran on 8.1.4 on Linux with fsync=off and did 300k updates bundled
into 100 updates / 1 transaction (100 turned out to be a sweeter
spot than 1000).
My postgresql 'magic numbe
> I'd dare to say that if you just ran ZABBIX on 8.1 with
> autovacuum on with a shortish interval (30 sec?) you'd
> get rid of your performance problems. Time to update
> the documentation after all? ;)
I would be curious to see what would happen if you added to vacuum_*
parameters to the equati
> > it would be cool if you could at least:
> >
> > - bundle your updates into transactions of, say, 1000 updates at a time
> >i.e. wrap a BEGIN; END; around a 1000 of them
> > - run postgresql with fsync off, since you're using MyISAM
> > - run PostgreSQL at least 8, since you're running M
[EMAIL PROTECTED] wrote on 11.10.2006 16:54:
Do a simple test to see my point:
1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop
I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.2
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Thomas Kellerer
> Sent: Friday, October 13, 2006 2:11 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] more anti-postgresql FUD
>
> [EMAIL PROTECTED
[EMAIL PROTECTED] wrote on 11.10.2006 16:54:
Do a simple test to see my point:
1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop
As others have pointed out, committing the data is a vi
> Face it, if one does hundreds updates per second for one table (that's
> exactly what ZABBIX does, and not for one record(!) table as in my
> simple test), performance degrades so fast that vacuum has to be
> executed once per 5-15 seconds to keep good performance. The vacuum
> will run at least
> it would be cool if you could at least:
>
> - bundle your updates into transactions of, say, 1000 updates at a time
>i.e. wrap a BEGIN; END; around a 1000 of them
> - run postgresql with fsync off, since you're using MyISAM
> - run PostgreSQL at least 8, since you're running MySQL 5
>
> I'
On Fri, Oct 13, 2006 at 01:35:51PM -0400, Tom Lane wrote:
> looked reasonably robust --- ie, both safe and not full of unsupportable
> assumptions about knowing exactly where everything actually is on the
> disk platter. It'd still be interesting if anyone gets a new idea...
Might it be the case
> > Unfortunately PostgreSQL performs much slower than MySQL doing large
> > number of updates for one single table. By its nature ZABBIX requires
> > to execute hundreds of updates per second for large installations.
> > PostgreSQL cannot handle this nicely.
>
> If you refuse to vacuum (or have th
> > I'm author and maintainer of ZABBIX and the manual. I would like to add
> > some comments to the thread.
>
> just so you know, I brought this up after taking a look at the zabbix
> software, which is in my opinion very excellent. I came across a
> little strong in my comments and peter e was c
> > 1. create table test (id int4, aaa int4, primary key (id));
> > 2. insert into test values (0,1);
> > 3. Execute "update test set aaa=1 where id=0;" in an endless loop
> >
> > I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
> > sorry had no configured InnoDB). Ubuntu 6.0.6, A
>
> 2. less points of failure: ups doesnt help you if your cpu fries,
> power supply fries, memory frieds, motherboard fries, o/s halts, etc
> etc. :-)
>
> 3. experience has taught me not to put 100% faith in ups power switchover.
As a follow up to this. We have all line conditioning natural gas
On 10/13/06, AgentM <[EMAIL PROTECTED]> wrote:
> No to mention if you are *that* concerned you could buy a generator
> for
> 500 bucks that will keep the machine alive if you absolutely have to.
>
> There is nothing wrong with write back cache as long as you have the
> infrastructure to support i
On Oct 13, 2006, at 14:36 , Joshua D. Drake wrote:
Stephen Frost wrote:
* Alexander Staubo ([EMAIL PROTECTED]) wrote:
What formula did you use to get to that number? Is there a generic
way on Linux to turn off (controller-based?) write caching?
Just a side-note, but if you've got a pretty g
On Fri, 2006-10-13 at 13:07 -0500, Jim C. Nasby wrote:
> On Fri, Oct 13, 2006 at 01:52:10PM -0400, Merlin Moncure wrote:
> > On 10/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> > >Martijn van Oosterhout writes:
> > >> Is that really true? In theory block n+1 could be half a revolution
> > >> after
Stephen Frost wrote:
> * Alexander Staubo ([EMAIL PROTECTED]) wrote:
>> What formula did you use to get to that number? Is there a generic
>> way on Linux to turn off (controller-based?) write caching?
>
> Just a side-note, but if you've got a pretty good expectation that you
> won't be without
* Alexander Staubo ([EMAIL PROTECTED]) wrote:
> What formula did you use to get to that number? Is there a generic
> way on Linux to turn off (controller-based?) write caching?
Just a side-note, but if you've got a pretty good expectation that you
won't be without power for 24 consecutive hours
On Fri, Oct 13, 2006 at 01:52:10PM -0400, Merlin Moncure wrote:
> On 10/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> >Martijn van Oosterhout writes:
> >> Is that really true? In theory block n+1 could be half a revolution
> >> after block n, allowing you to commit two transactions per revolution.
On Fri, 2006-10-13 at 13:52 -0400, Merlin Moncure wrote:
> On 10/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Martijn van Oosterhout writes:
> > > Is that really true? In theory block n+1 could be half a revolution
> > > after block n, allowing you to commit two transactions per revolution.
> >
On 10/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Martijn van Oosterhout writes:
> Is that really true? In theory block n+1 could be half a revolution
> after block n, allowing you to commit two transactions per revolution.
Not relevant, unless the prior transaction happened to end exactly at a
Martijn van Oosterhout writes:
> On Fri, Oct 13, 2006 at 03:35:37PM -, Andrew - Supernews wrote:
>> It's just the number of disk revolutions per second. Without caching, each
>> WAL flush tends to require a whole revolution unless the on-disk layout of
>> the filesystem is _very_ strange.
> I
On Fri, Oct 13, 2006 at 03:35:37PM -, Andrew - Supernews wrote:
> It's just the number of disk revolutions per second. Without caching, each
> WAL flush tends to require a whole revolution unless the on-disk layout of
> the filesystem is _very_ strange. You can get multiple commits per WAL
> fl
On 2006-10-13, Alexander Staubo <[EMAIL PROTECTED]> wrote:
> Makes sense. However, in this case I was batching updates in
> transactions and committing each txn at 1 second intervals, all on a
> single connection. In other words, the bottleneck illustrated by this
> test should not be related
On Oct 13, 2006, at 17:35 , Andrew - Supernews wrote:
On 2006-10-13, Alexander Staubo <[EMAIL PROTECTED]> wrote:
On Oct 13, 2006, at 17:13 , Andrew - Supernews wrote:
Your disk probably has write caching enabled. A 10krpm disk
should be
limiting you to under 170 transactions/sec with a singl
On 2006-10-13, Alexander Staubo <[EMAIL PROTECTED]> wrote:
> On Oct 13, 2006, at 17:13 , Andrew - Supernews wrote:
>> Your disk probably has write caching enabled. A 10krpm disk should be
>> limiting you to under 170 transactions/sec with a single connection
>> and fsync enabled.
>
> What formula d
On 10/13/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
# [EMAIL PROTECTED] / 2006-10-10 14:16:19 -0400:
> FUD from another open source project is really poor form, particulary
> when not in competing segements where a little bit of competitive
> rivalry is expected.
OMG WTF what FUD???
pl
On Oct 13, 2006, at 17:13 , Andrew - Supernews wrote:
On 2006-10-13, Alexander Staubo <[EMAIL PROTECTED]> wrote:
On my box (Dell PowerEdge 1850, dual Xeon 2.8GHz, 4GB RAM, 10kRPM
SCSI, Linux 2.6.15, Ubuntu) I get 1,100 updates/sec, compared to
10,000 updates/sec with MySQL/InnoDB, using a stock
On 2006-10-13, Alexander Staubo <[EMAIL PROTECTED]> wrote:
> On my box (Dell PowerEdge 1850, dual Xeon 2.8GHz, 4GB RAM, 10kRPM
> SCSI, Linux 2.6.15, Ubuntu) I get 1,100 updates/sec, compared to
> 10,000 updates/sec with MySQL/InnoDB, using a stock installation of
> both. Insert performance is
On Oct 11, 2006, at 16:54 , [EMAIL PROTECTED] wrote:
I'm author and maintainer of ZABBIX and the manual. I would like to
add
some comments to the thread.
[snip]
I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, defau
# [EMAIL PROTECTED] / 2006-10-10 14:16:19 -0400:
> FUD from another open source project is really poor form, particulary
> when not in competing segements where a little bit of competitive
> rivalry is expected.
OMG WTF what FUD???
# [EMAIL PROTECTED] / 2006-10-10 13:55:57 -0400:
> http://www
After a long battle with technology, [EMAIL PROTECTED] ("Jim C. Nasby"), an
earthling, wrote:
> But you can actually write good code that will run on multiple
> databases if you're willing to write the tools to allow you to do it.
There's an argument out there that we don't actually have relation
On Thu, Oct 12, 2006 at 07:40:42PM +0200, Tim Tassonis wrote:
> > I have yet to see a good application that supports "database
> independence".
>
> If you are talking about high- end applications (big databases with lot
> of transactions), you're of course right. However, there are a lot of
> a
Joshua D. Drake wrote:
Well, that is hardly surprising. What exactly is your point?
If you want to write portable software, you usually stay with generally
available, standardized features or API's, be it "database independent",
"platform independent", you name it. You certainly don't go for
use
> Used to run a time reporting system on a 3b2
> 400, 4MB Ram, WE32100 10MHz processor, 1.1 MIPS.
You had zeroes? We had to use the letter "O"!
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)--
Not necessarily. Last I heard, MySQL ships with multiple config files,
ie: small, medium and large. So by choosing one of those you're
effectively tuning MySQL as well.
Hmm, wouldn't it be a good idea to provide something similar?
I think an initdb could have an additional flag, like "--tune-fo
> Do a simple test to see my point:
>
> 1. create table test (id int4, aaa int4, primary key (id));
> 2. insert into test values (0,1);
> 3. Execute "update test set aaa=1 where id=0;" in an endless loop
>
> I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
> sorry had no config
1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop
I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
dat
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
> Unfortunately PostgreSQL performs much slower than MySQL doing large
> number of updates for one single table. By its nature ZABBIX requires
> to execute hundreds of updates per second for large installations.
> PostgreSQL cannot handle this nicely.
On 11 Oct 2006 07:54:52 -0700, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
Hello,
I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.
just so you know, I brought this up after taking a look at the zabbix
software, which is in my opinion very ex
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 19:10, Geoffrey wrote:
> Ron Johnson wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 10/11/06 14:48, Chris Browne wrote:
>>> [EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom
Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 14:48, Chris Browne wrote:
[EMAIL PROTECTED] (Andrew Sullivan) writes:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
[snip]
Oh, and a cluster of IBM p570s would probably be enough to run a 20
user system
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/11/06 14:48, Chris Browne wrote:
> [EMAIL PROTECTED] (Andrew Sullivan) writes:
>> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
[snip]
> Oh, and a cluster of IBM p570s would probably be enough to run a 20
> user system :-(. [Actually
> Well, that is hardly surprising. What exactly is your point?
>
> If you want to write portable software, you usually stay with generally
> available, standardized features or API's, be it "database independent",
> "platform independent", you name it. You certainly don't go for
> user-defined ty
Steve Crawford schrieb:
Guy Rouillier wrote:
Andrew Sullivan wrote:
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a
Hello,
I'm author and maintainer of ZABBIX and the manual. I would like to add
some comments to the thread.
First of all, ZABBIX supports three database engines: MySQL, Oracle and
PostgreSQL. It uses absolutely standard SQL, same for all three
database engines. We have absolutely no intention to
[EMAIL PROTECTED] (Andrew Sullivan) writes:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
>
> On the day when you don't, please tell me what application you found
> where it isn't. I want to buy the developers a drink. Or maybe a
Guy Rouillier wrote:
> Andrew Sullivan wrote:
>> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>>> Some days I think database independence is a myth.
>> On the day when you don't, please tell me what application you found
>> where it isn't. I want to buy the developers a drink. Or may
Andrew Sullivan wrote:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
>
> On the day when you don't, please tell me what application you found
> where it isn't. I want to buy the developers a drink. Or maybe a
> bar.
Command Pro
Andrew Sullivan wrote:
> On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
>> Some days I think database independence is a myth.
>
> On the day when you don't, please tell me what application you found
> where it isn't. I want to buy the developers a drink. Or maybe a
> bar.
The Manti
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
> Some days I think database independence is a myth.
On the day when you don't, please tell me what application you found
where it isn't. I want to buy the developers a drink. Or maybe a
bar.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
W
On Tue, 2006-10-10 at 14:50 -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> >> * MySQL is used as a primary development platform.
>
> > Another good reason.
>
> Actually that's *the* reason --- it's always going to be hard for
> Postgres to look good for an application th
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> >> * MySQL is used as a primary development platform.
>
> > Another good reason.
>
> Actually that's *the* reason --- it's always going to be hard for
> Postgres to look good for an applica
On Tue, Oct 10, 2006 at 06:25:21PM -0300, Jorge Godoy wrote:
> "Jacob Coby" <[EMAIL PROTECTED]> writes:
>
> > We were looking to improve our session performance, so I did a basic
> > test of using mysql 4.0 innodb vs postgres 8.1. The test did a simple
> > retrieve, update, save; 1 time per page.
"Jacob Coby" <[EMAIL PROTECTED]> writes:
> We were looking to improve our session performance, so I did a basic
> test of using mysql 4.0 innodb vs postgres 8.1. The test did a simple
> retrieve, update, save; 1 time per page. mysql was stock, pg had a
> shared_buffers and a couple of other stan
Tom Lane <[EMAIL PROTECTED]> writes:
> Some days I think database independence is a myth.
I believe it is as real as Santa Claus and the Easter Bunny. All of us know
that those three exist, right? :-)
--
Jorge Godoy <[EMAIL PROTECTED]>
---(end of broadcast)-
> -Original Message-
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> >> * MySQL is used as a primary development platform.
>
> > Another good reason.
>
> Actually that's *the* reason --- it's always going to be hard for
> Postgres to look good for an application that's been
designed/opti
also big considerations.
--
Brandon Aiken
CS/IT Systems Engineer
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure
Sent: Tuesday, October 10, 2006 1:56 PM
To: PgSQL General
Subject: [GENERAL] more anti-postgresql FUD
http://www.zabbix.com/m
[EMAIL PROTECTED] ("Merlin Moncure") writes:
> http://www.zabbix.com/manual/v1.1/install.php
> anybody know these guys? this is right off the mysql anti-postgresql
> advocacy page.
On the upside, they actually indicated what versions they were working
with.
If they're so out of date that their d
On 10/10/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
Merlin Moncure wrote:
> http://www.zabbix.com/manual/v1.1/install.php
>
> in section PostgreSQL vs MySQL :
> [quoting]
> Regarding the choice between PostgreSQL and MySQL, MySQL is
> recommended for several reasons:
I don't see any fear, u
Peter Eisentraut <[EMAIL PROTECTED]> writes:
>> * MySQL is used as a primary development platform.
> Another good reason.
Actually that's *the* reason --- it's always going to be hard for
Postgres to look good for an application that's been designed/optimized
for MySQL. The application has alre
Merlin Moncure wrote:
> http://www.zabbix.com/manual/v1.1/install.php
>
> in section PostgreSQL vs MySQL :
> [quoting]
> Regarding the choice between PostgreSQL and MySQL, MySQL is
> recommended for several reasons:
I don't see any fear, uncertainty, or doubt there.
> * MySQL is faster
It pr
On 10/10/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
Merlin Moncure wrote:
> http://www.zabbix.com/manual/v1.1/install.php
>
> in section PostgreSQL vs MySQL :
Well they may be right that far back. But 7.1 is years and years old.
Joshua D. Drake
no excuse. that would be like postgresql
Merlin Moncure wrote:
> http://www.zabbix.com/manual/v1.1/install.php
>
> in section PostgreSQL vs MySQL :
> [quoting]
> Regarding the choice between PostgreSQL and MySQL, MySQL is
> recommended for several reasons:
>
>* MySQL is faster
>
> recent benchmarks using ZABBIX clearly show th
http://www.zabbix.com/manual/v1.1/install.php
in section PostgreSQL vs MySQL :
[quoting]
Regarding the choice between PostgreSQL and MySQL, MySQL is
recommended for several reasons:
* MySQL is faster
recent benchmarks using ZABBIX clearly show that PostgreSQL
(7.1.x) is at least 10 time
73 matches
Mail list logo
