Mysql not logging slow queries

Submitted by amitashok9999 on Thu, 05/01/2014 - 13:20

I have mysql 5.1.73 running on CentOS release 6.4 (Final)

I am trying to enable slow logs but i am unable to do so . my.cnf has it enabled but still it is not working . can some one suggest what is wrong in this

[mysqld] datadir = /mnt/disk1/mysql socket = /var/lib/mysql/mysql.sock user=mysql

symbolic-links=0 innodb_file_per_table = 1 thread_concurrency = 8 query_cache_size = 32M thread_cache_size = 8 myisam_sort_buffer_size = 64M read_rnd_buffer_size = 8M read_buffer_size = 2M sort_buffer_size = 2M table_cache = 512 max_allowed_packet = 1M key_buffer = 384M max_connections = 250

[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log-queries-not-using-indexes = 1 log-slow-queries = 1 slow-query-log-file = /mnt/disk1/mysql/mysql-slow.log

I would also like to know how can i upgrade to the latest mysql version without breaking anything in virtualmin .

Status: 
Closed (fixed)

Comments

Submitted by andreychek on Thu, 05/01/2014 - 13:40

Howdy -- unfortunately, there aren't any supported ways to change the MySQL version. We always recommend using the version provided by your distro. Using versions of MySQL provided by third parties are a frequent source of problems.

Regarding the slow query logging -- try adding those slow-query statements to the "[mysqld]" section of your config, rather than the "[mysqld_safe]" section where they currently are.

After changing that, restart MySQL, which you can do with the following command:

service mysqld restart

Do you see slow query logs after that?

Submitted by amitashok9999 on Sat, 05/03/2014 - 02:50

HI , I made the changes as suggested but still there are no changes to the slow log file. the new my.cnf is as follows.

[root@usloft2559 etc]# cat my.cnf
[mysqld]
datadir = /mnt/disk1/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 32M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 512
max_allowed_packet = 1M
key_buffer = 1024M
max_connections = 350
innodb_buffer_pool_size=4096M
innodb_flush_log_at_trx_commit=0
log-slow-queries                 = 1
slow-query-log-file            = /mnt/disk1/mysql/mysql-slow.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I also checked the owner of the mysql-slow.log , it is set as

-rw-rw---- 1 mysql mysql

but still no logging of slow queries is taking place.

Thanks Amit Ashok

Submitted by andreychek on Sat, 05/03/2014 - 10:08

In place of what you have set there, try setting these parameters in the /etc/my.cnf file:

log-slow-queries = /mnt/disk1/mysql/mysql-slow.log
long_query_time = 1

Once you do that, restart MySQL with this command:

service mysqld restart

If that doesn't work properly -- what output does these commands produce:

ls -ld /mnt
ls -ld /mnt/disk1
ls -ld /mnt/disk1/mysql
ls -l /mnt/disk1/mysql/mysql-slow.log

Submitted by amitashok9999 on Sun, 05/11/2014 - 06:58

I restated the server and it is working now .

I had restated the server after i made the changes but it did not work earlier . i may have missed something . Thanks for the help

Amit

Submitted by andreychek on Sun, 05/11/2014 - 09:37

We're glad to hear it's working properly! I'll go ahead and mark this as fixed. Feel free to let us know if you have any other questions!

Submitted by Issues on Sun, 05/25/2014 - 09:41

Automatically closed -- issue fixed for 2 weeks with no activity.