Bug 1283136 - Query cache efficiency: wrong selects count
Summary: Query cache efficiency: wrong selects count
Keywords:
Status: CLOSED EOL
Alias: None
Product: Fedora
Classification: Fedora
Component: mysqltuner
Version: 26
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Major Hayden
QA Contact: Fedora Extras Quality Assurance
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-11-18 11:10 UTC by Harald Reindl
Modified: 2017-12-12 12:08 UTC (History)
3 users (show)

Fixed In Version: mysqltuner-1.6.0-3.git.a154701.fc23 mysqltuner-1.6.0-3.git.a154701.fc22
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2017-12-12 11:10:26 UTC


Attachments (Terms of Use)

Description Harald Reindl 2015-11-18 11:10:50 UTC
how is it possible that there are 22M selects out of a total of 16M queries

whatever is wrong here is the reason for the way too low 46.9% because we had years ago more than 90% cache hits shown from mysqltuner

[--] Up for: 6d 16h 17m 51s (16M q [28.333 qps], 760K conn, TX: 45B, RX: 4B)
[OK] Query cache efficiency: 46.9% (10M cached / 22M selects)

>>  MySQLTuner 1.6.0 - 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 10.0.21-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV -FEDERATED -InnoDB +MRG_MyISAM 
[--] Data in MyISAM tables: 696M (Tables: 5141)
[--] Data in Aria tables: 22M (Tables: 2)
[!!] Total fragmented tables: 248

Comment 1 Fedora Update System 2016-03-04 16:56:20 UTC
mysqltuner-1.6.0-3.git.a154701.fc23 has been submitted as an update to Fedora 23. https://bodhi.fedoraproject.org/updates/FEDORA-2016-17c03956d1

Comment 2 Fedora Update System 2016-03-04 17:11:38 UTC
mysqltuner-1.6.0-3.git.a154701.fc22 has been submitted as an update to Fedora 22. https://bodhi.fedoraproject.org/updates/FEDORA-2016-3a2ffcfff2

Comment 3 Fedora Update System 2016-03-05 02:19:59 UTC
mysqltuner-1.6.0-3.git.a154701.fc22 has been pushed to the Fedora 22 testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2016-3a2ffcfff2

Comment 4 Fedora Update System 2016-03-05 02:22:14 UTC
mysqltuner-1.6.0-3.git.a154701.fc23 has been pushed to the Fedora 23 testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2016-17c03956d1

Comment 5 Harald Reindl 2016-03-08 11:27:40 UTC
https://bugzilla.redhat.com/show_bug.cgi?id=1267523 is fixed but 2M selects out of 1M queries is still strange :-)

[--] Up for: 15h 3m 39s (1M q [31.158 qps], 73K conn, TX: 5G, RX: 961M)
[OK] Query cache efficiency: 45.9% (967K cached / 2M selects)

Comment 6 Fedora Update System 2016-05-12 20:55:45 UTC
mysqltuner-1.6.0-3.git.a154701.fc23 has been pushed to the Fedora 23 stable repository. If problems still persist, please make note of it in this bug report.

Comment 7 Fedora Update System 2016-05-13 06:21:42 UTC
mysqltuner-1.6.0-3.git.a154701.fc22 has been pushed to the Fedora 22 stable repository. If problems still persist, please make note of it in this bug report.

Comment 8 Harald Reindl 2017-05-15 18:53:58 UTC
frankly this problem still exists

how is it thinkable that with 310K total there where 412K selects?

 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative password: 
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.23-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV -InnoDB +MRG_MyISAM 
[--] Data in MyISAM tables: 573M (Tables: 4819)
[--] Data in Aria tables: 32M (Tables: 2)
[!!] Total fragmented tables: 219

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'rsyslog_insert@%' 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: 6m 29s (310K q [798.964 qps], 100K conn, TX: 813M, RX: 33M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 1.0G global + 2.7M per thread (800 max threads)
[OK] Maximum reached memory usage: 1.1G (9.12% of installed RAM)
[OK] Maximum possible memory usage: 3.1G (26.22% of installed RAM)
[OK] Slow queries: 0% (6/310K)
[OK] Highest usage of available connections: 3% (27/800)
[OK] Aborted connections: 0.00%  (3/100794)
[OK] Query cache efficiency: 49.3% (203K cached / 412K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 3% (7 temp sorts / 197 sorts)
[!!] Joins performed without indexes: 5
[OK] Temporary tables created on disk: 8% (29 on disk / 348 total)
[OK] Thread cache hit rate: 99% (27 created / 100K connections)
[OK] Table cache hit rate: 99% (4K open / 4K opened)
[OK] Open file limit used: 14% (9K/65K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

Comment 9 Harald Reindl 2017-06-19 14:52:34 UTC
that is annyoing, from where do you take the number of "22M selects"

[--] Up for: 6d 16h 17m 51s (16M q [28.333 qps], 760K conn, TX: 45B, RX: 4B)
[OK] Query cache efficiency: 46.9% (10M cached / 22M selects)

even with the total number of all queries (which is correct) 100*10/16 would be 62.5% hits while the 16M contains also update/delete/insert

Comment 10 Harald Reindl 2017-06-19 16:14:39 UTC
from where is "cached" because i will implement a hitrate based on the total numbers of queries in my cms-status-page wich is more accurate than these fantasy number where selects is higher than the total amount of all queries

[--] Up for: 2h 1m 13s (248K q [34.178 qps], 16K conn, TX: 743M, RX: 66M)
[OK] Query cache efficiency: 34.3% (109K cached / 320K selects)

at least the 248k are correct compared with my status-page numbers

Cached queries	31.953
Uptime	02:01:18
Queries	249.067
Queries / Sec.	34,22

Comment 11 Harald Reindl 2017-06-19 20:58:03 UTC
i still have no idea where you take "320K selects" but that's more realistic: round(($mysql_srv_vars['Qcache_hits']*100)/$mysql_srv_vars['Queries'],1)

Uptime: 06:44:29
Queries: 661.363
Queries/Sec: 27,25
Cached queries: 42.411
Cache hits: 328.788 (49.7%)

versus 

[--] Up for: 6h 44m 25s (660K q [27.238 qps], 43K conn, TX: 2G, RX: 185M)
[OK] Query cache efficiency: 37.0% (328K cached / 889K selects)

Comment 12 Fedora End Of Life 2017-07-25 19:31:07 UTC
This message is a reminder that Fedora 24 is nearing its end of life.
Approximately 2 (two) weeks from now Fedora will stop maintaining
and issuing updates for Fedora 24. It is Fedora's policy to close all
bug reports from releases that are no longer maintained. At that time
this bug will be closed as EOL if it remains open with a Fedora  'version'
of '24'.

Package Maintainer: If you wish for this bug to remain open because you
plan to fix it in a currently maintained version, simply change the 'version'
to a later Fedora version.

Thank you for reporting this issue and we are sorry that we were not
able to fix it before Fedora 24 is end of life. If you would still like
to see this bug fixed and are able to reproduce it against a later version
of Fedora, you are encouraged  change the 'version' to a later Fedora
version prior this bug is closed as described in the policy above.

Although we aim to fix as many bugs as possible during every release's
lifetime, sometimes those efforts are overtaken by events. Often a
more recent Fedora release includes newer upstream software that fixes
bugs or makes them obsolete.

Comment 13 Harald Reindl 2017-07-25 19:38:22 UTC
and with the recent version in F25 the user-option is broken because in that case it's no longer asked ofr a password when you have an alias

additionally that should not happen

Use of uninitialized value in string eq at /usr/bin/mysqltuner line 1859,
        <STDIN> line 2 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.
    
    To help you figure out what was undefined, perl will try to tell you
    the name of the variable (if any) that was undefined.  In some cases
    it cannot do this, so it also tells you what operation you used the
    undefined value in.  Note, however, that perl optimizes your program
    and the operation displayed in the warning may not necessarily appear
    literally in your program.  For example, "that $foo" is usually
    optimized into "that " . $foo, and the warning will refer to the
    concatenation (.) operator, even though there is no . in                                                                                                                                                       
    your program.

Comment 14 Fedora End Of Life 2017-11-16 19:11:21 UTC
This message is a reminder that Fedora 25 is nearing its end of life.
Approximately 4 (four) weeks from now Fedora will stop maintaining
and issuing updates for Fedora 25. It is Fedora's policy to close all
bug reports from releases that are no longer maintained. At that time
this bug will be closed as EOL if it remains open with a Fedora  'version'
of '25'.

Package Maintainer: If you wish for this bug to remain open because you
plan to fix it in a currently maintained version, simply change the 'version'
to a later Fedora version.

Thank you for reporting this issue and we are sorry that we were not
able to fix it before Fedora 25 is end of life. If you would still like
to see this bug fixed and are able to reproduce it against a later version
of Fedora, you are encouraged  change the 'version' to a later Fedora
version prior this bug is closed as described in the policy above.

Although we aim to fix as many bugs as possible during every release's
lifetime, sometimes those efforts are overtaken by events. Often a
more recent Fedora release includes newer upstream software that fixes
bugs or makes them obsolete.

Comment 15 Fedora End Of Life 2017-12-12 11:10:26 UTC
Fedora 25 changed to end-of-life (EOL) status on 2017-12-12. Fedora 25 is
no longer maintained, which means that it will not receive any further
security or bug fix updates. As a result we are closing this bug.

If you can reproduce this bug against a currently maintained version of
Fedora please feel free to reopen this bug against that version. If you
are unable to reopen this bug, please file a new report against the
current release. If you experience problems, please add a comment to this
bug.

Thank you for reporting this bug and we are sorry it could not be fixed.


Note You need to log in before you can comment on or make changes to this bug.