mysql enable slow query logging/Query Cache Solution - find/track cause of slow mysqld/high IO/CPU usage

If you have a webserver and find you have high IO/lagginess MySQL is one of the first things to check.  It turns out MySQL was my problem and it was creating a high load on my server, especially for IO.

How to Enable MySQL Slow Query Logging To Find Slow Performance/Queries

vi /etc/my.cnf

Add this anywhere under [mysqld]

#slow queries
log-slow-queries = /var/log/mysql/mysqlslowqueries.log
long_query_time = 1

You can change the log path to whatever you like.  You can also chanege the threshold for slow queries, I have it sent on 1 second.  Once you do this and restart mysqld you'll start seeing the user, database and exact query that is slowing things down.

I was finding queries that were taking as long as 42 seconds long!  That was 42 seconds of slow IO performance that was wreaking havoc on my webserver.

The Quick Fix for slow MySQL performance Queries?

I thought about creating a MySQL cluster but it requires several servers and takes some time to setup and test.  I also thought about a hardware upgrade, getting faster RPM HDD's and maybe going SSD, but this takes time and money even if I wanted to do it now.

What can be done immediately?  The answer for me and a lot of people will be MySQL Query Caching.

Enable MySQL Query Caching To Improve MySQL performance

vi /etc/my.cnf

Under [mysqld] add:

#increase performance with caching
#128MB of memory cache in KB
query_cache_size=131072
query_cache_type=1
#maximum size of individual query that can be cached in KB (about 1 gig although note the cache total size is restricted to 128MB now)
query_cache_limit=1048576

After doing this my performance issues were solved.  A quick note about Query Caching is that it only works for the EXACT same SELECT statements when data has not changed, if data changes the cache becomes irrelevant until the next request. Also note that if the SELECT statement expliclity specifies NOCACHE (can't remember the syntax) then the cache will not be used.

For many database driven sites this is still good and at least it can only improve performance.  I've found it to make a huge difference.

 


Tags:

mysql, enable, query, logging, cache, mysqld, io, cpu, usageif, webserver, lagginess, creating, server, queries, vi, etc, cnf, var, mysqlslowqueries, long_query_time, chanege, threshold, restart, ll, user, database, slowing, wreaking, havoc, cluster, requires, servers, hardware, upgrade, rpm, hdd, ssd, caching, improve, mb, kb, query_cache_size, query_cache_type, maximum, individual, cached, restricted, query_cache_limit, select, statements, irrelevant, expliclity, specifies, nocache, syntax, sites, ve,

Latest Articles

  • Huion and Wacom Tablets How To Install in Linux Mint / Ubuntu and make the stylus work properly
  • ffmpeg how to cut certain parts of video out
  • ffmpeg how to concat and join two video clips
  • mencoder instead of ffmpeg to join or concatenate video files with different audio streams
  • Linux How To Stop Missing Drive from Halting Boot Process in fstab
  • How To Replace Audio Track of Video using ffmpeg
  • qemu-img convert formats vdi vmdk raw qcow2
  • Linux and Windows Dual Boot Crazy Time Issues
  • dynagen / dynamips 100% high CPU usage solution - how to set the idlepc value
  • How To Setup a Cisco CME (Cisco Manager Express) Virtual Router under Linux using dynamips and dynagen
  • Linux Mint Ubuntu Debian CentOS Dual Boot Install Issues
  • Linux Mint Ubuntu Debian Centos RHEL no sound solution
  • Linux Mint/Debian/Ubuntu/Centos Installer black grub screen and blank screen after trying to boot installer or main OS
  • Linux Mint Dual Boot Install Avoid Wiping our your Main C: drive /dev/sda MBR and EFI
  • QEMU-KVM soundhw deprecated how to enable sound in QEMU 4.x series
  • Virtualbox Error Cannot register the hard disk because a hard disk with UUID already exists solution
  • kernel: [549267.368859] mate-terminal[7871]: segfault at 2000000101 ip 00007f5d0a9548f0 sp 00007fff7012c610 error 4 in libgobject-2.0.so.0.4800.2[7f5d0a920000+52000]
  • apcupsd how to setup and monitor APC UPS units
  • How To Password Reset, Recover, Bypass, Remove and Unlock on Windows 10,8,7,Vista,XP,NT,2000,2003,2008,2012,2016,2019 Administrative Login Programs
  • Nvidia Ubuntu Linux Screentearing Video with solution driver