Re: JDBC J/Connect driver is seriously slow against InnoDB
Mark: Once of the great things about MySQL is the prompt attention and response on this list. Thanks for replying to this email so quickly. Read on... --- Mark Matthews [EMAIL PROTECTED] wrote: j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( It's kind of a moot point anyway because those SCOPE_* columns don't really do anything as far as I can tell. But hey, you may as well be complete. B) InnoDB, on their benchmark page, say that inserting 100,000 rows into the DB is about 5 seconds. http://www.innodb.com/bench.html Can you point out where it says this? When I look at the benchmark page, I see that 100,000 inserts take 25 seconds, which JDBC can pretty much match (read on for more). Quoted (from the page in question): InnoDBMyISAM 100 000 inserts25 s. 40 s. 100 000 selects on primary key57 s. 58 s. 100 000 selects on secondary key 68 s. 95 s. True. I mistakenly mis-remembered this figure. The right figure *is* 25s. I am finding this to be more like 300-400 seconds. Try this: I think something is broken with your network, or your machine, read on for more ... I would double-check your network. I just ran your code here on my desktop runnin Linux, which is known not to have the fastest java support, to my test server (100 mbit private network), and it takes 30 seconds, which is 5 seconds longer than 100,000 inserts using DBI (which is using native code to access the database, btw), but is not long enough for HotSpot to actually optimize the code, either. You are right. It looks like some sort of network problem. I tried this on a seperate set of 2 machines both on a different network. The driver was running on a windows 2000 box (dual 1 GB) hitting the mysql box (dual xeon, 1 GB, linux) over a 100 Mbit line. This test now took about 55 seconds. I have a feeling that switching to 4.x would make things even faster, seeing as you are getting 30 seconds with a slower box. Are you using 4.x internally for your testing ? Best regards, --j __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC J/Connect driver is seriously slow against InnoDB
j.random.programmer wrote: Mark: Once of the great things about MySQL is the prompt attention and response on this list. Thanks for replying to this email so quickly. Read on... No problem! --- Mark Matthews wrote: j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( It's kind of a moot point anyway because those SCOPE_* columns don't really do anything as far as I can tell. But hey, you may as well be complete. Well, I'm all for completeness, so I'll put the columns in there, in case any tools rely on them. [snip -- removed comment about perceived performance problem] You are right. It looks like some sort of network problem. We've seen problems with various network adapters and MySQL causing problems like this, and specifically problems with network adapters 'autoconfiguring' for full-duplex (read 'misconfiguring'). Make sure that your network adapters/drivers have no unsolved issues running full-duplex, and make sure all ports in the network topology (client, hub/switch, server) are running at the same duplex (hopefully full). I tried this on a seperate set of 2 machines both on a different network. The driver was running on a windows 2000 box (dual 1 GB) hitting the mysql box (dual xeon, 1 GB, linux) over a 100 Mbit line. This test now took about 55 seconds. I have a feeling that switching to 4.x would make things even faster, seeing as you are getting 30 seconds with a slower box. Are you using 4.x internally for your testing ? Yes, I'm using 4.0.5 (from BK, so I can do SSL development, which is done, btw, so look for it in Connector/J 3.0.2, to be released later this week!) on Suse Linux, and 4.0.4 on Windows XP. BTW, if you _really_ want Inserts like your benchmark to have excellent performance (like in bulk loading situations), use the multi-value insert form: INSERT INTO TABLE foo VALUES (...), (...), (...)... Setting the max_allowed_packet to 16M for my test server, and building the statement for 100,000 rows in one shot before executing it, I was able to run your benchmark in under 5 seconds! The multiple-value insert is _much_ more efficient on the server-side, so if you can use it in your situation, I advise that you do. In the future, MySQL will support multiple queries in a single call, and when this happens Connector/J will turn Statement.addBatch()/executeBatch() type queries into this form, which will give you the same type of speedup in a JDBC-standard manner. For now, you need to do it by hand (like I outlined above). -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC J/Connect driver is seriously slow against InnoDB
Hi:
Thought I'd mention this (using the latest dev
J/connect):
A)
DatabaseMetaData.getColumns() does not return the last
4 columns (SCOPE_*) but it should according to the
API spec
B)
InnoDB, on their benchmark page, say that inserting
100,000 rows into the DB is about 5 seconds.
http://www.innodb.com/bench.html
I am finding this to be more like 300-400 seconds.
Try this:
CREATE TABLE T1
(A INT NOT NULL AUTO_INCREMENT, B INT, PRIMARY KEY(A))
TYPE=INNODB;
CREATE TABLE T2
(A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB;
Now run the java driver (source shown at the end of
this
message).
This takes a long time. The innodb bench URL mentioned
above uses perl DBI has a test driver, the JDBC
*should* be as fast if I am not missing anything (but
it's not). Once it's finally done, if you now say
(from
the mysql client):
mysql insert into T2 select * from T1;
Query OK, 107825 rows affected (5.23 sec)
Records: 107825 Duplicates: 0 Warnings: 0
Note, this takes about 5 seconds, which shows that
the slowdown is not at the DB level but at the JDBC
driver level (mysql client even sets auto commit
to true by default, and it still takes 5 seoonds).
So why does the JDBC driver take so long ? I am
accessing the mysql machine over a private 100Mbps
connection so I don't think it's the network either.
Best regards,
--j
java driver --
import java.sql.*;
import java.util.*;
import java.io.*;
public class insertTiming {
/* Change these as appropriate */
static String user=CHANGE_ME;
static String password=CHANGE_ME;
static String url=CHANGE_ME;
public static void main(String[] args) throws
Exception
{
Class.forName(com.mysql.jdbc.Driver);
Connection con =
DriverManager.getConnection(url,user,password);
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
/*
Make sure table T1 has been created prior to this
SQL command:
CREATE TABLE T1 (A INT NOT NULL AUTO_INCREMENT, B INT,
PRIMARY KEY(A)) TYPE=INNODB;
*/
con.setAutoCommit(false);
String sql =
INSERT INTO T1 VALUES (null, '1234567890');
for (int n = 0; n 10; n++) {
stmt.execute(sql);
}
con.commit();
} //~main
} //~class
--
__
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC J/Connect driver is seriously slow against InnoDB
j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( B) InnoDB, on their benchmark page, say that inserting 100,000 rows into the DB is about 5 seconds. http://www.innodb.com/bench.html Can you point out where it says this? When I look at the benchmark page, I see that 100,000 inserts take 25 seconds, which JDBC can pretty much match (read on for more). Quoted (from the page in question): InnoDBMyISAM 100 000 inserts25 s. 40 s. 100 000 selects on primary key57 s. 58 s. 100 000 selects on secondary key 68 s. 95 s. I am finding this to be more like 300-400 seconds. Try this: I think something is broken with your network, or your machine, read on for more CREATE TABLE T1 (A INT NOT NULL AUTO_INCREMENT, B INT, PRIMARY KEY(A)) TYPE=INNODB; CREATE TABLE T2 (A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB; Now run the java driver (source shown at the end of this message). This takes a long time. The innodb bench URL mentioned above uses perl DBI has a test driver, the JDBC *should* be as fast if I am not missing anything (but it's not). Once it's finally done, if you now say (from the mysql client): mysql insert into T2 select * from T1; Query OK, 107825 rows affected (5.23 sec) Records: 107825 Duplicates: 0 Warnings: 0 This isn't the same thing as what your test program is testing. This is optimized by the database, it is not even close to issuing 100,000 queries. It moves some rows from one place to another, and only has to issue _one_ query. Note, this takes about 5 seconds, which shows that the slowdown is not at the DB level but at the JDBC driver level (mysql client even sets auto commit to true by default, and it still takes 5 seoonds). So why does the JDBC driver take so long ? I am accessing the mysql machine over a private 100Mbps connection so I don't think it's the network either. I would double-check your network. I just ran your code here on my desktop runnin Linux, which is known not to have the fastest java support, to my test server (100 mbit private network), and it takes 30 seconds, which is 5 seconds longer than 100,000 inserts using DBI (which is using native code to access the database, btw), but is not long enough for HotSpot to actually optimize the code, either. I would guess you have I/O problems on one of your machines, or you're dropping a lot of packets somewhere, or you're not using the best JVM for your machine/OS combination. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
