Re: Idea to speed up multiple jdbc connections?
Marc mysqld runs on a very powerful Operton machine with 16GB memory and barely any other application process running, it is hard to believe that a simple select that runs under 2 second will utilize all the resources...that is why I tend to think there is something in the mysql set up that caused this...any idea where I should look? BTW: the numbers are in milliseconds Thanks Haitao --- Marc Slemko [EMAIL PROTECTED] wrote: On Mon, 7 Jun 2004, Haitao Jiang wrote: Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Umh... if your queries are limited by some bottleneck on the server (such as, for example, CPU) then why would running them in parallel make it any faster? It seems that in the sequential case they are taking a total of 1922 (whatever those units are) while in the parallel case they are taking 1923. What this is telling you is that, in this case, a single query is able to fully utilize the resources (likely CPU given these numbers, although it is possible it could be disk) on the server. If a single query can fully utilize the server, all that adding more concurrency can possibly do is slow the total throughput down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
On Mon, 7 Jun 2004, Haitao Jiang wrote: Marc mysqld runs on a very powerful Operton machine with 16GB memory and barely any other application process running, it is hard to believe that a simple select that runs under 2 second will utilize all the resources...that is why I tend to think there is something in the mysql set up that caused this...any idea where I should look? How many processors? If there is only one and the query is CPU bound (as it probably is if everything is cached, given 16 gigs of ram), then why shouldn't it use all the CPU? Or, to phrase the question differently: why should the query take 2 seconds to run if there are free resources? Now, on a multiprocessor box it clearly starts to get more complicated. mysql has no capability to spread one query across multiple CPUs in parallel, and while it can spread multiple queries across CPUs the scalability has its limits. The fact that is a simple query is irrelevant (some of the simplest can be the slowest if it has to do a full table scan). From the fact that it takes 2 seconds it is clear it is not an entirely trivial query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Each of 4 individual query only took 0.6 seconds, there is no other clients, it hardly to believe taht mysql query performance will degrade 300% (from 0.6s to ~1.9s) if we have 4 concurrent connections... As far as I know, MySQL should be able to handle hundreds of connections on a single CPU box without degrading performance like above. Thanks HT --- Marc Slemko [EMAIL PROTECTED] wrote: On Mon, 7 Jun 2004, Haitao Jiang wrote: Marc mysqld runs on a very powerful Operton machine with 16GB memory and barely any other application process running, it is hard to believe that a simple select that runs under 2 second will utilize all the resources...that is why I tend to think there is something in the mysql set up that caused this...any idea where I should look? How many processors? If there is only one and the query is CPU bound (as it probably is if everything is cached, given 16 gigs of ram), then why shouldn't it use all the CPU? Or, to phrase the question differently: why should the query take 2 seconds to run if there are free resources? Now, on a multiprocessor box it clearly starts to get more complicated. mysql has no capability to spread one query across multiple CPUs in parallel, and while it can spread multiple queries across CPUs the scalability has its limits. The fact that is a simple query is irrelevant (some of the simplest can be the slowest if it has to do a full table scan). From the fact that it takes 2 seconds it is clear it is not an entirely trivial query. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
On Tue, 8 Jun 2004, Haitao Jiang wrote: Each of 4 individual query only took 0.6 seconds, there is no other clients, it hardly to believe taht mysql query performance will degrade 300% (from 0.6s to ~1.9s) if we have 4 concurrent connections... As far as I know, MySQL should be able to handle hundreds of connections on a single CPU box without degrading performance like above. You are completely missing the point. It is nothing to do with concurrent _connections_ it has to do with running concurrent _queries_. What you are saying is like well, if you can sit down and solve this equation in 10 minutes, why does it take you 40 minutes to solve 4 different equations? There is no magic way for the machine to do a hundred things at once on a single processor (assuming you don't yet have a quantum computer), they all get run for brief periods interleaved with one another. If you are running 4 at once, then each will only run 1/4 of the time. The box is working as hard as it can to process one query, do you think it should slow down how quickly it processes one concurrent query just so that number will change less if you have more than one? I'll repeat what I said before: a query that takes 600ms on such a machine is not a trivial query. If you real question is why is my query so slow then you should probably ask that instead of getting confused about why your machine can't do 4 things at once. P.S. Please do not go around reposting your same question on multiple lists, it has already been answered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Idea to speed up multiple jdbc connections?
Hi, I would appreciate any help on this: I got approximate same timing on following two: case 1: create 1 jdbc connection and issue 4 queries sequentially case 2: create 4 jdbc connections and issue 4 queries via 4 different threads at the same time The timing is done around statement.execQuery(query), so overhead of multithreading can be ignored. I would think case 2 should be faster, but it was not. Any idea? Thanks a lot PS: the mysqld server 4.1.1a is running with 16 threads __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Idea to speed up multiple jdbc connections?
AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Best Regards, Jonathan Chiu OOCL Logistics Unit 1, 4/F., Sun Hung Kai Centre, 30 Harbour Road, Wanchai TEL: 852 . 2990 0174 FAX: 852 . 28249017 -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 8:14 AM To: mysql Subject: Idea to speed up multiple jdbc connections? Hi, I would appreciate any help on this: I got approximate same timing on following two: case 1: create 1 jdbc connection and issue 4 queries sequentially case 2: create 4 jdbc connections and issue 4 queries via 4 different threads at the same time The timing is done around statement.execQuery(query), so overhead of multithreading can be ignored. I would think case 2 should be faster, but it was not. Any idea? Thanks a lot PS: the mysqld server 4.1.1a is running with 16 threads __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] IMPORTANT NOTICE Email from OOCL is confidential and may be legally privileged. If it is not intended for you, please delete it immediately unread. The internet cannot guarantee that this communication is free of viruses, interception or interference and anyone who communicates with us by email is taken to accept the risks in so doing. Without limitation, OOCL and its affiliates accept no liability whatsoever and howsoever arising in connection with the use of this email. Under no circumstances shall this email constitute a binding agreement to carry or for provision of carriage services by OOCL, which is subject to the availability of carrier's equipment and vessels and the terms and conditions of OOCL's standard bill of lading which is also available at http://www.oocl.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Thanks Haitao --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Idea to speed up multiple jdbc connections?
Oppz! Sorry for overlooking your timing method. In this case, I believe if you run the query in four different machines at the same time, the statistics should almost the same as running four consecutive queries in the same machine. I believe the multi-threading implemented in the JVM and the OS is not parallelly the same! Best Regards, Jonathan Chiu OOCL Logistics Unit 1, 4/F., Sun Hung Kai Centre, 30 Harbour Road, Wanchai TEL: 852 . 2990 0174 FAX: 852 . 28249017 -Original Message- From: Haitao Jiang [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 08, 2004 12:06 PM To: Mark Matthews; JONATHAN CHIU (ISD-OLAPL/HKG) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Idea to speed up multiple jdbc connections? Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Thanks Haitao --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ IMPORTANT NOTICE Email from OOCL is confidential and may be legally privileged. If it is not intended for you, please delete it immediately unread. The internet cannot guarantee that this communication is free of viruses, interception or interference and anyone who communicates with us by email is taken to accept the risks in so doing. Without limitation, OOCL and its affiliates accept no liability whatsoever and howsoever arising in connection with the use of this email. Under no circumstances shall this email constitute a binding agreement to carry or for provision of carriage services by OOCL, which is subject to the availability of carrier's equipment and vessels and the terms and conditions of OOCL's standard bill of lading which is also available at http://www.oocl.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
On Mon, 7 Jun 2004, Haitao Jiang wrote: Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Umh... if your queries are limited by some bottleneck on the server (such as, for example, CPU) then why would running them in parallel make it any faster? It seems that in the sequential case they are taking a total of 1922 (whatever those units are) while in the parallel case they are taking 1923. What this is telling you is that, in this case, a single query is able to fully utilize the resources (likely CPU given these numbers, although it is possible it could be disk) on the server. If a single query can fully utilize the server, all that adding more concurrency can possibly do is slow the total throughput down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
