Performance Test Review of Server Databases

Discussions

News: Performance Test Review of Server Databases

  1. Performance Test Review of Server Databases (24 messages)

    A new article on eWeek tests database performance of various popular including DB2, SQL Server, MySQL, Oracle9i and Sybase. MySQL showd great performance due to a new caching feature. The article also noted that database connectivity drivers proved to be the biggest source of problems - a point also supported by the performance difference of the two published Weblogic ECperf results.

    Note the great performance of MySQL due to its new caching feature. If the text of an incoming query has a byte-for-byte match with a cached query, MySQL can retrieve the results directly from the cache without compiling the query

    See the article at: http://www.eweek.com/article/0,3658,s=702&a=23115,00.asp.

    Threaded Messages (24)

  2. I can't believe that no other databases other than MySQL have a query cache! Surely this is quite obvious - though perhaps it is far too low-brow for the likes of Oracle......
  3. Interesting results. MySQL has come a long way! I think that it is maturing into a true alternative to the big $$$$ of Oracle, IBM, and MS.

    A comment on Oracle however... Oracle does cache both queries and data. Queries are cached by making use of bind variables within the actual SQL statements. This allows Oracle to repeatedly "soft" parse the statement as it is reused. (A "hard" parse is when the statement is parsed for syntax and placed in the shared-pool for the first time.) If statements do not use bind variables each occurance of the statement that does not have the exact parameters is hard parsed, thus clutering the shared-pool and degrading performance. Data is cached each time a row is retrieved. Data is placed in the buffer cache, thus speeding recurrent access. (There are advanced features within Oracle allowing tables to specify their own buffer caches parameters but that is a topic unto itself.)
  4. MySQL as an alternative to Oracle?!?!??! In what parallel universe are those two products ever mentioned in the same breath by any sane Engineer?

    Seriously, I lost all respect for MySQL a year or two back when they had a chapter of their manual dedicated to explaining why the lack of foreign keys in MySQL was a good thing. It's not in the current version but there's an archived copy here: http://unixtech.be/docs/mysql/manual_Compatibility.html#Broken_Foreign_KEY

    They cite such reasons as not being able to "move, copy, and remove" database files via filesystem commands. They also of course mention a decrease in performance, that is after all one of the reasons they only recently implemented transactions. Yep, that's right, MySQL got it's reputation as a blazingly fast database because they didn't bother with transactions.

    http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Differences_from_ANSI is rather interesting. Make sure to read the bits on lack of subqueries (just use a temporary table or loop through in code) and data consistency through the usage of lock table.

    Oh and one last thing, it looks like views, triggers, constraints, cursors, and stored procedures are still missing (http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#MySQL-PostgreSQL_features). I've found those useful on occasion.

  5. In response to what Jonathan White was writting at the same time as my post about Postgres, I have (and use) in Postgres:

    1. Transactions (which I am KNOW have saved my butt many times)
    2. Subqueries (I did one once, I think in postgres...not sure if they are completly implemented. I don't need to use them in my postgres DB's)
    3. Constraints (PK and FK)
    4. Views...how could anyone live without views!?
    5. Triggers (which I don't use)
    6. Stored Procedures (which I don't use)

    Not sure about others. Postgres doesn't get the attention it deserves. Too many people start with MySQL because people say it is fast, and stick with that until they need a real ACID DB, then shell out the bucks. Postgres fills that void I think.

    -Pete
  6. Pete,
    I have not used pgsql in anything other than small projects but my experiences with it have been very positive. If I were struggling with a tight budget and had a strong development group, pgsql would likely be at the top of a very short list of options.

    I believe mysql gets a lot of attention due to their misleading claims. That attention combined with the ability to either use a preinstalled version or simply grab the RPMS causes people unfamiliar with relational databases to choose mysql. Let's face it, a lot of the projects which use mysql were started by developers unfamiliar with commercial databases and / or under great time and budget pressure.

    I would not tolerate a system without the six features you list, I would feel far too handicapped. Even stored procedures which (on most projects) I try to avoid have their place.

  7. I agree, it is not an alternative for mid to large solutions, but for a small departmental solution, it sure saves on $$$$.

    I am a grand Oracle disciple as I worked for the giant for 3 years, dealing largely with VLDBs, but if I can't use Oracle or DB2, then I'd choose MySQL or Postgres rather than M$ any day. I agree, the MySQL folks don't get what relational databases are all about regarding FKs, stored procs, sub-queries, etc. I don't know how many times I've used correlated sub-queries...
  8. the MySQL folks don't get what relational databases are

    >all about regarding FKs, stored procs, sub-queries.

    Strange. I always thought that relational databases are all about Codd's theory ;-)

    --
    Dimitri
  9. What a pitty that they havn't choosen JSQLConnect from J-netdirect... We evaluated different JDBC-Drivers for use with SQL-Server 7.0 8 months back and JSQLConnect tourned out to be by far the best performing (we tested 4 different drivers)

    cheers,

    Manuel
  10. Manuel,

    Can we take this discussion offline? I like MS SQL Server 7.0 (and 2000) simply because its lean and mean and has these excellent tools for designing & maintaining databases. (It runs with absolutely no problems even on a laptop)

    What I am looking for are your thoughts on the JDBC drivers you have evaluated. My mail address is ksubramanian at borland dot com

    -krish
  11. Hi Krishnan,

    funny that its a Borland-guy, who asks me about this. Funny because we evaluated the JDBC-Drivers for MS-SQL 7 in conjunction with BAS 4.5.1, which we now have in production at germanys leading ticket selling platform since september 2001...

    I mail you, when I'm back in office...

    cheers,

    Manuel

  12. Bloody MySQL!

    If it was actually a Relational Database then these sorts of figures would be impressive, but really it is more akin to a SQL wrapper to the filesystem. No wonder it outperforms other systems.

    From the MySQL documentation:

    Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied, and removed.

    The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order, anyway.
    There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.

    ----------

    For further rants about database theory see: http://www.dbdebunk.com/
  13. hehe... I come from a database background. I've profiled just about every database out there for speed and features. What you are seeing on his site is MySQL under windows as well. MySQL, from my tests, is quite a bit faster in a Unix-ish environment. I think it has to do with the filesystem overhead of windows. Also, you are seeing InnoDB(www.innodb.com), which supports transactions and foreign keys. It's ACID compliant, and will probably perform better under windows if you use the raw partition table space storage option :). Not as easy to set up, but it's been good for me. InnoDB, in my calculations doing statistical calculations on about two million stock option entries, is about three times faster than PostgreSQL. (These were the older versions.. 3.23.4? (2-3) and PostgreSQL 7.1.2) MySQL vs PostgreSQL using MyISAM table types, PostgreSQL was faster.

    That being said, MySQL+InnoDB is great for application servers because it is ACID compliant, supports FK's, supports row level locking, is free, and is really fast. You can do strange things with MySQL like compiling in your own funtions. This is in a way stored procedures, but they aren't user friendly :). Stored procedures will be out in the very near future for MySQL.

    PostgreSQL is my DBMS of choice for development because I'm a DB guru, and am a lot afraid of an app server holding my hand and making queries for me. The stored procedures are very similar to Oracle. It supports everything, and I use everything it supports. (Except it doesn't support partitioning yet) Some of the things I see people not use that really drives me mad are: clustered keys, custom data types, rules, and the crazy constraints it supports. You have severel index types to choose from as well. If you are doing any application where you need to store shapes in a database, PostgreSQL is better than ANY other DBMS I've seen. It has special index types, special data types, and special functions for dealing with any geometric shapes. The bad side is, it's not all that good at data that gets deleted and inserted. You have to lock tables, at least somewhat, to reclaim deleted rows in a table. If there are a lot of gaps, you can run into serious problems. The row size of any table is limited. The reason why PostgreSQL is slower in my opinion is that they are pretty much the only one's not on top of the query cache algorithms as far as I can tell. I know DB2, Oracle, and MySQL(now) supports query caching. PostgreSQL is going to make a great DBMS when they get the rest of the "Vaccuum" problems worked out, and get an implementation of the two phase commit going good on partitioned databases.

    BTW, are there any appservers that support CMP partitioning? Would be cool to configure a BMP bean to partition the data itself :) Would work good as long as your transactions don't span two DBMS's... some thought required :) (simplest way would penalize inserts/updates at the advantage of faster selects by implementing your own 2 phase commit with an IsCommitted column for inserts and an OldKey column for updates (insert with history, or just delete old keys and set column null after committed)) Anyone ever tried that? Theoretically, after about 6 machines, you'll not loose anything really on updates/inserts, but will gain on average 3x (1-6x) the query speed.
  14. I don´t take this serious[ Go to top ]

    Dear friends,

    Having done and still doing many a relational database performance test I have to give this review a really big laugh! Please be serious.

    I have seen test results by many vendors on many platforms and all results are different. In the real world we drive our applications by real products and we know if it works or not and how fast. Have been doing this for fourteen years now. It is difficult to measure but some things are clear right away:

    MySQL is not a relational database to reckon with. DB2 you only take when you run IBM hardware and want to do so for ever. MS SQL Server you only take when you run MS hardware and want to do so for ever and when you don´t mind low performance, bad scalability and low availability. For real big business there are only two products, Oracle and Sybase. Oracle has the better market share and Sybase is cheaper and faster. Simple as that. We use them both.

    These facts have not changed for some time now.

    Grtz,

    Hans van Buuren
    Senior Systems Architect
    Regional Amsterdam Police Force
    The Netherlands
  15. I don´t take this serious[ Go to top ]

    "DB2 you only take when you run IBM hardware and want to do so for ever. MS SQL Server you only take when you run MS hardware and want to do so for ever and when you don´t mind low performance, bad scalability and low availability"

    I don't really see why you only run db2 on ibm hardware and want to do so forever!?!? I think that these statements are personal oppinions and have no facts backing them up.

    We use both DB2 and MS SQL Server in our product and have made performance tests on on several plattforms. For example Windows 2000 Server, AIX-RS/6000, OS/400-AS/400, Sun Solaris-Sparc (SQL Server of course only on win2k) and have good performance on (almost) all of them. Surprisingly (perhaps) the configuraion with Mandrake on Intel for our java app server and MS SQL Server/win2k leads both the _performance_ and the price/performance comparisons by far.

    For DB2 we use the ibm shipped jdbc driver and for MS SQL Server we use i-net Sprinta2000 from http://www.inetsoftware.de
    I think that this is really the issue, which jdbc driver you are using. Read the last page of the eWEEK test or just have to look at the latest ecperf result from bea to see what the difference can be by only changing jdbc driver.
    http://ecperf.theserverside.com/ecperf/index.jsp?page=results/top_ten_performance

    If we are disussing pure database benchmarks the TPC-C results ought to be interesting
    http://www.tpc.org/
    Both DB2 and SQL Server are performing pretty good ;o)

    /Richard
  16. "If we are disussing pure database benchmarks the TPC-C results ought to be interesting
                      http://www.tpc.org/
                      Both DB2 and SQL Server are performing pretty good ;o) "

    Only in shared nothing clusters( No option for OLTP ). It may make sense for DSS. Otherwise is all Oracle and SymfoWARE . ( TPC-C - Non clustered ) :-)
  17. I don´t take this serious[ Go to top ]

    I would have liked to see Sybase Adaptive Server Anywhere (ASA) in the comparision as well. The article suggests that the limitations encountered with Sybase were in the jConnect driver, which is also used for ASA.

    ASA came from the old Watcom database, and is very clean, fast, and feature-rich--and is way (10x-100x) less expensive than the big iron DBs. Major Java support in the DB too. Lots of people think Sybase plays it down so it doesn't compete as much with Sybase ASE.

    -J
  18. <quote>
    PostgreSQL is my DBMS of choice for development because I'm a DB guru, and am a lot afraid of an app server holding my hand and making queries for me. The stored procedures are very similar to Oracle. It supports everything, and I use everything it supports.
    </quote>

    How about cross-db queries? AFAIR, cross-db queries were/are considered to be an "exotic feature" according to PostgreSQL ToDo list. I lost my interest in PostgreSQL after that.
  19. PostgreSQL cross-db[ Go to top ]

    http://candle.pha.pa.us/mhonarc/todo.detail/crossdb/msg00001.html should detail why it isn't done even though it is exotic. At this very moment, they have replication and data partitioning to be their most urgent issues. This means they will soon have that two-stage commit it requires to do cross-db queries. The entire 7.2 release was to get ready for this.

    http://developer.postgresql.org/todo.php is the big todo list for anyone interested. Just because it's labeled exotic, doesn't mean there isn't some hacker out there waiting for the right moment to do it right :)

    Technically... it's on the page twice... look under urgent, and you'll see cross-db queries as well :)

    The biggest thing about PostgreSQL that I don't like is the course grained security. If it gets fine grained security and partitioned data, I would be estatic :) Or if MySQL gets subqueries, partitioned tables, and stored procedures, I would be thrilled.

    Not that I'm above commercial databases. I like Oracle and DB2 quite a bit, but the draconian licensing scheme is a bit more than I can handle. I make software that will be installed in several places. Every dollar Oracle or IBM claims is overhead. I can't justify spending 10,000$ on software to be faster when a 5,000$ disk array + MySQL or PostgreSQL is much more cost effective. If you have to build an SQL server for less than 100,000$, you can just about always do better, performance wise, by spending 80% or more on hardware. If you get better hardware, Oracle and IBM will just charge more.

    If I didn't forsee PostgreSQL getting partioned data, and the need for scalability in my app, I would stick with C++ and MySQL. The prospect of throwing more cheap servers at the problem is quite attractive :) J2EE+PostgreSQL should become a very attractive combination with respect to !/$ (read bang per buck) and scalability. I'm thinking another 3-6 months before it happens though. PostgreSQL is usually only released on three month intervals. Maybe JBoss or JOnAS will get up to commercial grade scalability, and then you can spend money where it really counts, development and hardware. You absolutly must have a good disk array and backup system. If one spends less on those than the software to use them, then they have serious priority issues :)
  20. Toby Writes:

    "Bloody MySQL!

    If it was actually a Relational Database then these sorts of figures would be impressive, but really it is more akin to a SQL wrapper to the filesystem. No wonder it outperforms other systems.

    From the MySQL documentation:

    Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied, and removed. "

    --
    You haven't looked at MySQL, lately, then. With the InnoDB table type, you get almost everything you need (except sub-selects, which can be an issue). But for many people the price/performance ratio is hard to beat. One thing that other databases don't have is the ability to choose when you need full-blown ACID, or lightweight read-only type tables, or only in-memory tables...Which gives the application architect quite a few options for optimizing the database.

    Also, you should notice in the article that the JDBC driver for MySQL is the only driver besides Oracle's that actually could handle the load in a stable fashion (once again, consider the price/performance ratio). I'm biased (as I'm the author of the driver), but I would say that MM.MySQL (the JDBC driver) is one of the most feature-complete and stable JDBC drivers out there.
  21. Hi Mark,

    I use your driver (and MySQL) for most of my own stuff! So thanks! And I use it because the price performance ration is pretty impossible to beat.

    Its just one of my pet hates, is all ... MySQL is not in the same catergory as Oracle, Sybase, DB2, SQL Server, and I think it is a misrepresentation to place in line with these other types of systems. And if it is a RDBMS you can't choose ACID, that's one of the crucial defining elements of RDBMS in the first place! This is purely a semantic issue and I know I am being overly pointy-headed about it, so my apologies.

    I have not used InnoDB table types, but thanks for the tip I will investigate them for my own work...

  22. Toby Hede writes:

    "Its just one of my pet hates, is all ... MySQL is not in the same catergory as Oracle, Sybase, DB2, SQL Server, and I think it is a misrepresentation to place in line with these other types of systems. And if it is a RDBMS you can't choose ACID, that's one of the crucial defining elements of RDBMS in the first place! This is purely a semantic issue and I know I am being overly pointy-headed about it, so my apologies.

    I have not used InnoDB table types, but thanks for the tip I will investigate them for my own work... "

    You really should investigate InnoDB. With InnoDB you get ACID, row-level locking, foreign key support, etc. That should put a stop to most complaints that MySQL is a "toy" database because it doesn't have ACID. And they great thing as I said before is that you get to choose...Not all tables in a database require ACIDity (lookup-type tables come to mind). When you pair that with the tool support, the cost, the hardware requirements, and the performance, it's hard to beat.

    If you want to get nitpicky, E.F. Codd (the inventor of the Relational Model), does not mention ACIDidity in the requirements for a relational database system, and in fact there are not any commercially-available implementations of what could scientifically be called a "Relational" database managment system.
  23. Cool, thanks

    I know, what we have are 'SQL Databases', which are just not the RealThing(tm). I don't know if you're a fan of Fabian Pascal, but he has heaps to say on these issues.

    Anyway ...
  24. I wish postgres had been included. Postges is the Open Source DB for people who need things like transactions and more types of joins.

    We get what I would like to believe is good results with Postgres, on some databases with a couple tables hitting the scales at 5 to 6 million records.

    I thought DB2 would have fared better.

    -Pete
  25. Similarly, I would have liked to have seen SapDB as part of the comparison. It works quite nicely for us, handles transactions and all sorts of joins, cursors, stored procedures, constraints, etc.

    Cheers
    Ray