Re: MySQL server statistics
On 21.07.2008 22:52 CE(S)T, Benjamin Wiechman wrote: With 5.1 you have more control over general query log and the slow query log - enable or disable at runtime, output to file or DB table. Okay, now that sounds a lot better. Waiting for 5.1 GA then. Although it only provides the very basics. I still have to postprocess/aggregate the log to find who did what and how long that all took. Or - looking at your original question it may be able to narrow down the source of the queries if you can graph your data more often - maybe every 10-15 seconds instead of a longer interval to help you profile which applications are hammering your db server. This is a webhosting services machine. I don't know who of the users is doing what and what applications they have installed. Recently I found one of my applications being vulnerable to senseless recursive bot requests that took quite a bit of the time. That was by chance because I had also looked into the web server requests diagramm, which I can separate by users. And in that case, my colour was clearly visible... ;) So maybe it will already help a lot to see for which user account the database server is working most in a certain time range. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server statistics
On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html As far as I have understood that, there is only one log that can be turned on or off. When turned on, it logs every single query sent to the server. I hope it also logs some other data, like the username and maybe the client address (local/remote). But this has one major drawback: To enable or disable the log (which, I can imagine, takes quite a bit performance) the whole server must be stopped and restarted. This isn't really an option for a production database server, just to know what's going on from time to time. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL server statistics
With 5.1 you have more control over general query log and the slow query log - enable or disable at runtime, output to file or DB table. As of MySQL 5.1.6, the server can write general query and slow query entries to log tables, log files, or both. For details, see Section 5.2.1, Selecting General Query and Slow Query Log Output Destinations. As of MySQL 5.1.12, additional runtime control of the general query and slow query logs is available: You can enable or disable logging, or change the name of the log file. See Section 5.2.3, The General Query Log, and Section 5.2.5, The Slow Query Log. See http://dev.mysql.com/doc/refman/5.1/en/log-files.html If you can either use non-GA or wait until 5.1 is GA. Or - looking at your original question it may be able to narrow down the source of the queries if you can graph your data more often - maybe every 10-15 seconds instead of a longer interval to help you profile which applications are hammering your db server. Ben Wiechman -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Monday, July 21, 2008 11:19 AM To: Rob Wultsch Cc: mysql@lists.mysql.com Subject: Re: MySQL server statistics On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html As far as I have understood that, there is only one log that can be turned on or off. When turned on, it logs every single query sent to the server. I hope it also logs some other data, like the username and maybe the client address (local/remote). But this has one major drawback: To enable or disable the log (which, I can imagine, takes quite a bit performance) the whole server must be stopped and restarted. This isn't really an option for a production database server, just to know what's going on from time to time. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server statistics
On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
