How to see slow queries?

[tempus1984]

Hello,

I had installed zabbix, and I wanna display the slow queries in a text file
to replace the simple graph. Is it possible when you have slow queries to see
SQL requests in zabbix interface?? If yes, how can I make it?

Thanks to everybody!

[Calimero]

Enable slow queries logging in mysqld.
Have zabbix_agend collect the content of the log file with a log[] item.

You may have some permissions problems as the slow query log is usually only readable for user mysql and the mysql group. You may have to add zabbix to group mysql (which raises some security concerns) or find another workaround. I don't know how/whether the slow queries log is rotated by mysql. If not a simple chmod 0644 may help (but be aware that you may be me "leaking" information through that file if it's world readable).

[tempus1984]

Yes! I did it!
Thanks to you calimero

I have know a problem, I want to see mysql-slow.log in zabbix,
I have put the item correctly:

- Agent active
- log [/var/...]
- type = log

I have defined the server hostname in zabbix_agent.conf
I have put the firewall rule on zabbix server:

   ${IPTABLES} -A SERVICES -p tcp -d ${@IP} --dport 10051  -j ACCEPT

But it still doesn't work... do you have some idea?

Thanks all

[Calimero]

OK.

"telnet zabbix_server_ip 10051" works from the mysql server ?

On the mysql server, in zabbix_agentd.conf is 'Hostname' set to the host name given in zabbix db ? (case must match)

Are active checks enabled in zabbix_agentd.conf (DisableActive=0) ?

Does user zabbix have read access to the log file (check the perms of all path components) ? Log as zabbix user and try reading the log file.

By the way you should first try with a dummy log file (/tmp/mytest.log with perms 0644) and append lines to the file by hand. It will be easier to troubleshoot.

[tempus1984]

Yes, I just come to find. thanks you!
It was the hostname, but know i had put the same and I have make a step.
But I have another error:

9512:20090518:172746 Cannot open [/var/log/mysql/mysql-slow.log] [Permission denied]
9512:20090518:172746 Active check [log[/var/log/mysql/mysql-slow.log]] is not supported. Disabled.

But I had make a "chmod 777" on the file so I don't understand.

Do you have an idea calimero?
Is it possible that I have a compatibility problem due to the agent version?
I have the 1.4.2...

[Calimero]

9512:20090518:172746 Cannot open [/var/log/mysql/mysql-slow.log] [Permission denied]
9512:20090518:172746 Active check [log[/var/log/mysql/mysql-slow.log]] is not supported. Disabled.

But I had make a "chmod 777" on the file so I don't understand.

Do is it work once the file has 777 permissions ?

As I said zabbix_agentd is probably running as user zabbix. Hence if mysql-slow.log belongs to mysql:mysql and has 0640 permissions (must be something like that), zabbix_agentd won't be able to read it.

You can either make mysql-slow.log world readable as you did (world executable/writable isn't needed !) but you would have to check how the log is rotated to restore the world readable permission.

Another option is to add the mysql group as a supplemental group to user zabbix.

I have to admit I like neither of these two options. Option #1 will give only access to the slow query log but you have to make sure perms are set when the log file is rotated (if it is rotated).

[tempus1984]

hello calimero!

I know it makes time but after other task I come back on slow queries ^^
Today It doesn't works. but I have no errors in zabbix logs.

I have put the correct right.

Thanks you