1

I want to profile a running web application in dev on a mysql 5.5 database. The web application uses hibernate and I am able to see that a rather large number of queries are run against the database.

I'm able to see long running queries with logging, but that is not a concern as they are all running fairly quickly.

What I would like to know is, what queries are being run, and how many times they are run.

  • 1
    Daniel, I've just rejected your edit, not because I think it is harmful but because it changes Ahmad's suggestion significantly. It is OK to post an answer to your own question, and even to accept it as the answer if you choose. – Michael Green Dec 16 '14 at 22:27
  • I did the same. Your content seems reasonable, but really should be a separate answer. It's perfectly ok to answer your own question. – Erwin Brandstetter Dec 16 '14 at 23:29
  • I disagree, I simply added documentation for commands he used. His answer is correct, but I wanted more detail. – Daniel Bower Dec 17 '14 at 15:07
0

for the query monitoring you have two solutions from MySQL level:

solution 1:

create log file for the query by editing my.ini or my.cnf

slow_query_log_file = "C:/slowquery.log"
log = log_file_name

solution 2:

Create log tables:

Slow queries:

CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

General queries

CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'


  SET global general_log = 1;
SET global log_output = 'table';

To show the log use the following command:

select * from mysql.general_log;

if you want the slow query:

select * from mysql.slow_log;

To count the number of query executing:

show session status like "Queries";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.