Re: High number of MySQL threads when using mod_log_sql
Edward Rudd <[email protected]> Wed, 29 Feb 2012 23:44:06 -0500
| Newsgroups | gmane.comp.apache.mod-log-sql |
|---|---|
| Message-ID | <[email protected]> |
--===============0870658704== Content-Type: multipart/alternative; boundary=Apple-Mail-53-82527918 --Apple-Mail-53-82527918 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii On Feb 29, 2012, at 23:19 , Thomas Goirand wrote: > Hi, >=20 > 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. >=20 > 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 --Apple-Mail-53-82527918 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii <html><head></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; = "><br><div><div>On Feb 29, 2012, at 23:19 , Thomas Goirand = wrote:</div><br class=3D"Apple-interchange-newline"><blockquote = type=3D"cite"><div>Hi,<br><br>On 02/29/2012 05:47 PM, Robert Krig = wrote:<br><blockquote type=3D"cite">I don't know if its gonna cause = problems if MySQL has so many threads<br></blockquote><blockquote = type=3D"cite">open.<br></blockquote><br>It is *not* a problem. Just make = sure that the max_connection directive<br>in your MySQL server is set to = a high number. The default (100<br>connections) really is too low, and = should be increased to something<br>like 500 or 1000. My experience = shows that 500 MySQL connections isn't<br>an issue = anyway.<br></div></blockquote><div><br></div><div>MySQL does handle = large # of connections very well in my experience.</div><div>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.</div><div><br></div><div>There are a few other options if = it is becoming a problem.</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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.</div></div><br><div> <span class=3D"Apple-style-span" style=3D"border-collapse: separate; = color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; = font-variant: normal; font-weight: normal; letter-spacing: normal; = line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: = 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: = 0px; -webkit-border-horizontal-spacing: 0px; = -webkit-border-vertical-spacing: 0px; = -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; font-size: medium; "><span = class=3D"Apple-style-span" style=3D"border-collapse: separate; color: = rgb(0, 0, 0); font-family: Helvetica; font-size: medium; font-style: = normal; font-variant: normal; font-weight: normal; letter-spacing: = normal; line-height: normal; orphans: 2; text-indent: 0px; = text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; = -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: = 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space; "><span class=3D"Apple-style-span" = style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: = Helvetica; font-size: medium; font-style: normal; font-variant: normal; = font-weight: normal; letter-spacing: normal; line-height: normal; = orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; = widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; = -webkit-border-vertical-spacing: 0px; = -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space; "><span class=3D"Apple-style-span" = style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: = Helvetica; font-size: medium; font-style: normal; font-variant: normal; = font-weight: normal; letter-spacing: normal; line-height: normal; = orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; = widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; = -webkit-border-vertical-spacing: 0px; = -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: = auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: = break-word; -webkit-nbsp-mode: space; -webkit-line-break: = after-white-space; "><div>Edward = Rudd</div><div>OutOfOrder.cc</div><div><br></div></div></span><br = class=3D"Apple-interchange-newline"></div></span><br = class=3D"Apple-interchange-newline"></div></span><br = class=3D"Apple-interchange-newline"></span><br = class=3D"Apple-interchange-newline"> </div> <br></body></html>= --Apple-Mail-53-82527918-- --===============0870658704== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ 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-7qY7E20V6GW73k+5HYS8LQqVMODqnSLI@public.gmane.org --===============0870658704==--