You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by John Cameron <jo...@n6.com.au> on 2002/10/18 07:49:09 UTC
Do all apache childs create persistant mysql connections?
Hi. We are using Apache::DBI to connect mod_perl to mysql.
Suddenly (at about 21:00 GMT every day) the number of mysql processes spirals into chaos. From a stable 8 to 20 processes, it jumps up to 70 processes. This uses up all our 510MB RAM, swap-space gets used, and the system grinds to a complete halt. Sometimes, if all load it taken off (users stop their access), it sorts itself out, and mysql processes return to normal. Other times server load hits 50 and I have to restart mysql. This kills all its processes and the system continues happily.
1) We have no idea why this happens at that particular time. There are no crons that run then, and we think have a finite ammount of users accessing the system who do not do anything different at the time the spike happens. The site is public though, so perhaps spiders could be doing this? Any suggestions?
2) Does Apache create a new mysql connection/process for EVERY child apache process that is spawned? I assume some apache processes are spawned to handle simple non-database actions such as retrieving a graphic or static html file. Because we're using Apache::DBI, does this mean that even these little processes are creating a big mysql process?
This is bringing us to our knees. Any help or comments, no matter how obscure, would be greatly appriciated!
Thanks everyone,
John Cameron
Re: Do all apache childs create persistant mysql connections?
Posted by Victor Tsang <vi...@outblaze.com>.
> 1) We have no idea why this happens at that particular time. There are
> no crons that run then, and we think have a finite ammount of users
> accessing the system who do not do anything different at the time the
> spike happens. The site is public though, so perhaps spiders could be
> doing this? Any suggestions?
Try study your access log and see if you find something there.
>
> 2) Does Apache create a new mysql connection/process for EVERY child
> apache process that is spawned? I assume some apache processes are
> spawned to handle simple non-database actions such as retrieving a
> graphic or static html file. Because we're using Apache::DBI, does
> this mean that even these little processes are creating a big mysql
> process?
yes, if you are using Apache::DBI, that's what happen, one presistence
connection for each children. Since connection to mysql is fairly
'cheap', it might be a good idea to disable Apache::DBI and establish a
connection everytime you need them.
Or alternatively, try reducing your max apache children setting, that
should ease your overloading problem with mysql.
Good luck.
Tor.
Re: Do all apache childs create persistant mysql connections?
Posted by Perrin Harkins <pe...@elem.com>.
John Cameron wrote:
>Thankyou! We are using connect_on_init, so this may explain our problem.
>What happens if I turn off connect_on_init? Do I need to change our code in
>any way? Or will the connection be made automatically?
>
The connection will be made when you first do a DBI connect in that
process. When you use connect_on_init, it is made when the child
process spawns.
You would still be advised to move the static files to another server.
- Perrin
Re: Do all apache childs create persistant mysql connections?
Posted by John Cameron <jo...@n6.com.au>.
Perron Harkins Wrote:
> No, if they never use the database they will never create a connection
(unless you use connect_on_init).
Thankyou! We are using connect_on_init, so this may explain our problem.
What happens if I turn off connect_on_init? Do I need to change our code in
any way? Or will the connection be made automatically?
----- Original Message -----
From: "Perrin Harkins" <pe...@elem.com>
To: "John Cameron" <jo...@n6.com.au>
Cc: <mo...@perl.apache.org>
Sent: Friday, October 18, 2002 4:05 PM
Subject: Re: Do all apache childs create persistant mysql connections?
> John Cameron wrote:
>
> > 2) Does Apache create a new mysql connection/process for EVERY child
> > apache process that is spawned?
>
>
> It creates one in each process that uses the database.
>
> > I assume some apache processes are spawned to handle simple
> > non-database actions such as retrieving a graphic or static html file.
> > Because we're using Apache::DBI, does this mean that even these little
> > processes are creating a big mysql process?
>
>
> No, if they never use the database they will never create a connection
> (unless you use connect_on_init). Of course you have no way of keeping
> the static file requests to a single process, so it's likely that each
> process is serving some static files and some dynamic requests that
> require database handles.
>
> > This is bringing us to our knees. Any help or comments, no matter how
> > obscure, would be greatly appriciated!
>
>
> Use a reverse proxy as described in the mod_perl documentation to keep
> static requests from tying up database connections. Use the MaxClients
> setting to liit the total number of apache children, and thus the total
> number of MySQL connections. Make sure you are always using the exact
> same connection info, or you could be opening multiple database
> connections per child.
>
> - Perrin
>
Re: Do all apache childs create persistant mysql connections?
Posted by Perrin Harkins <pe...@elem.com>.
John Cameron wrote:
> 2) Does Apache create a new mysql connection/process for EVERY child
> apache process that is spawned?
It creates one in each process that uses the database.
> I assume some apache processes are spawned to handle simple
> non-database actions such as retrieving a graphic or static html file.
> Because we're using Apache::DBI, does this mean that even these little
> processes are creating a big mysql process?
No, if they never use the database they will never create a connection
(unless you use connect_on_init). Of course you have no way of keeping
the static file requests to a single process, so it's likely that each
process is serving some static files and some dynamic requests that
require database handles.
> This is bringing us to our knees. Any help or comments, no matter how
> obscure, would be greatly appriciated!
Use a reverse proxy as described in the mod_perl documentation to keep
static requests from tying up database connections. Use the MaxClients
setting to liit the total number of apache children, and thus the total
number of MySQL connections. Make sure you are always using the exact
same connection info, or you could be opening multiple database
connections per child.
- Perrin
Re: Do all apache childs create persistant mysql connections?
Posted by Nigel Hamilton <ni...@turbo10.com>.
Hi John,
Maybe consider using MyTop
(http://jeremy.zawodny.com/mysql/mytop/) to view the state of queries when
your server comes under load. It will show what your database is doing to
choke your system.
Has a table become too big to insert into quickly? Move to an
archive. Consider using MySQL heap tables to buffer hits in memory before
writing to disk. You can use a cron job to flush the heap table to disk
periodically.
Do you have another cronjob that runs at 21:00 GMT every day? Is
there a lot of contention for the disk at this time (e.g., webalizer,
locate)? Move the cronjob to a time when your server is quiet.
Just some ideas ... but I hope they help.
NIge
> Hi. We are using Apache::DBI to connect mod_perl to mysql.
>
> Suddenly (at about 21:00 GMT every day) the number of mysql processes spirals into chaos. From a stable 8 to 20 processes, it jumps up to 70 processes. This uses up all our 510MB RAM, swap-space gets used, and the system grinds to a complete halt. Sometimes, if all load it taken off (users stop their access), it sorts itself out, and mysql processes return to normal. Other times server load hits 50 and I have to restart mysql. This kills all its processes and the system continues happily.
>
> 1) We have no idea why this happens at that particular time. There are no crons that run then, and we think have a finite ammount of users accessing the system who do not do anything different at the time the spike happens. The site is public though, so perhaps spiders could be doing this? Any suggestions?
>
> 2) Does Apache create a new mysql connection/process for EVERY child apache process that is spawned? I assume some apache processes are spawned to handle simple non-database actions such as retrieving a graphic or static html file. Because we're using Apache::DBI, does this mean that even these little processes are creating a big mysql process?
>
> This is bringing us to our knees. Any help or comments, no matter how obscure, would be greatly appriciated!
>
> Thanks everyone,
>
> John Cameron
>
--
Nigel Hamilton
Turbo10 Metasearch Engine
email: nigel@turbo10.com
tel: +44 (0) 207 987 5460
fax: +44 (0) 207 987 5468
________________________________________________________________________________
http://turbo10.com Search Deeper. Browse Faster.