MySQL 4.1.7 Network slowdown
We just installed MySQL 4.1.7 (we were previously using 4.0.22) Our applications have now become extremely slow. When executing queries directly on the database server, our response times are 228 rows in set (0.00 sec) When we execute them from our development servers (via mysql query browser, or using Connector/J) we get 228 rows in 5172 msec. I do not really know what to do to try to determine the cause of the slowdown. Using trace route there is only one hop directly to the DB server and large files transfer lightning quick so I do not think it is a network problem, but rather a MySQL connection problem. Can someone please provide a pointer or two to try to determine what is going on? 4.0.x was significantly faster, but I now need subqueries. Thanks in advance, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank, If you provide more informations like the query and how many rows it returns, it should be easier help you. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Thanks for the response If you provide more informations like the query and how many rows it returns, it should be easier help you. The query is: select * from cmContent; The query returns 228 rows. mysql describe cmContent; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | contentId | int(11) unsigned | | PRI | NULL | auto_increment | | name| text | | | || | publishDateTime | datetime | | | -00-00 00:00:00 || | expireDateTime | datetime | | | -00-00 00:00:00 || | contentTypeDefinitionId | int(11) | YES | | NULL || | parentContentId | int(11) | YES | | NULL || | creator | text | | | || | repositoryId| int(11) | | | 0 || | isBranch| tinyint(4) | | | 0 || | isProtected | tinyint(4) | | | 2 || +-+--+--+-+-++ Really nothing special to speak of. Thanks for helping me out, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Hmmm... The MySQL-client is 4.1.7, too? I realy don´t know if it make some difference, but perhaps it does. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
The MySQL-client is 4.1.7, too? Yes The localhost mysql command is version 4.1.7-standard The remote query browser is version 1.1.1 gamma The remote JDBC driver is version 3.0.16-ga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank, Wouldn´t it because the client driver (ODBC version)? Perhaps it´s faster in the local server because it uses a mysql-4.1.7 client. Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Frank Febbraro wrote: The MySQL-client is 4.1.7, too? Yes The localhost mysql command is version 4.1.7-standard The remote query browser is version 1.1.1 gamma The remote JDBC driver is version 3.0.16-ga Are you seeing the slowdown only from your java app? Or from all clients? In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Are you seeing the slowdown only from your java app? Or from all clients? I see this slowdown from my java app AND from the remote MySQL client application (called MySQL Query Browser 1.1.1 and 1.1.2). Basically I see the slowdown from all clients that are remote machines, but I do not see it when I execute the queries on localhost (the server on which the database is running) In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. I experienced the slowdown with the 3.0.15 driver so I upgraded to the 3.0.16 driver and have the same problem. Any insight? THanks again, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Frank Febbraro wrote: Are you seeing the slowdown only from your java app? Or from all clients? I see this slowdown from my java app AND from the remote MySQL client application (called MySQL Query Browser 1.1.1 and 1.1.2). Basically I see the slowdown from all clients that are remote machines, but I do not see it when I execute the queries on localhost (the server on which the database is running) In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. I experienced the slowdown with the 3.0.15 driver so I upgraded to the 3.0.16 driver and have the same problem. Any insight? Frank, What character set are you using in the JDBC driver? Does it happen to be utf-8? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBtfk7tvXNTca6JD8RAqIKAJ9VWXpIxD0NNJwShZrcgNpjwklJ4gCgmINX bCCMT15MyxvqqWe5G6LZ2q8= =a6bd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Got me then?! I am still running 4.0.22. Waiting for the connecter/J to come out of gamma and for 4.1.* to stabilize a bit, so I can't offer much other than what I have read. Frank Febbraro wrote: Are you seeing the slowdown only from your java app? Or from all clients? I see this slowdown from my java app AND from the remote MySQL client application (called MySQL Query Browser 1.1.1 and 1.1.2). Basically I see the slowdown from all clients that are remote machines, but I do not see it when I execute the queries on localhost (the server on which the database is running) In the changelog for the 3.0.16 connector, they disabled BufferedInput as they were having problems with it on some systems. You should be able to pass useUnbufferedInput=true in the jdbc url to get this back. I think they are working on their own implementation to get buffered input back in the new 3.1 connector. I experienced the slowdown with the 3.0.15 driver so I upgraded to the 3.0.16 driver and have the same problem. Any insight? THanks again, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
What character set are you using in the JDBC driver? Does it happen to be utf-8? I have tried it with both utf8 and latin1, no difference in delay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
I have tried it with both utf8 and latin1, no difference in delay. Sorry, to be more specific. I have tried it originally with everything as UTF-8 (database, tables, connections) I thought that the encoding could be the problem so I converted/configured everything to latin1 (ISO-8859-1) and have the same delays. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
This might be related to a bug I filed a couple months ago, assuming your server is running on Windows http://bugs.mysql.com/bug.php?id=5787 Sorry, I did not mention it, we are running on Linux, Fedora Core 3 to be exact. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
This might be related to a bug I filed a couple months ago, assuming your server is running on Windows http://bugs.mysql.com/bug.php?id=5787 Sorry, I did not mention it, we are running on Linux, Fedora Core 3 to be exact. My client machine (the remote machine in this mix) is a windows machine though -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.7 Network slowdown
I have a bunch of 4.1.7 boxes all running linux, some 32 bit and some 64 bit and I've never experienced the problem you are having. Are you have the problem if you connect from a remote linux box? The final question, does the windows box have reverse DNS setup for it? If not add it to the /etc/hosts file on your fc3 linux box. And reconnect to mysql. Donny -Original Message- From: Frank Febbraro [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 1:01 PM To: [EMAIL PROTECTED] Subject: Re: MySQL 4.1.7 Network slowdown This might be related to a bug I filed a couple months ago, assuming your server is running on Windows http://bugs.mysql.com/bug.php?id=5787 Sorry, I did not mention it, we are running on Linux, Fedora Core 3 to be exact. My client machine (the remote machine in this mix) is a windows machine though -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
The final question, does the windows box have reverse DNS setup for it? If not add it to the /etc/hosts file on your fc3 linux box. And reconnect to mysql. WOW! That was it. Things are lightning fast now. Sorry for going completely down the wrong path. What would have been the best way to figure this out, not knowing this could have been the case? I truly apprecuate the help. Regards, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.7 Network slowdown
I've been using mysql 4.1 since the first day it was out; it's all been trial and error. If I remember correctly, I found it on 4.1.0 when I was doing a processlist, and noticed that some of the boxes connecting to mysql had reverse and others didn't. So now we actually go to the /etc/hosts file and name all of our boxes something just in the hosts file so it will be easier to know exactly what everything is. You might still want to file that as a bug and you can at least have mysql determine if it's a bug or not. They will probably say it's on some documentation like on page 13,645 paragraph 12 or something so they won't consider it a bug. But at least this way somebody else may know about the problem. Donny -Original Message- From: Frank Febbraro [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 3:09 PM To: Donny Simonton; [EMAIL PROTECTED] Subject: Re: MySQL 4.1.7 Network slowdown The final question, does the windows box have reverse DNS setup for it? If not add it to the /etc/hosts file on your fc3 linux box. And reconnect to mysql. WOW! That was it. Things are lightning fast now. Sorry for going completely down the wrong path. What would have been the best way to figure this out, not knowing this could have been the case? I truly apprecuate the help. Regards, Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
I've been using mysql 4.1 since the first day it was out; it's all been trial and error. If I remember correctly, I found it on 4.1.0 when I was doing a processlist, and noticed that some of the boxes connecting to mysql had reverse and others didn't. So now we actually go to the /etc/hosts file and name all of our boxes something just in the hosts file so it will be easier to know exactly what everything is. You might still want to file that as a bug and you can at least have mysql determine if it's a bug or not. They will probably say it's on some documentation like on page 13,645 paragraph 12 or something so they won't consider it a bug. But at least this way somebody else may know about the problem. So do you think the 5+ second delay was that it was actually getting a reverse lookup, just from somewhere that responded really slow, or it tried, got some timeout, then gave up? Thanks again...so happy. Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.7 Network slowdown
It was probably attempting to do a reverse and nothing exists, so it just has to timeout. Donny -Original Message- From: Frank Febbraro [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 3:23 PM To: Donny Simonton; [EMAIL PROTECTED] Subject: Re: MySQL 4.1.7 Network slowdown I've been using mysql 4.1 since the first day it was out; it's all been trial and error. If I remember correctly, I found it on 4.1.0 when I was doing a processlist, and noticed that some of the boxes connecting to mysql had reverse and others didn't. So now we actually go to the /etc/hosts file and name all of our boxes something just in the hosts file so it will be easier to know exactly what everything is. You might still want to file that as a bug and you can at least have mysql determine if it's a bug or not. They will probably say it's on some documentation like on page 13,645 paragraph 12 or something so they won't consider it a bug. But at least this way somebody else may know about the problem. So do you think the 5+ second delay was that it was actually getting a reverse lookup, just from somewhere that responded really slow, or it tried, got some timeout, then gave up? Thanks again...so happy. Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 Network slowdown
Donny Simonton wrote: It was probably attempting to do a reverse and nothing exists, so it just has to timeout. Reverse DNS is evil... :) http://dev.mysql.com/doc/mysql/en/DNS.html You can disable DNS hostname lookups by starting |mysqld| with the |--skip-name-resolve| option. However, in this case, you can use only IP numbers in the MySQL grant tables. If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with |--skip-name-resolve| or by increasing the |HOST_CACHE_SIZE| define (default value: 128) and recompiling |mysqld|. Another issue... why was this even having a problem? Are you not running JDBC connection pooling? This should have only happened once and then moved on since the connection is open. I don't think MySQL is doing this per query. Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
