Tweet
Cannot use mysql_connect? Why!
Hi. My boards at [url]www.romanceforums.com[/url] have been down lately, and my host, rackspace told me:
[quote]
Suggested Answer
---------------------------------------------------------------
At 06/19/2001 01:00 AM we wrote - While I was not able to thoroughly go through all of the pages for your sites, I can tell you that one of your problems causing mysql to use all of your memory is the way your web pages are connecting to the MySQL server. I found that in in one of the first pages I checked, you are using persistant connections. Basically a persistant connection does not close immediately but stays open in case it is needed again. The problem here is that with a lot of connections, the mysql server is leaving open a bunch of connections, and opening new ones for other people connecting to the site (and in turn the mysql server). All of the open connections are using up CPU cycles and memory.
Below is a an example setting from the first main area that I checked (the phpPolls section):
PhpPoll Configuration for Main Site:
-------------------------
$poll_usePersistentConnects = 1; // 1 - uses persistent connects to the database
// (always leaves a link open)
I am not sure how many of your pages are using these types of connections, but this could be a big part of your problem.
If for whatever reason you need to have persistant connections rather than the standard mysql connection type, you could adjust some of your mysql server variables to try and minimize both the idle connection time and the conneciton establishment time.
These are the two variables that you might want to adjust:
connect_timeout - this handles the number of seconds the mysql server will wait for the connection to go through
wait_timeout - this is how long the connections will stay open waiting for new connections before closing.
This may or may not help the load on your server. If your main site heavily relies on a mysql backend, then as your traffic increases, so will your server load in general. This is because not only will each page called put a demand on the server from Apache, but it will also put load on the server from calling and using MySQL.
If the variable adjustments do not help, the only other options I can see would be the following:
(1) hardware upgrades (currently you are using a 650mhz processor and have 512 megs of RAM, increasing either or both should help lessen the overall server load).
(2)Getting another server to server only as your database server, private-netting your web server and your database server so that overall server load generated from your site(s) is distributed between the web server and the database server. This option is more than likely your best idea, especially as your traffic increases, you could easily scale in many directions from there (such as load balancing web servers sharing a dedicated database server, etc).
(3)Moving to a stronger database backend such as Oracle. This option seems like it would be your last idea to try as Oracle is very expensive, and would involve a complete overhaul of how your sites operate and interact with the database backend. This option would only be worth seriously looking at once you had tried options 1 & 2 (also including the scaling to load balancing, etc).
I don't see a need to charge for this advice, but if you want us to personally implement the variable changes on your server, that is outside of our standard area of support and would be billable at $75.00 per half hour, estimated time to implement the variable changes would be 1/2 hour. This would be an adjustment to your my.cnf file, which looks like you have already modified it in the past to increase the max connections, buffers, and cache values.
Please let me know if you have any other concerns or require further assistance.
[/quote]
How do I use mysql_connect instead of pconenct? In mysqldb.php, I changed
if ( 0 == $this->link_id ) {
if ($this->password=="") {
$this->link_id=[b]mysql_pconnect[/b]($this->server,$this->user);
} else {
$this->link_id=[b]mysql_pconnect[/b]($this->server,$this->user,$this->password);
}
to
if ( 0 == $this->link_id ) {
if ($this->password=="") {
$this->link_id=[b]mysql_connect[/b]($this->server,$this->user);
} else {
$this->link_id=[b]mysql_connect[/b]($this->server,$this->user,$this->password);
}
but got databse errors. Please help me use vB! by letting me use connect instead of pconenct. Do help.
[quote]
Suggested Answer
---------------------------------------------------------------
At 06/19/2001 01:00 AM we wrote - While I was not able to thoroughly go through all of the pages for your sites, I can tell you that one of your problems causing mysql to use all of your memory is the way your web pages are connecting to the MySQL server. I found that in in one of the first pages I checked, you are using persistant connections. Basically a persistant connection does not close immediately but stays open in case it is needed again. The problem here is that with a lot of connections, the mysql server is leaving open a bunch of connections, and opening new ones for other people connecting to the site (and in turn the mysql server). All of the open connections are using up CPU cycles and memory.
Below is a an example setting from the first main area that I checked (the phpPolls section):
PhpPoll Configuration for Main Site:
-------------------------
$poll_usePersistentConnects = 1; // 1 - uses persistent connects to the database
// (always leaves a link open)
I am not sure how many of your pages are using these types of connections, but this could be a big part of your problem.
If for whatever reason you need to have persistant connections rather than the standard mysql connection type, you could adjust some of your mysql server variables to try and minimize both the idle connection time and the conneciton establishment time.
These are the two variables that you might want to adjust:
connect_timeout - this handles the number of seconds the mysql server will wait for the connection to go through
wait_timeout - this is how long the connections will stay open waiting for new connections before closing.
This may or may not help the load on your server. If your main site heavily relies on a mysql backend, then as your traffic increases, so will your server load in general. This is because not only will each page called put a demand on the server from Apache, but it will also put load on the server from calling and using MySQL.
If the variable adjustments do not help, the only other options I can see would be the following:
(1) hardware upgrades (currently you are using a 650mhz processor and have 512 megs of RAM, increasing either or both should help lessen the overall server load).
(2)Getting another server to server only as your database server, private-netting your web server and your database server so that overall server load generated from your site(s) is distributed between the web server and the database server. This option is more than likely your best idea, especially as your traffic increases, you could easily scale in many directions from there (such as load balancing web servers sharing a dedicated database server, etc).
(3)Moving to a stronger database backend such as Oracle. This option seems like it would be your last idea to try as Oracle is very expensive, and would involve a complete overhaul of how your sites operate and interact with the database backend. This option would only be worth seriously looking at once you had tried options 1 & 2 (also including the scaling to load balancing, etc).
I don't see a need to charge for this advice, but if you want us to personally implement the variable changes on your server, that is outside of our standard area of support and would be billable at $75.00 per half hour, estimated time to implement the variable changes would be 1/2 hour. This would be an adjustment to your my.cnf file, which looks like you have already modified it in the past to increase the max connections, buffers, and cache values.
Please let me know if you have any other concerns or require further assistance.
[/quote]
How do I use mysql_connect instead of pconenct? In mysqldb.php, I changed
if ( 0 == $this->link_id ) {
if ($this->password=="") {
$this->link_id=[b]mysql_pconnect[/b]($this->server,$this->user);
} else {
$this->link_id=[b]mysql_pconnect[/b]($this->server,$this->user,$this->password);
}
to
if ( 0 == $this->link_id ) {
if ($this->password=="") {
$this->link_id=[b]mysql_connect[/b]($this->server,$this->user);
} else {
$this->link_id=[b]mysql_connect[/b]($this->server,$this->user,$this->password);
}
but got databse errors. Please help me use vB! by letting me use connect instead of pconenct. Do help.
Comment