Help with mysql tuner script results

Hello,

I am over my head and am sure I am missing some very obvious stuff, I am not claiming to know squat about anything here :-)

I was receiving some 502 errors and decided to run some tests to see how the server was running.

I ran the tuner script and below are my results, they should be pretty good since the server has been up for 19 days and there has been a lot of traffic.

If someone could please take a look and provide me with some tips on what changes I should make I would appreciate it.

[!!] User '_db@%' hasn't specific host restriction.
[!!] User 'rlive@%' hasn't specific host restriction.
[!!] User '_dev_usr@%' hasn't specific host restriction.
[!!] User '_usr@%' hasn't specific host restriction.
[!!] User '_usr@%' hasn't specific host restriction.
[!!] User '_wp_usr@%' hasn't specific host restriction.
[!!] User '_r_user_1@%' hasn't specific host restriction.
[!!] User '_r_user_2@%' hasn't specific host restriction.
[!!] User '_f@%' hasn't specific host restriction.
[!!] User '_usr@%' hasn't specific host restriction.
[!!] There is not basic password file list !
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 19d 8h 31m 16s (316M q [189.532 qps], 3M conn, TX: 1276B, RX: 104B)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 191.5M (1.28% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (2.26% of installed RAM)
[OK] Slow queries: 0% (19/316M)
[OK] Highest usage of available connections: 13% (20/151)
[OK] Aborted connections: 1.65%  (59849/3635386)
[!!] Key buffer used: 19.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.5M
[OK] Read Key buffer hit rate: 100.0% (34M cached / 1K reads)
[OK] Write Key buffer hit rate: 97.2% (7M cached / 218K writes)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (29K temp sorts / 73M sorts)
[!!] Joins performed without indexes: 1881793
[!!] Temporary tables created on disk: 36% (15M on disk / 42M total)
[OK] Thread cache hit rate: 99% (5K created / 3M connections)
[!!] Table cache hit rate: 3% (2K open / 64K opened)
[OK] Open file limit used: 4% (226/5K)
[OK] Table locks acquired immediately: 99% (567M immediate / 567M locks)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB  buffer pool / data size: 128.0M/1.8G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.51% (7168 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.98% (44718670910 hits/ 44726526172 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 3963602 writes)

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

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    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 without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 1G) if possible.
    innodb_buffer_pool_instances (=1)

Open in new window

LVL 1
movieprodwAsked:
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.

arnoldCommented:
It would have been best to post the running script you run.

The first part is merely informative/notice dealing with defined users who are not restricted to connect from a specific host because you grant these users @%
The recommendations should be followed.

The warning means when you run the script the password was passed on the command line versus provided when prompted.

The variables can be set effective immediately, using the show variables and then using set global variable_name (ref variable names in the script report)
While at the same time modifying the /etc/my.cnf to make sure the modifications to the variables will be there after the system reboots/service restarts.


echo "show variables;" | MySQL -u username -p | egrep '(query|tmp|pool|size|cache)'

You will be prompted for the password.
The results will be the bulk of the variables and their current settings. They are in bytes.when size/memory is involved.
0
Tomas Helgi JohannssonCommented:
Hi!

The script points out what variables you need to adjust

Variables to adjust:
    query_cache_type (=1)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 1G) if possible.
    innodb_buffer_pool_instances (=1)

Enable the query_cache_type to cache the queries that are executed for faster retrieval of results.
https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html
Innodb_buffer_pool is way to low. Put it around 1 GB as the script suggest.
Also increase the other variables to little over the values the script suggest and you will see some performance increase.

And also I see this
[!!] Joins performed without indexes: 1881793

This means that you have queries with joins on tables that lacks matching indexes on those tables.
Indexes that should have the same columns as the where clause columns in those queries in an optimal order.
You will need to investigate your queries either by looking at the code or capture the queries using slow_query_log with the log_queries_not_using_indexes system variable turned on.

https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

Regards,
    Tomas Helgi
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
movieprodwAuthor Commented:
Thank you for the clarification :-)
0
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.