How to profile MySQL

Question

How do I profile a MySQL database. I want to see all the SQL being run against a database.

I know you can do this:

set profiling=1;
Run your slow query (eg SELECT * FROM messages WHERE fromaddress='xxx';
SHOW PROFILES;

But this seem to only apply to stuff run on the command line, I want to see the results from running a website.

Obviously, nothing prevents you from running those queries in a web site. I guess you're asking for a method that doesn't imply modifications in the application code. — Álvaro González, Aug 29 '13 at 10:29
["'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead."](http://dev.mysql.com/doc/refman/5.7/en/show-profiles.html) — bishop, Sep 08 '16 at 16:07

score 7 · Accepted Answer · answered Jun 18 '09 at 14:26

7

You want the query log - but obviously doing this on a heavy production server could be... unwise.

answered Jun 18 '09 at 14:26

Tom Ritter

99,986
30
138
174

score 2 · Answer 2 · edited Sep 22 '13 at 06:23

That worked for me on Ubuntu.

Find and open your MySQL configuration file, usually /etc/mysql/my.cnf on Ubuntu. Look for the section that says “Logging and Replication”

# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.

log = /var/log/mysql/mysql.log

or in newer versions of mysql, comment OUT this lines of codes

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
log_error                = /var/log/mysql/error.log

Just uncomment the log variable to turn on logging. Restart MySQL with this command:

sudo /etc/init.d/mysql restart

Now we’re ready to start monitoring the queries as they come in. Open up a new terminal and run this command to scroll the log file, adjusting the path if necessary.

tail -f /var/log/mysql/mysql.log

score 1 · Answer 3 · edited Sep 22 '13 at 06:22

1

There is a commercial product

http://www.webyog.com/en/

edited Sep 22 '13 at 06:22

Sergey Telshevsky

12,077
6
55
78

answered Jun 18 '09 at 14:18

Haim Evgi

123,187
45
217
223

Bryan Migliorisi · Answer 4 · 2009-06-18T14:25:18.940

0

You can simply parse the query log in real time. If on linux, you can use tail -f to see the log live

Also, you can try some free software from these guys:

http://hackmysql.com/mysqlsla

edited Jun 18 '09 at 14:25

answered Jun 18 '09 at 14:18

Bryan Migliorisi

8,982
4
34
47

score 0 · Answer 5 · answered Jun 18 '09 at 14:29

0

MySqlAdministrator have some useful build in features (including logs view), but for logs it have to be run on same machine as database

answered Jun 18 '09 at 14:29

buddy

725
1
6
9

How to profile MySQL

5 Answers5

Linked

Related