Need help with mysql optimization

[tictocman]

Hello,

I have been working around until I finally came looking for your help.

Our server has got 3GB dedicated Ram and uses exclusively MyIsam tables.

Here is the my.cnf file:

[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock



[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp

skip-external-locking

bind-address        = 127.0.0.1

max_allowed_packet    = 64M
thread_stack        = 192K
thread_cache_size       = 8

key_buffer = 768M
query_cache_size = 128M
table_cache = 64
query_cache_type = 1
query_cache_limit = 10M
default-storage-engine=MyISAM
wait_timeout = 30
interactive_timeout = 30
tmp_table_size = 32M
max_heap_table_size = 32M

max_connections = 50

sort_buffer_size = 4M
read_rnd_buffer_size = 2M


init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

default_storage_engine=MyISAM
skip-innodb

log-slow-queries=/var/log/mysql/slow-queries.log

[client]
default-character-set = utf8


myisam-recover = BACKUP

expire_logs_days    = 1
max_binlog_size         = 10M



[mysqldump]
quick
quote-names

When we run ./mysqltuner.pl we get this result after more than 24 hours use:

>>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.44-0ubuntu0.12.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 248M (Tables: 369)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 1

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 7m 31s (78M q [803.119 qps], 126K conn, TX: 107B, RX: 30B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 928.0M global + 6.4M per thread (50 max threads)
[OK] Maximum possible memory usage: 1.2G (41% of installed RAM)
[OK] Slow queries: 0% (11/78M)
[OK] Highest usage of available connections: 72% (36/50)
[OK] Key buffer size / total MyISAM indexes: 768.0M/200.1M
[OK] Key buffer hit rate: 99.8% (3B cached / 6M reads)
[OK] Query cache efficiency: 77.3% (60M cached / 77M selects)
[!!] Query cache prunes per day: 14481876
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 5M sorts)
[!!] Joins performed without indexes: 717
[OK] Temporary tables created on disk: 5% (88K on disk / 1M total)
[OK] Thread cache hit rate: 99% (386 created / 126K connections)
[!!] Table cache hit rate: 0% (64 open / 218K opened)
[OK] Open file limit used: 10% (104/1K)
[OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    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_size (> 128M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 64)

Thanks for helping me out.

 

[ModServ]

Hello,

From what I'm seeing, first you need to add table_open_cache, but what's the best value to use?

Note: You need to perform these steps in peak hours to get the best result.

1. Find out total tables of your database, type mysql to login into mysql server, then execute this command

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';

2. Find threads currently connected to your database, execute this command:

show global status like '%Threads_connected%';

3. Calculate the best value for table_open_cache using this formula:

table_open_cache = (total_tables x Threads_connected) / 2

Note: We divided it here as not all the users are accessing all the tables.

To help with the rest of the values, please get the results using MySQL Tuning Primer after 48 hours of MySQL uptime:
https://launchpad.net/mysql-tuning-primer

Waiting the results.

Regards,

 

[tictocman]

Thank you very much,
I put the new variable to the test and restarted the server.

I also run (for testing only ) tuning-primer but it finishes before the end of the script because of this error:

tuning-primer.sh: line 1318: *100/: syntax error: operand expected (error token is "*100/")

Thanks.

 

[cPanelMichael]

tuning-primer.sh: line 1318: *100/: syntax error: operand expected (error token is "*100/")

Hello

A search of this error message indicates it happens when InnoDB is disabled. You do have the skip-innodb option added to your /etc/my.cnf file. Is there any reason you have disabled it on this server?

Thank you.

 

[tictocman]

You were right, I skipped innodb and now runs fine.
I will let it for the whole weekend and let you know.

Thank you.

 

[cPanelMichael]

Sounds good. Thank you for updating us with the outcome.

 

[tictocman]

Hello,

After 4 days up, this is the mysqltuner

>>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.44-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 251M (Tables: 369)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 20h 6m 43s (408M q [810.418 qps], 682K conn, TX: 566B, RX: 161B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 224.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 629.8M (20% of installed RAM)
[OK] Slow queries: 0% (94/408M)
[OK] Highest usage of available connections: 26% (40/151)
[OK] Key buffer size / total MyISAM indexes: 64.0M/202.1M
[OK] Key buffer hit rate: 100.0% (28B cached / 4M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (190 temp sorts / 63M sorts)
[!!] Joins performed without indexes: 5783
[OK] Temporary tables created on disk: 13% (5M on disk / 41M total)
[OK] Thread cache hit rate: 99% (2K created / 682K connections)
[!!] Table cache hit rate: 0% (400 open / 107K opened)
[OK] Open file limit used: 61% (630/1K)
[OK] Table locks acquired immediately: 99% (1B immediate / 1B locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    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_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 400)

 

[cPanelMichael]

[OK] Currently running supported MySQL version 5.5.44-0ubuntu0.12.04.1

To clarify, is this a cPanel server, or just a standard server running MySQL? cPanel is not typically supported on Ubuntu.

Thank you.

 

[tictocman]

It is a standard server

Thanks.

 

[cPanelMichael]

You may find more useful feedback at a website such as WebHostingTalk or Stackoverflow. The forums here are primarily intended for help with cPanel servers, so while you are welcome to seek user-feedback, you likely won't get as many responses as you would on other forums.

Thank you.