[Monetdb-developers] JDBC/MonetDB performance test
Inspired by a student's preliminary test program, I conducted a -- at first quite innocent -- experiment to see how 'fast' MonetDB is and it's upcoming major release; MonetDB/Five.
The conducted experiment mainly consists of inserting 8000, 19200 and 152800 tuples into three tables respectively. There are no constraints in use, as such it is quite 'dumb', but aggressive inserting. This inserting of values was done in three different ways: 1) using plain INSERT INTO statements (DBTestI) 2) using INSERT INTO statements batched by the JDBC driver (DBTest) [1] 3) using PreparedStatements to insert the date (DBTestP)
I timed the wall-clock times of PostgreSQL 8.0.3 (PG8), MySQL 4.0.25 with InnoDB (My4), MonetDB 4.8.2 (M4) and todays CVS version of MonetDB/Five (M5). The JDBC drivers used for PostgreSQL and MySQL were the latest stable versions available, for MonetDB the released version and a progressive/experimental version were tested. This is marked with an 's' for the stable released version and 'p' for the progressive version. The databases run on a Dual Athlon MP 1600+ with 1GB of main-memory running Gentoo Linux with a 2.6.12 kernel (pegatoo). Two series of tests were performed; a local and remote version. In the local variant both the client application (with JDBC driver) and database server run on the same machine (as mentioned before). In the remote variant the client is on a different machine that is hardware wise equal to the machine that runs the database (crux). Both machines are directly connected to each other.
The results follow in the table below:
PG8 My4 M4s M4p M5s M5p JDBC local - DBTestI 37s 27s 90s 86s 62s 60s - DBTest 19s 27s 60s 59s 46s 46s - DBTestP 14s 27s 63s 64s 59s 60s JDBC remote - DBTestI 49s 37s 84s 85s 62s 62s - DBTest 19s 37s 57s 58s 43s 43s - DBTestP 14s 37s 66s 62s 53s 53s JDBC remote [2] - DBTestI 197s 168s >1201s >1545s - DBTest 67s 168s 76s 77s 61s 63s - DBTestP 68s 168s 75s 77s 66s 70s
The results are somewhat 'confusing'.
[1] Note: the program was not called DBTestB because it was submitted by the students under this name. [2] To simulate a slow link, we used an ssh tunnel to the database server as connection link, instead of a direct connection
Can you say anything about the default settings for logging and TM management. It is known that such facors may greatly impact perceived performance.
regards, Martin
Fabian Groffen wrote:
Inspired by a student's preliminary test program, I conducted a -- at first quite innocent -- experiment to see how 'fast' MonetDB is and it's upcoming major release; MonetDB/Five.
The conducted experiment mainly consists of inserting 8000, 19200 and 152800 tuples into three tables respectively. There are no constraints in use, as such it is quite 'dumb', but aggressive inserting. This inserting of values was done in three different ways:
- using plain INSERT INTO statements (DBTestI)
- using INSERT INTO statements batched by the JDBC driver (DBTest) [1]
- using PreparedStatements to insert the date (DBTestP)
I timed the wall-clock times of PostgreSQL 8.0.3 (PG8), MySQL 4.0.25 with InnoDB (My4), MonetDB 4.8.2 (M4) and todays CVS version of MonetDB/Five (M5). The JDBC drivers used for PostgreSQL and MySQL were the latest stable versions available, for MonetDB the released version and a progressive/experimental version were tested. This is marked with an 's' for the stable released version and 'p' for the progressive version. The databases run on a Dual Athlon MP 1600+ with 1GB of main-memory running Gentoo Linux with a 2.6.12 kernel (pegatoo). Two series of tests were performed; a local and remote version. In the local variant both the client application (with JDBC driver) and database server run on the same machine (as mentioned before). In the remote variant the client is on a different machine that is hardware wise equal to the machine that runs the database (crux). Both machines are directly connected to each other.
The results follow in the table below:
PG8 My4 M4s M4p M5s M5pJDBC local
- DBTestI 37s 27s 90s 86s 62s 60s
- DBTest 19s 27s 60s 59s 46s 46s
- DBTestP 14s 27s 63s 64s 59s 60s
JDBC remote
- DBTestI 49s 37s 84s 85s 62s 62s
- DBTest 19s 37s 57s 58s 43s 43s
- DBTestP 14s 37s 66s 62s 53s 53s
JDBC remote [2]
- DBTestI 197s 168s >1201s >1545s
- DBTest 67s 168s 76s 77s 61s 63s
- DBTestP 68s 168s 75s 77s 66s 70s
The results are somewhat 'confusing'.
[1] Note: the program was not called DBTestB because it was submitted by the students under this name. [2] To simulate a slow link, we used an ssh tunnel to the database server as connection link, instead of a direct connection
This SF.Net email is sponsored by: Power Architecture Resource Center: Free content, downloads, discussions, and more. http://solutions.newsforge.com/ibmarch.tmpl _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
Ah, I forgot to say this. Of course logging by the JDBC driver was switched off. The program starts a transaction by turning off auto commit mode, and commits after it has done all inserts.
An additional test could be to enable foreign/primary keys in the scripts such that the engine has to peform some work while inserting. This won't make us faster on single inserts, so I'll try to improve that this weekend first.
Martin Kersten wrote:
Can you say anything about the default settings for logging and TM management. It is known that such facors may greatly impact perceived performance.
On 14-10-2005 23:43:33 +0200, Fabian Groffen wrote:
The results follow in the table below:
PG8 My4 M4s M4p M5s M5pJDBC local
- DBTestI 37s 27s 90s 86s 62s 60s
- DBTest 19s 27s 60s 59s 46s 46s
- DBTestP 14s 27s 63s 64s 59s 60s
JDBC remote
- DBTestI 49s 37s 84s 85s 62s 62s
- DBTest 19s 37s 57s 58s 43s 43s
- DBTestP 14s 37s 66s 62s 53s 53s
JDBC remote [2]
- DBTestI 197s 168s >1201s >1545s
- DBTest 67s 168s 76s 77s 61s 63s
- DBTestP 68s 168s 75s 77s 66s 70s
We have gone through some extensive optimalisations on all levels of the database, in order to improve the performance. I conducted the same experiments as before, excluding the remote over an SSH tunnel and the tests with the 'progressive' JDBC driver. The results can be seen below.
JDBC local - DBTestI 37s 27s 49s xxs 39s xxs - DBTest 19s 27s 27s xxs 19s xxs - DBTestP 14s 27s 28s xxs 23s xxs JDBC remote - DBTestI 49s 37s 56s xxs 49s xxs - DBTest 19s 37s 28s xxs 21s xxs - DBTestP 14s 37s 29s xxs 25s xxs
A new round of optimisations, focussed on minimising the communication overhead, is about to start, in which we hope to be able to get at least a performance level between PostgreSQL and MySQL. On a concluding note, I see a 40-50% improvement on the previous numbers and the current numbers, so I guess in total some major steps have been made.
A new round of MAPI protocol changes (sorry Steffen ;) ) also means a new round of performance figures. This time I rearranged the tables to deal with MonetDB or its successor MonetDB/Five only. To keep a bit of track on what we have gained, I decided to keep the previous results around. Hence, I included Mx1, Mx2 ... MxN in the tables, where x = 4 for MonetDB and x = 5 for MonetDB/Five. The 1, 2 ... N represent the natural order in which the experiments were conducted. For the record and future generations: Mx1: MAPI/SQL protocol as used for ages Mx2: blockmode protocol opmisation: suppres empty blocks, and use smaller ints (2 byte iso 4). These experiments were unfortunately only conducted on MonetDB. Mx3: first optimisaton to MAPI/SQL by compressing the number of headers per result. 4 to 5 headers/lines could be combined into 1. Mx4: continuation of the first optimisation by removing the prompt (= one line) in blockmode streams, resulting in for updates only one line sent by the server to the client instead of 2 (used to be 4).
PG8 My4 M41 M42 M43 M44 JDBC local - DBTestI 37s 27s 90s 62s 49s 39s - DBTest 19s 27s 60s 48s 27s 20s - DBTestP 14s 27s 63s 61s 28s 22s JDBC remote - DBTestI 49s 37s 84s ??s 56s 48s - DBTest 19s 37s 57s ??s 28s 21s - DBTestP 14s 37s 66s ??s 29s 22s
PG8 My4 M51 M52 M53 M54 JDBC local - DBTestI 37s 27s 62s ??s 39s 30s - DBTest 19s 27s 46s ??s 19s 13s - DBTestP 14s 27s 59s ??s 23s 18s JDBC remote - DBTestI 49s 37s 62s ??s 49s 40s - DBTest 19s 37s 43s ??s 21s 14s - DBTestP 14s 37s 53s ??s 25s 18s
participants (2)
-
Fabian Groffen -
Martin Kersten