Hello,

I got a strange problem related to a production server. It has been working OK
for months, but yesterday it start to fail. There are several batch scripts
using the database in addition to a web application using it.

The php scripts running in batch mode began to get:

mysql_connect(): Lost connection to MySQL server at 'reading initial
communication packet', system error: 111

I stopped the server and restarted it and everything seems to work OK for
hours but when the load start to increase, the errors begin to appear again.

Today I noticed that after I starte phpMyAdmin and selected one of the
databases, phpMyAdmin was hanging and the batch scripts began to fail again=2E
Seems like the server does not handle much load anymore.


What's strange is the memory usage. The server is a quad core cpu with 48 Gb
memory, where 28 Gb is allocated to innodb (we mostly use innodb). But when
using top command, I noticed this:

VIRT: 33.9g
RES: 9.4g
SWAP: 23g

at this time over 11G memory is free. vm.swappiness is set to 0. I find it
strange that the server is not able to use physical memory but use swap
instead. The amount of cpu time used for swapping is rather high during sql
queries. The amount of RESident memory may increase slowly over time but very
slowly (it can take hours before it increase to 20+ Gb).

[PS: I also got a MySQL server running at a dedicated host at home, where the
it seem to use the memory as I except it to use:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP DATA COMMAND
1462 mysql 20 0 30.0g 27g 3900 S 0.3 87.3 2633:14 844m 29g mysqld
]


I would like to have some suggestions what I can do to solve this problem.
I have google'd it but found nothing that seem to solve my case.

Server:
OS: Debian 6
MySQL: 5.1.61-0+squeeze1

my.cnf:
#
# The MySQL database server configuration file.
#

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

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld].
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /database/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
## All applications use 127.0.0.1 when connectiong to the db.

#
# * Fine Tuning
#
#key_buffer = 16M
max_allowed_packet = 64M
thread_stack = 192K
#thread_cache_size = 8

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#
# * Query Cache Configuration
#
query_cache_limit = 1M

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

thread_cache_size = 192
table_cache = 768
## key_buffer = 64M
## sort_buffer_size = 256K
## read_buffer_size = 256K
## read_rnd_buffer_size = 256K
tmp_table_size=32M
max_heap_table_size=32M
query_cache_size=128M
query_cache_type=2

innodb_open_files=1000
innodb_buffer_pool_size = 28G
innodb_additional_mem_pool_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 0
innodb_lock_wait_timeout = 50
## innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
## innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 14
innodb_file_per_table

max_connections = 100
binlog_cache_size = 1M
sort_buffer_size = 16M
join_buffer_size = 16M
ft_min_word_len = 1
ft_max_word_len = 84
ft_stopword_file = ''
default_table_type = InnoDB
key_buffer = 2G
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


=2D-
J=F8rn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql