Thread: Database Performance?
Dear Postgres Users, I am getting ready to be a big project for this customer and I was just curious on the performance of Postgresql 7.2? I have seen in previous benchmarks where MySQL was a lot faster overall than Postgres. Has 7.2 improved at all in the performance? I want to make sure I choose the right database since the database will be rather busy :) Devin
On Wed, Feb 13, 2002 at 12:26:10AM -0700, Devin wrote: > > Dear Postgres Users, > > I am getting ready to be a big project for this customer > and I was just curious on the performance of Postgresql 7.2? > I have seen in previous benchmarks where MySQL was a lot > faster overall than Postgres. Has 7.2 improved at all in > the performance? I want to make sure I choose the right > database since the database will be rather busy :) Be a little bit careful about benchmarks. Whether postgresql or mysql is faster depends on what you're doing. If all you want to do is the occasional insert and lots and lots of simple selects, mysql (or even grep) will beat postgres. If you want to do subselects, transactions, foreign keys, outer joins, fault tolerence or anything else that makes a database a database, mysql just can't do it. See if MySQL and PostgreSQL satisfy your business requirements and then decide which one you want. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Terrorists can only take my life. Only my government can take my freedom.
This argument is out of date. MySQL currently supports transactions, foreign keys, and outer joins. (4.1 will support subselects) As for fault tolerance, MySQL has built in support for replication. A couple years ago I converted a project from MySQL to PostgreSQL because of MySQL's lack of features. I am now in the process of converting back to MySQL because of the performance improvements and replication. > > Be a little bit careful about benchmarks. Whether postgresql or > mysql is > faster depends on what you're doing. If all you want to do is the > occasional > insert and lots and lots of simple selects, mysql (or even grep) > will beat > postgres. If you want to do subselects, transactions, foreign > keys, outer > joins, fault tolerence or anything else that makes a database a > database, > mysql just can't do it. > > See if MySQL and PostgreSQL satisfy your business requirements and then > decide which one you want.
Doesn't PostgreSQL's better locking and query optimising mean that it performs better in multi-user situations? - Andrew On Sun, Feb 17, 2002 at 05:49:55PM -0500, Robert Berger wrote: > This argument is out of date. MySQL currently supports > transactions, foreign > keys, and outer joins. (4.1 will support subselects) > > As for fault tolerance, MySQL has built in support for replication. > > A couple years ago I converted a project from MySQL to PostgreSQL > because > of MySQL's lack of features. I am now in the process of converting > back to > MySQL because of the performance improvements and replication. > > > > > > Be a little bit careful about benchmarks. Whether postgresql or > > mysql is > > faster depends on what you're doing. If all you want to do is the > > occasional > > insert and lots and lots of simple selects, mysql (or even grep) > > will beat > > postgres. If you want to do subselects, transactions, foreign > > keys, outer > > joins, fault tolerence or anything else that makes a database a > > database, > > mysql just can't do it. > > > > See if MySQL and PostgreSQL satisfy your business requirements and then > > decide which one you want. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Hi all, A friend of mine who's into MySQL recently said told me that MySQL doesn't *really* support transactions, it just looks like it does. He then went on to say it doesn't really do ROLLBACK, or isolate the transactions or something. Now, that's kind of FUD, but I'd rather it not be. If someone out there knows if MySQL *really* supports transactions (of the ACID variety) could they please speak up? :-) Regards and best wishes, Justin Clift Andrew Snow wrote: > > Doesn't PostgreSQL's better locking and query optimising mean that it > performs better in multi-user situations? > > - Andrew > > On Sun, Feb 17, 2002 at 05:49:55PM -0500, Robert Berger wrote: > > This argument is out of date. MySQL currently supports > > transactions, foreign > > keys, and outer joins. (4.1 will support subselects) > > > > As for fault tolerance, MySQL has built in support for replication. > > > > A couple years ago I converted a project from MySQL to PostgreSQL > > because > > of MySQL's lack of features. I am now in the process of converting > > back to > > MySQL because of the performance improvements and replication. > > > > > > > > > > Be a little bit careful about benchmarks. Whether postgresql or > > > mysql is > > > faster depends on what you're doing. If all you want to do is the > > > occasional > > > insert and lots and lots of simple selects, mysql (or even grep) > > > will beat > > > postgres. If you want to do subselects, transactions, foreign > > > keys, outer > > > joins, fault tolerence or anything else that makes a database a > > > database, > > > mysql just can't do it. > > > > > > See if MySQL and PostgreSQL satisfy your business requirements and then > > > decide which one you want. > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Mon, Feb 18, 2002 at 01:52:34PM +1100, Justin Clift wrote: > Hi all, > > A friend of mine who's into MySQL recently said told me that MySQL > doesn't *really* support transactions, it just looks like it does. He > then went on to say it doesn't really do ROLLBACK, or isolate the > transactions or something. No flames, please. I just thought reading the fine manuals might give us some info. <disclaimer> I haven't used MySQL for anything in a long time. Your mileage may vary. Health Canada/Surgeon General recommends. &c. Opinions are my own; my employer very likely disagrees, and in any case does not approve or disapprove of, or agree or disagree with, any opinion, explicit or implied, herein. Passages marked "----quote----" are extracted from copyright materials published elsewhere, and are used under fair-use provisions. Any errors, omissions, or misrepresentations are the result of my feeble mind and not attributable to anyone else. Do not attempt at home. This is all my fault. </disclaimer> Here's all I could gather from the MySQL man pages: ----quote---- <http://www.mysql.com/doc/B/D/BDB_characteristics.html> -LOCK TABLES works on BDB tables as with other tables. If you don't use LOCK TABLE, MySQL will issue an internal multiple-write lock on the table to ensure that the table will be properly locked if another thread issues a table lock. -Internal locking in BDB tables is done on page level. ----end quote---- So, that looks like not-high-load to me, although much better than talk-SQL-to-the-filesystem, which always seemed like a kludge. But, ----quote---- <http://www.mysql.com/doc/I/n/InnoDB_overview.html> InnoDB provides MySQL with a transaction-safe (ACID compliant) table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL. InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine. Technically, InnoDB is a complete database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, MyISAM tables where each table is stored as a separate file. InnoDB tables can be of any size also on those operating systems where file size is limited to 2 GB. ----end quote---- The latter just raises the question, of course, of why in the world you'd want to paste MySQL on top of InnoDB, since the latter seems pretty cool from the description. Especially considering this limitation <http://www.mysql.com/doc/I/n/InnoDB_restrictions.html>: ----quote---- WARNING: Do NOT convert MySQL system tables from MyISAM TO InnoDB tables! This is not supported; If you do this MySQL will not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script. ----end quote---- That suggests that MySQL as a system is not transaction-safe. I guess that's what Justin's friend may have been talking about. Anyway, it'd make me nervous. But the individual databases might be transaction-safe. Note that most of the traditional "Nyah-nyah, Postgres has all these limitations" limitations are imposed by InnoDB as well (see the url). Fancy that. I'd like to see some real benchmarks under load comparing PostgreSQL and MySQL+InnoDB. It seems that such would be an apples::apples (ok, maybe one's an overbred hybrid apple, but still an apple) comparison at last. Anyone know how to do as much? A -- ---- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M6K 3E3 +1 416 646 3304 x110
Hi, "Performance improvements and replication". This is MySQL. Not a database. I'd keep my data in a text file instead of MySQL! My friends, who use MySQL, say they would compile it without transaction support, since MySQL users would not need it. MySQL -> for the web... That's all :) Regards and best wishes, Devrim On Sun, 17 Feb 2002, Robert Berger wrote: > This argument is out of date. MySQL currently supports > transactions, foreign > keys, and outer joins. (4.1 will support subselects) > > As for fault tolerance, MySQL has built in support for replication. > > A couple years ago I converted a project from MySQL to PostgreSQL > because > of MySQL's lack of features. I am now in the process of converting > back to > MySQL because of the performance improvements and replication. > > > > > > Be a little bit careful about benchmarks. Whether postgresql or > > mysql is > > faster depends on what you're doing. If all you want to do is the > > occasional > > insert and lots and lots of simple selects, mysql (or even grep) > > will beat > > postgres. If you want to do subselects, transactions, foreign > > keys, outer > > joins, fault tolerence or anything else that makes a database a > > database, > > mysql just can't do it. > > > > See if MySQL and PostgreSQL satisfy your business requirements and then > > decide which one you want. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Devrim GUNDUZ devrim@oper.metu.edu.tr devrim.gunduz@linux.org.tr devrimg@tr.net Web : http://devrim.oper.metu.edu.tr ------------------------------------------------------------------
Le Lundi 18 Février 2002 06:18, Andrew Sullivan a écrit : > I'd like to see some real benchmarks under load comparing PostgreSQL > and MySQL+InnoDB. It seems that such would be an apples::apples (ok, > maybe one's an overbred hybrid apple, but still an apple) comparison > at last. Anyone know how to do as much? Dear Andrew, The main difference is that PostgreSQL allows server-side programming and does things smartly: PostgreSQL supports triggers, rules, views, functions and types to deliver ready to use data to your backend (Apache, Java, Windows). PostgreSQL has several server-side languages (PLpgSQL, PLperl, PLbash, etc...) and may soon support Java server-side programming. It allows the storage of multi-byte data, including Unicode support. Optimization is 10% hardware, 90% software programming. The way you build tables, views and program server-side logic can boost your application by a factor of 10. As a result, an optimized database with PostgreSQL running on a i586 with IDE discs may handle more transactions than a double Pentium MySQL with Ultra-wide SCSI. This is life ! This is not a question hardware benchmark. Your email clearly shows that MySQL lacks features. Pease keep in mind that MySQL is developped by a single man (who accepts patches from others), not a community of developpers like PostgreSQL. But a tool is only worth "what you are doing with it". If you are a part-time hacker, MySQL may suffice for simple SELECT or UPDATE queries. IMHO, MySQL can only be used in an Apache environment with PERL/PHP. Also, do not use it in an ODBC environment, it is simply too buggy. This is personal experience. On the converse, if you are looking for a professional tool, go for PostgreSQL. Probably, the best way to start is http://pgadmin.postgresql.org and install pgAdmin2, PostgreSQL Windows GUI. pgAdmin2 makes programming very easy. Modern computing is "do it yourself and make your own mind". In a production environment, you will see it is not like apple:apple. Cheers, Jean-Michel POURE
hi.. i'm a newbie in postgresql and have some problem when accessing large object (image) in postgresql from different computer. for inserting the image into the database, i copy the image into the memory, then querying it into the database. and it work. i also check it with the '\lo_export' command from the psql, and it produce a file with the same size just like the original image. but i cannot load the image from the database into my application. using the packet monitor program (trafshow), the query only return a small size result. when trying to query the image using psql using 'SELECT MY_IMAGE FROM MY_TABLE WHERE condition', 'MY_IMAGE' field return some integer, and i think this called by oid (cmiiw). what method should i do to make the result of the query is returning data of the image, not the oid? fyi, i use borland cppbuilder to develop my application, and odbc for connecting the client and the database server, and the 'bytea' type variable as the type of the image field. regards, edward ps: sorry for my bad english..
On Mon, Feb 18, 2002 at 10:40:59AM +0100, Jean-Michel POURE wrote:
> Le Lundi 18 Février 2002 06:18, Andrew Sullivan a écrit :
> > and MySQL+InnoDB. It seems that such would be an apples::apples (ok,
> > maybe one's an overbred hybrid apple, but still an apple) comparison
> > at last.
>
> Dear Andrew,
>
> The main difference is that PostgreSQL allows server-side programming and
> does things smartly:
Sorry, I wasn't precise enough. I know that MySQL does not have all
the features of Postgres. I simply meant that, for ages, there have
been all sorts of benchmarks floating around purporting to show that
MySQL is faster than Postgres. None of these have ever taken into
account the transaction overhead that Postgres automatically incurs.
With InnoDB transactions, MySQL seems to have the same sort of
transactional overhead. So, a real comparison can be made.
Yes, all sort of other nice features will be missing. No-one needs
to convince me of the benefits of Postgres. But if MySQL _really_
has transactions now, and they really scale and all that, then we may
have something to learn from it (well, ok, others may have something
to learn from it. I can barely write 'Hello World' in C, so I'm not
going to be much use as a code contributor). And since InnoDB and
MySQL are both GPL'd, one would be able to examine the source for
bright ideas, if it turns out that MySQL+InnoDB is blazing fast with
(say) 50 concurrent users.
Anyway, it was just a suggestion. Sorry I brought it up, since it
has the potential to spark another holy war.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
>>>>> "Jean-Michel" == Jean-Michel POURE <jm.poure@freesurf.fr> writes:
Jean-Michel> Your email clearly shows that MySQL lacks
Jean-Michel> features. Pease keep in mind that MySQL is developped
Jean-Michel> by a single man (who accepts patches from others),
Jean-Michel> not a community of developpers like PostgreSQL.
Now this is FUD. MySQL was mainly initially developed by Monty but
gradually a team of developers was added at first by people
volunteering as with PostgreSQL then those deemed good enough
(presumably) were offered jobs to hack MySQL. Certainly since I
stopped reading the MySQL mailing list a couple of years ago this was
happening (and there were a number of people who had got jobs, 7 or 8
at least) and I doubt that it has shrunk down since.
Sincerely,
Adrian Phillips
--
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now? [OK]
Robert Berger wrote:
> This argument is out of date. MySQL currently supports
> transactions, foreign
> keys, and outer joins. (4.1 will support subselects)
>
> As for fault tolerance, MySQL has built in support for replication.
>
> A couple years ago I converted a project from MySQL to PostgreSQL
> because
> of MySQL's lack of features. I am now in the process of converting
> back to
> MySQL because of the performance improvements and replication.
Just stating "support of foreign keys" is IMHO a little
fuzzy. Are referential actions supported (ON UPDATE/DELETE
CASCADE, SET DEFAULT and SET NULL)? Can constraint checks be
deferred? Are multi-key references supported? If so, what
about match types?
I doubt that MySQL completely supports all of that. Wouldn't
surprise me if it accepts the syntax though, while stating in
the FAQ that these features are not really good, that you
don't want them and if you write your applications correnctly
don't need them anyway.
So please, what exactly does MySQL support?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Mon, 18 Feb 2002, Jan Wieck wrote: > Robert Berger wrote: > > This argument is out of date. MySQL currently supports > > transactions, foreign > > keys, and outer joins. (4.1 will support subselects) > > > > As for fault tolerance, MySQL has built in support for replication. > > > > A couple years ago I converted a project from MySQL to PostgreSQL > > because > > of MySQL's lack of features. I am now in the process of converting > > back to > > MySQL because of the performance improvements and replication. > > Just stating "support of foreign keys" is IMHO a little > fuzzy. Are referential actions supported (ON UPDATE/DELETE > CASCADE, SET DEFAULT and SET NULL)? Can constraint checks be > deferred? Are multi-key references supported? If so, what > about match types? From a quick look at the docs the docs it appears the answers are no, no, probably, I don't think so. Admittedly our support is a little weak (a few bugs, the for update locking issues, and lack of match partial), but theirs is still a little behind, although I'd guess that 4.1 will probably start adding some of these things.