A module to log accesses to an SQL database.

Robert Krig | 29 Feb 2012 10:47

High number of MySQL threads when using mod_log_sql

Hi there. I'm the admin for a very high volume website. Wie have 4 
webservers which we use to distribute the load of our website. We would 
like to use mod_log_sql to log apache logs to a central MySQL database.

So far, I've just enabled on of our webservers to log to a MySQL server 
running on a different host.

What I find a bit worrying, is the number of MySQL threads which this 
causes.
The one webserver I have logging to MySQL gets about 30-50 hits per 
second. And already this is causing about 150-200 MySQL server threads.

The load on the MySQL server is extremely low.
The host running the MySQL server is a 2x6 core Xeon Cpu with 
Hyperthreading, totalling 24 threads and equipped with 48GB of RAM.

What I find puzzling is that our central MySQL Database Server, which we 
use as a backend for our website, uses only about 12-30 MySQL threads in 
total. Thats for all our webservers.
Granted, this central database server uses SSD Harddrives for the 
database and handles 1500 Queries per second. But still.

Is it normal for mod_log_sql to be using so many MySQL threads? Is there 
a way I could optimise this?
Perhaps a tweak in the mysql server config or a parameter for 
mod_log_sql which I could try?

I don't know if its gonna cause problems if MySQL has so many threads 
open. Especially considering that most of those seem to be "sleeping" 
when monitored with MyTOP.

(Continue reading)

headers

Thomas Goirand | 1 Mar 2012 05:19

Re: High number of MySQL threads when using mod_log_sql

Hi,

FYI, I'm the Debian maintainer for the mod_log_sql package, and I've
been using it for many, many years on all of my servers (I'm tempted to
say a decade, but I'm not sure that's right).

On 02/29/2012 05:47 PM, Robert Krig wrote:
> The one webserver I have logging to MySQL gets about 30-50 hits per 
> second. And already this is causing about 150-200 MySQL server threads.

Frankly, I don't see why this should be worrisome.

On 02/29/2012 05:47 PM, Robert Krig wrote:
> Is it normal for mod_log_sql to be using so many MySQL threads?

Yes, for each apache thread, there's a MySQL connection being made.

On 02/29/2012 05:47 PM, Robert Krig wrote:
> Is there a way I could optimise this?

If by optimize, you mean connecting to MySQL when an apache thread needs
to log something, then immediately after, disconnect, then you'd be
slowing down things. The way things are done right now really *is* the
optimized way.

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

(Continue reading)

headers

Edward Rudd | 1 Mar 2012 05:44

Re: High number of MySQL threads when using mod_log_sql

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

_______________________________________________
Download the latest version at http://www.outoforder.cc/projects/apache/mod_log_sql/

To unsubscribe send an e-mail to 
mod_log_sql-unsubscribe@...

headers

Robert Krig | 1 Mar 2012 10:21

Re: High number of MySQL threads when using mod_log_sql

Thanks for clearing that up for me.
_______________________________________________
Download the latest version at http://www.outoforder.cc/projects/apache/mod_log_sql/

To unsubscribe send an e-mail to 
mod_log_sql-unsubscribe@...