Not the answer you need?
Register and ask your own question!

Dropped or unanswered connections

BobPBobP EntrantCurrent User Role Beginner
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 <[email protected]>
>> 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

Comments

  • BobPBobP Entrant Current User Role Beginner
    Hmm, do I have the correct location to ask this question?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.