PostgreSQL Performance - Re: Decide between Postgresql and Mysql (help of

Author

Re: Decide between Postgresql and Mysql (help of

Scott Marlowe

2006-03-28, 1:32 pm

On Tue, 2006-03-28 at 09:31, Marcos wrote:
> Hi,
>
> I'm a Postgresql's user and I think that it's very very good and
> robust.
>
> In my work we're confuse between where database is the best choose:
> Postgresql or Mysql. The Mysql have the reputation that is very fast
> working in the web but in our application we are estimating many access
> simultaneous, then I think that the Postgresql is the best choice.
>
> Am I right?
>
> Our server have 1 GB of RAM, how many users can it support at the same
> time with this memory?

This is as much about the code in front of the database as the database
itself. You'll want to use an architecture that supports pooled
connections (java, php under lighttpd, etc...) and you'll want to look
at your read to write ratio.

MySQL and PostgreSQL can handle fairly heavy parallel loads. PostgreSQL
is generally a much better performer when you need to make a lot of
parallel writes.

But the bigger question is which one is suited to your application in
general. If some major issue in MySQL or PostgreSQL makes it a poor
choice for your app, then it doesn't matter how much load it can handle,
it's still a poor choice.

Generally speaking, MySQL is a poor choice if you're doing things like
accounting, where the maths have to be correct. It's quite easy to ask
MySQL to do math and get the wrong answer. It also has some serious
problems with referential integrity, but most of those can be worked
around using innodb tables. But at that point, you're using the same
basic storage methods as PostgreSQL uses, i.e. an MVCC storage engine.
And now that Oracle has bought Innodb, the availability of that in the
future to MySQL is in doubt.

There's also the issue of licensing. If you'll be selling copies of
your app to customers, you'll be writing a check for each install to
MySQL AB. Not so with PostgreSQL.

So, what exactly are you planning on doing?

Lastly, take a look here:

http://sql-info.de/mysql/gotchas.html

and here:

http://sql-info.de/postgresql/postgres-gotchas.html

for a list of the common "gotchas" in both databases.

Generally you'll find the PostgreSQL gotchas are of the sort that make
you go "oh, that's interesting" and the MySQL gotchas are the kind that
make you go "Dear god, you must be kidding me!"

But that's just my opinion, I could be wrong.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

PFC

2006-03-28, 8:29 pm

> This is as much about the code in front of the database as the database
> itself. You'll want to use an architecture that supports pooled
> connections (java, php under lighttpd, etc...) and you'll want to look

Well, anybody who uses PHP and cares about performance is already using
lighttpd, no ?

> MySQL and PostgreSQL can handle fairly heavy parallel loads.

I'll only speak about MyISAM. MySQL == MyISAM. InnoDB is useless : if you
want transactions, use postgres.
If you say to yourself "oh yeah, but it would be cool to use a MyISAM
table for stuff like hit counters etc"... Is it the job of a SQL database
to count hits on the root page of your site ? No. To store user sessions ?
No. The job of a SQL database is to efficiently handle data, not to do
something that should stay in RAM in the application server process, or at
worst, in a memcached record.

MySQL + MyISAM has a huge advantage : it can look up data in the index
without touching the tables.
MySQL handles parallel SELECTs very well.

However, throw in some maintenance operation which involves a long query
with writes (like a big joined UPDATE) and all access to your website is
blocked while the query lasts.
This is worsened by the fact that MySQL sucks at complex queries.

If all of your updates are done to a few rows, MyISAM is cool, but
someday you'll want to do this query which locks a table during one
minute... and then you got a problem.

Just be very clear about what you want to do, what types of queries
you'll want to run in two years... etc.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jim C. Nasby

2006-03-28, 8:29 pm

On Tue, Mar 28, 2006 at 09:42:51PM +0200, PFC wrote:
> However, throw in some maintenance operation which involves a long
> query with writes (like a big joined UPDATE) and all access to your
> website is blocked while the query lasts.
> This is worsened by the fact that MySQL sucks at complex queries.
>
> If all of your updates are done to a few rows, MyISAM is cool, but
> someday you'll want to do this query which locks a table during one
> minute... and then you got a problem.

Not to mention that MyISAM loves to eat data. Livejournal suffered at
least one major crash due to MyISAM corruption.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-03-28, 8:29 pm

Heh, too quick on the send button...

On Tue, Mar 28, 2006 at 09:42:51PM +0200, PFC wrote:
> I'll only speak about MyISAM. MySQL == MyISAM. InnoDB is useless :
> if you want transactions, use postgres.
> If you say to yourself "oh yeah, but it would be cool to use a
> MyISAM table for stuff like hit counters etc"... Is it the job of a SQL
> database to count hits on the root page of your site ? No. To store user
> sessions ? No. The job of a SQL database is to efficiently handle data,
> not to do something that should stay in RAM in the application server
> process, or at worst, in a memcached record.

Actually, it's entirely possible to do stuff like web counters, you just
want to do it differently in PostgreSQL. Simply insert into a table
every time you have a hit, and then roll that data up periodically.

And using MyISAM is no panacea, either. Trying to keep a web counter in
a MyISAM table means you'll serialize every web page on that counter
update.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Vivek Khera

2006-03-29, 11:31 am

On Mar 28, 2006, at 1:59 PM, Scott Marlowe wrote:

> Generally you'll find the PostgreSQL gotchas are of the sort that make
> you go "oh, that's interesting" and the MySQL gotchas are the kind
> that
> make you go "Dear god, you must be kidding me!"
>
> But that's just my opinion, I could be wrong.

I nominate this for "quote of the month". :-)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Scott Marlowe

2006-03-30, 1:31 pm

On Thu, 2006-03-30 at 11:22, Chris Browne wrote:
> cjames@modgraph-usa.com ("Craig A. James") writes:
>
>
> Actually, this seems not so bad a point...
>
> If people are so interested in micro-managing certain bits of how
> performance works, then it seems an excellent question to ask why NOT
> write all the CGIs in C.
>
> After all, CGI in C *won't* suffer from the performance troubles
> associated with repetitively loading in Perl/PHP frameworks (which is
> why things like FastCGI, mod_perl, and such came about), and you can
> get a fair level of assurance that the compiled C won't be the
> performance bottleneck.
>
> And yes, it does become natural to ask "why not write CGIs in ASM?"
> ;-)

But as an aside, I've been load testing our web application. We have,
in the test farm, two tomcat servers feeding into three jboss servers,
feeding into a database farm (oracle and postgresql, doing different
things, oracle is the transaction engine, postgresql is the "data
collection bucket" so to speak.)

Our tomcat servers sit at 10% load, the jboss servers sit at 20 to 40%
load, and the Oracle server sits at 100% load.

And the thing is, while we can add load balanced tomcat and jboss
servers as need be, and get nearly linear scaling from them, we can't do
the same for the database. That's going to require vertical scaling.

And that, nowadays, is generally the state of web development. It's not
the language you're using to write it in, it's how efficiently you're
using your database. We can probably tweak the system we're testing now
and get more from our databases by adjusting how hibernate hits them,
and the types of queries that it's throwing, but in the long run, the
bottleneck will always be the database server, because we can throw
relatively small amounts of money at the other layers if they happen to
be bogging down. Not so much with the database.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links