[Mod_log_sql] High number of MySQL threads when using mod_log_sql

[Edward Rudd]

On Feb 29, 2012, at 23:19 , Thomas Goirand wrote:

> Hi,
> 
> On 02/29/2012 05:47 PM, Robert Krig wrote:
>> I don't know if its gonna cause problems if MySQL has so many threads
>> open.
> 
> It is *not* a problem. Just make sure that the max_connection directive
> in your MySQL server is set to a high number. The default (100
> connections) really is too low, and should be increased to something
> like 500 or 1000. My experience shows that 500 MySQL connections isn't
> an issue anyway.

MySQL does handle large # of connections very well in my experience.
Basically the architecture of mod_log_sql is to open and maintain one connection per apache preforked process.  So, if you have a lot of traffic going on *now* you'll have a lot of preforked apache processes, thus a large # of mysql connections.   If you have apache configured to have a large number of idle processes waiting for new traffic, then yes you'll encounter a large number of idle mysql connections.

There are a few other options if it is becoming a problem.

Use the mod_dbd support driver and a threaded apache MPM.  Assuming your application can run in a threaded apache MPM (Worker or event) you can make use of the mod_dbd connection pooling and reduce your connections.  This in *theory* should be thread safe due to mod_dbd's architecture. It's been a while since I write it, but I was pretty certain that I added mod_dbd support for a client who wanted to run in threaded mode and use connection pooling.

Another option is to use the force preserve option, and import the preserve files at a regular interval.  You do lose some "real-timeness" of the data, but it will reduce connection load to the server.

Like the preserve option, you can use the "cli utility" to import raw apache access logs in. (only available in the latest git master, not in a released version).  This was another client request.  They moved to an infrastructure that didn't allow for direct db connections (Amazon Cloud), so they wanted a CLI script that would parse the apache logs into the mod_log_sql db format.   The latest git code also includes a more powerful configuration syntax to allow better customizing of the db format.

Edward Rudd
OutOfOrder.cc






-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.outoforder.cc/pipermail/mod_log_sql/attachments/20120229/f4b185a0/attachment.html