How to enable log-slow-queries in mysql?

[futurevision]

Hi,

I'm seeing some high loads on the server with mysql on top with 98% cpu usage and I want to know what the cause is. I suspect some crappy query in a non-optimized table from one of my clients. So, I read about the --log-slow-queries option but I can't figure out how to kill them mysql processes without breaking all websites on the server.

Versions
OS: CentOS 5.5
MySQL: Ver 5.0.67-community for pc-linux-gnu on i686 (MySQL Community Edition (GPL))

What I tried: /usr/sbin/mysqld --log-slow-queries (under root)
and gave this output:

---
120404 20:46:12 [Warning] Asked for 196608 thread stack, but got 126976
120404 20:46:12 [ERROR] Fatal error: Please read "Security" section of the manua l to find out how to run mysqld as root!

120404 20:46:12 [ERROR] Aborting

120404 20:46:12 [Note] /usr/sbin/mysqld: Shutdown complete
---

I think I'm quite close but I just don't understand how I can gracefully restart mysql with the log option without breaking all running sites. Any help is welcome, tnx

 

[zEitEr]

Hello

Put these two lines under section [mysqld]

slow_query_log_file=/var/log/mysql-log-slow-queries.log
long_query_time=3

and restart mysqld

You might want to change log name and it's path.


More information can be found here http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html in official docs.

 

[futurevision]

Thanks but:
- in what file do I put those lines?
- how do I properly restart mysqld?

The docs you point to are for mysql 5.5, I'm running 5.0 so I guess I have to do something different, according to the manual I have to start mysqld with --log-slow-queries but I dont know how.

 

[BestBoard]

for 5.5:

slow_query_log=1 # (1 = on, 0 = off)
slow_query_log_file=slow_query_log.log # (optinal, file name, not full path)
long_query_time=5 # (in seconds)

for 5.0:

log_slow_queries=slow_query_log.log
long_query_time=5 # (in seconds)

put this in your /etc/my.cnf

to restart mysql:
/etc/init.d/mysqld restart

 

[zEitEr]

Oh, I see... it must be CentOS 5.5 version which made me to think you are running MySQL 5.5 ))

Then you should read this http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

 

[futurevision]

Thought so, thanks anyway

BestBoard: I did that and everything seems to be working again, thanks! Now I hope I will see what queries cause the loads from time to time.

 

[BestBoard]

Thought so, thanks anyway

BestBoard: I did that and everything seems to be working again, thanks! Now I hope I will see what queries cause the loads from time to time.

it's also recommended to use log_queries_not_using_indexes and create indexes for these queries for a better performance.
http://dev.mysql.com/doc/refman/5.0...l#option_mysqld_log-queries-not-using-indexes