Enable the slow query log

[Massoud]

How can
Enable the slow query log to troubleshoot bad queries?

 

[Massoud]

Re: Extreme disk writes and server IO load from mysqld.

After running the script I got this result:

>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-community
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 5M (Tables: 217)
[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18m 15s (6K q [6.058 qps], 260 conn, TX: 19M, RX: 1M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 314.0M global + 2.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.7G (66% of installed RAM)
[OK] Slow queries: 0% (0/6K)
[OK] Highest usage of available connections: 3% (16/500)
[OK] Key buffer size / total MyISAM indexes: 256.0M/2.3M
[OK] Key buffer hit rate: 99.8% (121K cached / 236 reads)
[OK] Query cache efficiency: 74.1% (3K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 264 sorts)
[!!] Joins performed without indexes: 20
[OK] Temporary tables created on disk: 17% (19 on disk / 107 total)
[OK] Thread cache hit rate: 93% (16 created / 260 connections)
[OK] Table cache hit rate: 97% (244 open / 250 opened)
[OK] Open file limit used: 5% (478/8K)
[OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 132.0K, or always use indexes with joins)


I change joing_buffer_size to 256K but simlar error has been generated
in addition I do not know how should I Enable the slow query log to troubleshoot bad queries
and
my current: my.conf configuration files is this:

[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=2M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
table_cache = 4096
join_buffer_size =132K
key_buffer=256M ## 128MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout


Database Collation is UTF-General-Ci
Please help me to modify setting to best performance my VPS configuration is:
Dual Core 2GHZ Processor
4GB RAM

 

[Infopro]

How can
Enable the slow query log to troubleshoot bad queries?

I've moved your posts to it's own thread.

 

[cPanelTristan]

Because you are running MySQL 5.0.91 per the mysqltuner.pl report you've provided, you would add the following to /etc/my.cnf file to enable the slow query log:

log-slow-queries=/var/lib/mysql/slow.log

After that, then do the following commands to create the file with the right ownership and file permissions:

touch /var/lib/mysql/slow.log
chmod 660 /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log

 

[Massoud]

Thank you for your reply.
How can I find this problem: "Joins performed without indexes" and how should I solve this?

in addition I did what you said but the slow-log file is always empty.

Now my result is something like that:
>> MySQLTuner 1.1.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 12M (Tables: 357)
[--] Data in InnoDB tables: 16K (Tables: 1)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 47m 14s (516K q [13.288 qps], 18K conn, TX: 1B, RX: 101M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 258.0M global + 9.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.2G (46% of installed RAM)
[OK] Slow queries: 0% (3/516K)
[OK] Highest usage of available connections: 27% (27/100)
[OK] Key buffer size / total MyISAM indexes: 200.0M/3.2M
[OK] Key buffer hit rate: 100.0% (13M cached / 1K reads)
[OK] Query cache efficiency: 72.4% (279K cached / 385K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 18K sorts)
[!!] Joins performed without indexes: 401
[OK] Temporary tables created on disk: 19% (690 on disk / 3K total)
[OK] Thread cache hit rate: 99% (27 created / 18K connections)
[OK] Table cache hit rate: 98% (419 open / 425 opened)
[OK] Open file limit used: 70% (794/1K)
[OK] Table locks acquired immediately: 98% (153K immediate / 155K locks)
[OK] InnoDB data size / buffer pool: 16.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 4.0M, or always use indexes with joins)

 

[cPanelTristan]

Since mysqltuner.pl continues to indicate that the slow query log doesn't exist, it would appear it wasn't properly enabled. Please provide your /etc/my.cnf file contents and run the following commands for us to see the output:

mysql_config --version
ls -lah /var/lib/mysql/*.log*

Also, I really don't suggest running mysqltuner.pl in less than 24 hour increments.

For the join question, you can simply increase the join_buffer_size to provide for not having indexes for the join queries. You'd have to go through the databases you have otherwise to find which ones have joins that aren't indexed.

Thanks.

 

[postcd]

To start loging slow queries you might need to restart mysql? service mysql restart

 

[cPanelTristan]

I'm uncertain how MySQL might not have been restarted when it was up for less than 24 hours on the mysqltuner.pl run per the results provided:

MySQL started within last 24 hours - recommendations may be inaccurate

It is true that they would need to restart MySQL, but I think some other inherent issue might be happening here instead given that it had been restarted within a less than 24 hour timeframe at the time of the prior post.

 

[pixeline]

log-slow-queries=/var/lib/mysql/slow.log

I think you mean:

log_slow_queries=/var/lib/mysql/slow.log

, not

log-slow-queries=/var/lib/mysql/slow.log

Right?

 

[cPanelTristan]

No, I do not:

MySQL :: MySQL 5.0 Reference Manual :: 5.1.2 Server Command Options

You can use log-slow-queries in /etc/my.cnf file. Both are acceptable variable names for that file. Please feel free to review the documentation. Here are a series of steps on my machine to show this works perfectly fine:

[email protected] [~]# mysql_config --version
5.0.92
[email protected] [~]# mysqladmin var | grep slow
| log_slow_queries                        | OFF        |
| slow_launch_time                        | 2          |
[email protected] [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1

[email protected] [~]# echo "log-slow-queries=/var/lib/mysql/slow.log" >> /etc/my.cnf
[email protected] [~]# touch /var/lib/mysql/slow.log
[email protected] [~]# chown mysql:mysql /var/lib/mysql/slow.log
[email protected] [~]# chmod 660 /var/lib/mysql/slow.log
[email protected] [~]# /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 

[email protected] [~]# mysqladmin var | grep slow
| log_slow_queries                        | ON     |
| slow_launch_time                        | 2      |
[email protected] [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1
log-slow-queries=/var/lib/mysql/slow.log
[email protected] [~]# cat /var/lib/mysql/slow.log 
/usr/sbin/mysqld, Version: 5.0.92-community-log 
(MySQL Community Edition (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

Also, there is a difference between empty and no slow queries logging. My /var/lib/mysql/slow.log for the slow query log isn't empty. It shows the restart command being issued but nothing logging as I just restarted to create it. Not to mention, I have no queries on my machine that take 10 or more seconds to run.

Thanks!