List:General Discussion« Previous MessageNext Message »
From:nixofortune Date:October 12 2013 10:01am
Subject:Re: Lost connection to MySQL server - need help.
View as plain text  
You might want to comment

bind-address            = 127.0.0.1

in your my.cnf and restart mysql server.



On 12/10/13 10:49, Jørn Dahl-Stamnes wrote:
> 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.
> 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/
>
>

Thread
Lost connection to MySQL server - need help.Jørn Dahl-Stamnes12 Oct
  • Re: Lost connection to MySQL server - need help.nixofortune12 Oct
    • Re: Lost connection to MySQL server - need help.Jørn Dahl-Stamnes12 Oct
      • Re: Lost connection to MySQL server - need help.Andrew Moore12 Oct
        • Re: Lost connection to MySQL server - need help.Jørn Dahl-Stamnes12 Oct
          • Re: Lost connection to MySQL server - need help.Reindl Harald12 Oct
            • Re: Lost connection to MySQL server - need help.Jørn Dahl-Stamnes12 Oct
              • Re: Lost connection to MySQL server - need help.Reindl Harald12 Oct
                • Re: Lost connection to MySQL server - need help.Chris McKeever12 Oct
                  • Re: Lost connection to MySQL server - need help.Reindl Harald12 Oct