MySQL slow.log file growing

We keep having our slow.log file filling up with logs that aren't over the threshold. Am I missing something to either set the threshold higher or are we experiencing a bug? we're on version 5.6 running on centos.

Example log (no where near 1 second or 100 seconds which is how we tried to stop it temporarily)
# Query_time: 0.000316  Lock_time: 0.000050 Rows_sent: 0  Rows_examined: 172

slow_query_log    OFF
slow_query_log_file    MRBURNS-slow.log (our slow.log file isn't even called this)
long_query_time    100.000000

Log file is currently 4.4Gb and incrementing on what looks like every query.
YorkDataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
Do you have another MySQL service (or other database) running?  Does the log show any other information, such as connection status or query?
0
YorkDataAuthor Commented:
We only have MySQL running but with multiple databases. It logs the full query also but no connection status.

Thanks!
0
Steve BinkCommented:
Are the queries something you would expect from your application?  Have you analyzed any of the queries in the log?

What is the value of the log_queries_not_using_indexes variable?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

nociSoftware EngineerCommented:
arent you missing '=' signs between the settings?....

slow_query_log =   OFF
slow_query_log_file =   MRBURNS-slow.log #(our slow.log file isn't even called this)
long_query_time   = 100.000000

Also check if the option /* SQL_NO_CACHE */ has been specified... seems to  log a lot of entries as well.
0
gheistCommented:
for me slow query log is not open all the time, so I think you check with lsof if you can truncate it.
(plus note by noci that you log all queries in reality)
0
YorkDataAuthor Commented:
Sorry, busy few days..

log_queries_not_using_indexes is set to off

The logs do appear to be just every query from the web server when updating and puling info from the SQL DB's. Heres an example

# User@Host: user[user] @  [10.10.10.11]
# Query_time: 0.006652  Lock_time: 0.000031 Rows_sent: 0  Rows_examined: 6523
SET timestamp=1443864887;
UPDATE app0stats_views SET viewcount = viewcount+1 WHERE viewdate >= '2015-10-01' && viewdate <= '2015-10-31' && objectType = 'Page' && objectID = '1';

I've even tried manually setting the long_query_time variables directly to SQL whilst its running so that the values are set really high but it still logs away.

Thanks
0
gheistCommented:
If your my.cnf is without equal signs to set configuration values it is not parsed correctly.
Can you post your my.cnf ?
0
nociSoftware EngineerCommented:
and are the slow log settings under the [mysqld] tag?
1
Steve BinkCommented:
What about your general log?  Is it running, and where does it go?
0
YorkDataAuthor Commented:
Sorry guys, please see our my.cnf attached. You'll see that the slow log settings are under the [mysqld] tag.

The general log is set to OFF at the moment.

Still struggling to find out what it is, currently turning the log regularly as an intermediate solution.

Thanks
my.txt
0
gheistCommented:
Why dont you go with default slow log parameters for start?
0
YorkDataAuthor Commented:
I think when it was originally setup we used a guide for MySQL config. I'll look for a default config and try that with the logs, I didn't think the logging was too much from standard.

I'll do that over the next couple of days and restart MySQL one night.
0
gheistCommented:
Just make it enabled and specify file, and leave rest of threshold settings commented.
0
Steve BinkCommented:
>>> log_queries_not_using_indexes is set to off

From your cnf file:

>>> log_queries_not_using_indexes  = 1

Try turning that off.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.