Need Help Getting Slow Query Log Working On Centos Setup.

[marcm]

Hi Everyone. I finally got around to installing my license of MONyog on my Centos server.

Right now I'm only able to analyze using the Sniffer as I have no slow query log and in fact I'm having trouble finding the proper MySQL folders on the box. The CPU % that MySQL is taking up has been going up and is causing some high load issues on my server. MySQL is taking up on Average about 20% of the CPU time.

Anyway, I'm first trying to sort out how to enable the slow query log. I'd like to just add the proper lines to my.cnf and restart MySQL. However, I'm not sure if that's the right way to do it. I do have a my.cnf in /etc although it's relatively empty and doesn't have any lines commented out or anything. I wish I could find a full my.ini or something like I see on my windows box, but I'm not having luck with that.

Once I do have the slow query log running, I'm not exactly sure where to put it, but I guess I can dump it into

/var/lib/mysql – I was able to find an .err file there but it is called host.mydomain.com.err

I'm a bit green here trying to sort this out, and I'd really like to use MONyog to the fullest extent to try and sort out why MySQL is having such a high load on the server.

Finally, does anyone know if using NOW() in an UPDATE query when recording if someone is online or not is a problem? I've read that using NOW() might not be a good idea, and I'm wondering if anyone else knows about this.

One of the sniffed queries that has a high occurence and high max execution time and a high total value is a very simple query that looks like this:

UPDATE members SET mem_last_activity = now(), last_page = ?, last_sub_page = ? WHERE mem_id = ?

The table has about 12k rows. It's indexed and has a unique primary key for mem_id.

Thanks for any help here, it's very much appreciated. I know I can get this slow query log working I just need a little more info.

[marcm]

post removed. I attempted to stop the service but it failed somehow. I used the command: # /etc/init.d/MONyogd stop and it stopped it and I was able to restart it.

[Sayan Chaliha]

Hey Marc,

Depending on your MySQL server version, you can enable your slow query log from within MONyog's interface (check attached screen-shot; this feature requires MySQL versions 5.1.6 or above).

If you're using an older version of the server, you could add the following to your my.cnf/my.ini:

Code:

slow_query_log=ON
slow_query_log_file= log_slow_queries=ON
long_query_time=
log_queries_not_using_indexes=

As for guidelines on where to store the slow query log file, you should check MySQL's documentation; specifically: http://dev.mysql.com…log-tables.html

Using NOW() is okay. However, you should know that NOW() returns the time when the query began execution, and not the current system time. To get the current system time use SYSDATE() instead.

PS: The screen-shot attachment failed… I'll try and upload it again.

[Sayan Chaliha]

[attachment=1404:slowlog.JPG]

[marcm]

Hi Sayan,

Thank you. I was able to manually add it into my my.cnf file and I added a path and then just manually inserted the path into MONyog and it worked. 🙂

So far I am loving MONyog and I only wish I would have installed it sooner 😉

Once I added in the SSH settings and got the monitors and advisors working I was really pleased!

Thanks again.

I'm sure I'll be back with more questions.

[Sayan Chaliha]

Hey Marc,

Glad that you're liking MONyog! You're always welcome here with any questions you may have. You can also create a support ticket here if you think you've run into problems with MONyog, and rest assured, we'll try our best and address those issues within 24 working hours!

Also please feel free to give us any feedback you may have while using MONyog (you can mail them to [email protected]).

[Author]

Posts