Hello,
I’ve been using the Percona 5.1.59 MySQL server for some time. I’ve run into a couple issues that have been bugging me, and I’ve finally got some time to deal with them.
I have several servers running PHP web apps that make calls to this particular server for database queries. From time to time, the various clients will report to me: “No Connection to Host”. I know I have solid connectivity. One of the clients is even on the same switch as the server, and still gets these random messages. Also, often I will see very long query times, even for queries of tables that have only a dozen entries in them.
I have tried replacing the motherboard, replacing the hard drives with SSD drives. Using the SSDs it seemed to get better, but still have long queries. The long queries are a problem, but the “No connection” thing is a lot worse.
I am using MyISAM tables for everything. Last time I tried innodb, it slowed down to a crawl.
Here is my current config:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
Default to using old password format for compatibility with mysql 3.x
clients (those using the mysqlclient10 compatibility package).
old_passwords=1
Disabling symbolic-links is recommended to prevent assorted security risks;
to do so, uncomment this line:
symbolic-links=0
02/16: this was 200:
max_connections=500
max_allowed_packet=7M
key_buffer_size=512M
table_open_cache=512
thread_cache_size=100
skip-name-resolve
skip-external-locking
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M
table_cache = 5000
tmp_table_size = 384M
thread_stack = 192K
query_cache_limit = 10M
join_buffer_size = 1M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
max_connect_errors = 1000000
query_cache_size=0
query_cache_type=0
table_open_cache = 10000
#default-storage-engine = innodb
innodb_file_per_table=1
innodb_buffer_pool_size = 200M
innodb_log_file_size = 100M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M
num cpu’s/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 4
#log=/tmp/mylog
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[myisamchk]
#key_buffer_size = 256M
#sort_buffer_size = 256M
#read_buffer = 2M
#write_buffer = 2M
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M
[mysql]
no-auto-rehash
Here is the output from mysql-tune.pl:
MySQLTuner 1.6.13 - 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] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.1.59-rel13.0-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -----------------------------------------------------------------
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 25G (Tables: 71)
[!!] InnoDB is enabled but isn’t being used
[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
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[–] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[–] Up for: 90d 6h 29m 20s (71M q [9.131 qps], 5M conn, TX: 62G, RX: 11G)
[–] Reads / Writes: 18% / 82%
[–] Binary logging is disabled
[–] Physical Memory : 15.8G
[–] Max MySQL memory : 6.9G
[–] Other process memory: 325.8M
[–] Total buffers: 752.0M global + 12.7M per thread (500 max threads)
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.0G (6.38% of installed RAM)
[OK] Maximum possible memory usage: 6.9G (43.87% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (109/71M)
[OK] Highest usage of available connections: 4% (22/500)
[OK] Aborted connections: 0.50% (25916/5190790)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (40 temp sorts / 22M sorts)
[!!] Joins performed without indexes: 505613
[OK] Temporary tables created on disk: 0% (12K on disk / 1M total)
[!!] Table cache hit rate: 0% (205 open / 138K opened)
[OK] Open file limit used: 2% (277/10K)
[OK] Table locks acquired immediately: 99% (55M immediate / 55M locks)
-------- Performance schema ------------------------------------------------------------------------
[–] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[–] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 16.1% (86M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/515.1M
[OK] Read Key buffer hit rate: 95.6% (2B cached / 114M reads)
[!!] Write Key buffer hit rate: 56.4% (109M cached / 47M writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[–] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is disabled.
-------- 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:
Add skip-innodb to MySQL configuration to disable InnoDB
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
Beware that open_files_limit (10510) variable
should be greater than table_open_cache ( 5000)
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 500.0K, or always use indexes with joins)
table_open_cache (> 5000)
Any suggestions? This is a quad core system running CentOS 5 32-bit, with the PAE kernel.
Bob