Re: Very slow to connect
increase the number of connections allowed, that might be it On Mon, 29 Jan 2001, Leonardo Dias wrote: I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. a mysqladmin status shows the following: Uptime: 441237 Threads: 29 Questions: 23849146 Slow queries: 11390 Opens: 90355 Flush tables: 8 Open tables: 128 Queries per second avg: 54.051 We know that 54 queries per second should be a lot, but it's not. We've already had more traffic than that. A show processlist rarely shows more than 30 processes. The machine is a Linux SMP with 2 CPUs PIII 800MHz and 1G of RAM. What could possibly be wrong? Also, our setup for file-max is big enough to support lots of connections. We, of course, do lots of concurrent updates and selects in some (not all) tables. For the MySQL specialists out there, these are the variables returned by a show variables. Are there any values that could be changed so that the connection wouldn't be so slow? Are there people with the same problems out there? I'm waiting for a shout from the wild... :) Thanks. -- -Spinlock EmpireQuest Creator http://www.empirequest.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: Very slow to connect
Is this perhaps related to the 'slow thread creation' 'feature' of some linux kernels? http://lists.mysql.com/php/search.php?ps=10q=fast+thread+creation+ps=20m= and -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Leonardo Dias Sent: 29 January 2001 15:41 To: [EMAIL PROTECTED] Subject: Very slow to connect I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. a mysqladmin status shows the following: Uptime: 441237 Threads: 29 Questions: 23849146 Slow queries: 11390 Opens: 90355 Flush tables: 8 Open tables: 128 Queries per second avg: 54.051 We know that 54 queries per second should be a lot, but it's not. We've already had more traffic than that. A show processlist rarely shows more than 30 processes. The machine is a Linux SMP with 2 CPUs PIII 800MHz and 1G of RAM. What could possibly be wrong? Also, our setup for file-max is big enough to support lots of connections. We, of course, do lots of concurrent updates and selects in some (not all) tables. For the MySQL specialists out there, these are the variables returned by a show variables. Are there any values that could be changed so that the connection wouldn't be so slow? Are there people with the same problems out there? I'm waiting for a shout from the wild... :) Thanks. -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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: Very slow to connect
I bet your webserver and database server are seperate machine. Make sure the hostname of web and db are in the /etc/hosts on both machine, it's very slow to use DNS to resolve everything That doesn't matter. We use IP to connect, not hostnames. It might be a problem with the slow pthread_create() thingy I've been reading. Although there was a lot of discussion, they didn't come up with a fix that could deal with this cleanly. And we DO need some solution here. Does anyone have any other ideas? Increasing the max_connections number to more than 500 won't do it. The kernel we're using is 2.2.17. I believe that upgrading it to 2.4 won't help either. Am I right or totally wrong? -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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: Very slow to connect
Leonardo Dias wrote: I bet your webserver and database server are seperate machine. Make sure the hostname of web and db are in the /etc/hosts on both machine, it's very slow to use DNS to resolve everything That doesn't matter. We use IP to connect, not hostnames. But if you use IP the mysql server may still try to resolve the host name to for access checks. So make sure you have the IP in the /etc/hosts of the server for the machine you are connecting from . - 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: Very slow to connect
[1 text/plain; us-ascii (7bit)] I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. Use connection caching, e.g. mod_perl with Apache::DBI. Based on you idea and what I have found in the MySQL manual, I decided to use the thread_cache_size. I put it up to 20. I also increased the thread_stack to 128K. Now we have solved our problems with slow connections and found a new one: slow queries. They have increased a lot now that our users are able to connect to the database and do their favorite concurrent inserts, concurrent updates and concurrent selects. Solved a problem. Found a new one. -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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: Very slow to connect
[1 text/plain; us-ascii (7bit)] I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. Use connection caching, e.g. mod_perl with Apache::DBI. Based on you idea and what I have found in the MySQL manual, I decided to use the thread_cache_size. I put it up to 20. I also increased the thread_stack to 128K. Now we have solved our problems with slow connections and found a new one: slow queries. They have increased a lot now that our users are able to connect to the database and do their favorite concurrent inserts, concurrent updates and concurrent selects. Solved a problem. Found a new one. Concurrent updates and mysql aren't the best of friends, yet. (Perhaps DBD and/or that new geminii stuff can fix some of your problems? Although geminii will probably cost you...) A lot of the slow queries can be solved by making better tables, sometimes even de-normalizing them if that can prevent a join, by sticking the numeric data into seperate tables (variable length stuff kills performance), etc. Standard procedure :) But in the end, if you have tons of concurrent updates then you might want to consider alternatives to MySQL. (postgres, frontbase, etc.) - 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: Very slow to connect
Concurrent updates and mysql aren't the best of friends, yet. (Perhaps DBD and/or that new geminii stuff can fix some of your problems? Although geminii will probably cost you...) A lot of the slow queries can be solved by making better tables, sometimes even de-normalizing them if that can prevent a join, by sticking the numeric data into seperate tables (variable length stuff kills performance), etc. Standard procedure :) We know. We've been through this. The problem with MySQL is that concurrent updates can't use row locking, but table locking. That means that the whole table will be locked until the updates are finished. This isn't good. Row locking based on the primary key would be A LOT faster. But in the end, if you have tons of concurrent updates then you might want to consider alternatives to MySQL. (postgres, frontbase, etc.) Indeed. We might give it a try someday. Although they don't have LIMIT (which provides a nice "paging" system). We use LIMIT everywhere here. It's a nice functionality if you're doing searches all the time. -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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: Very slow to connect
You don't seem to have mentioned how much memory you have. On 29 Jan 2001, at 14:52, Leonardo Dias wrote: [1 text/plain; us-ascii (7bit)] I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. Use connection caching, e.g. mod_perl with Apache::DBI. Based on you idea and what I have found in the MySQL manual, I decided to use the thread_cache_size. I put it up to 20. I also increased the thread_stack to 128K. Now we have solved our problems with slow connections and found a new one: slow queries. They have increased a lot now that our users are able to connect to the database and do their favorite concurrent inserts, concurrent updates and concurrent selects. Solved a problem. Found a new one. -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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 John Jensen 520 Goshawk Court Bakersfield, CA 93309 - 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: Very slow to connect
[1 text/plain; us-ascii (7bit)] I don't know if any of you people have ever had this trouble, but it's been a messy one here. Whenever a website of ours get lots of traffic, MySQL gets too slow to connect. Whenever it connects, the queries are fast. Since lots of our scripts relies on database connections, it has become a big problem to our website and we've been unable to answer to our costumers what is going on. You don't seem to have mentioned how much memory you have. 1Gb. -- Leonardo Dias Catho Online WebDevelopper http://www.catho.com.br/ - 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
