Solved

setting up the MY.CNF file to allow for Slow Query Logging - permanent after restarting of MYSQL

Posted on 2016-08-23
1
109 Views
Last Modified: 2016-08-29
I have MYSQL database 5.1.73.

I am working on using the MYSQL tuner and have the recommendation to turn on the Slow Query Logging.
I have updated the variable using the command lin but when i reboot I seem to be reseting.
I tried to update the MY.CNF file.

-----------------------------------Contents of MY.CNF-------------------------
[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-slow-queries=1
log-slow-queries=/var/lib/mysql/slow.log
long_query_time=10
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
#long_query_time=1
#log-slow-queries=/var/log/mysql/log-slow-queries.log
#log-queries-not-using-indexes

When I review documents regarding using the Slow Query loggin, I see different variables than the variables listed in the MY.CNF.

result of the Mysql Tuner.


[root@mail ~]# perl mysqltuner.pl
 >>  MySQLTuner 1.6.15 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 522)
[--] Data in InnoDB tables: 891M (Tables: 1332)
[--] Data in MEMORY tables: 0B (Tables: 51)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'Insynch@%' hasn't specific host restriction.
[!!] User 'ROI@%' hasn't specific host restriction.
[!!] User 'bio_admin@%' hasn't specific host restriction.
[!!] User 'bio_dev@%' hasn't specific host restriction.
[!!] User 'bio_marketing@%' hasn't specific host restriction.
[!!] User 'bio_pre@%' hasn't specific host restriction.
[!!] User 'bio_site@%' hasn't specific host restriction.
[!!] User 'bio_synch@%' hasn't specific host restriction.
[!!] User 'bioadmin@%' hasn't specific host restriction.
[!!] User 'biotone_ajg@%' hasn't specific host restriction.
[!!] User 'biotone_magento@%' hasn't specific host restriction.
[!!] User 'bioweb@%' hasn't specific host restriction.
[!!] User 'connector@%' hasn't specific host restriction.
[!!] User 'dev_biotone@%' hasn't specific host restriction.
[!!] User 'livehelp@%' hasn't specific host restriction.
[!!] User 'magento_d@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 25m 5s (169K q [112.940 qps], 1K conn, TX: 474M, RX: 63M)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory     : 7.1G
[--] Max MySQL memory    : 457.2M
[--] Other process memory: 1.4G
[--] Total buffers: 42.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 61.2M (0.84% of installed RAM)
[OK] Maximum possible memory usage: 457.2M (6.29% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/169K)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Aborted connections: 0.17%  (2/1193)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 50.8% (74K cached / 145K selects)
[!!] Query cache prunes per day: 461680
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 606
[!!] Temporary tables created on disk: 59% (7K on disk / 12K total)
[!!] Table cache hit rate: 0% (64 open / 19K opened)
[OK] Open file limit used: 1% (11/1K)
[OK] Table locks acquired immediately: 99% (154K immediate / 156K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.4% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/276.0M
[OK] Read Key buffer hit rate: 99.9% (3M cached / 3K reads)
[OK] Write Key buffer hit rate: 98.6% (523K cached / 7K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/891.4M
[OK] InnoDB Used buffer: 100.00% (512 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 99.59% (16709334 hits/ 16777878 total)
[!!] InnoDB Write Log efficiency: 43.8% (3286 hits/ 7502 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4216 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 64)
Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 64)
    innodb_buffer_pool_size (>= 891M) if possible.


I like to insert these variable into the MY.CNF file but am a bit confused with the variables not matching what is already in the file.  Since noe of these variables are in the MY.CNF, do I just need to add them?
0
Comment
Question by:Scott Johnston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 41768525
Slow query logging are settings to record and retain data for further review. The tunner suggestions are settings to improve performance given the statistical history.. with an active DB the slow querys might (likely) falls into an insignificant statistical data point.
I.e. if you have many queries that fall into the "slow query", the tunner wold provide suggestions to remedy hem through addition of indexes increasing various temp space and resource allocations.

When adjusting , set global variable, will make immediate change to the running mysqlmsame variable with the same parameter should be saved into my.cnf..
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question