Announcement

Announcement Module
Collapse
No announcement yet.

mysql connection issue

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql connection issue

    i am getting this error on new centos 6.0 OS with Server version: 5.1.42-community-log MySQL Community Server (GPL).

    Memory is available on system,


    MEMORY USAGE
    Max Memory Ever Allocated : 110.10 G
    Configured Max Per-thread Buffers : 21.68 G
    Configured Max Global Buffers : 90.07 G
    Configured Max Memory Limit : 111.75 G
    Physical Memory : 126.03 G
    Max memory limit seem to be within acceptable norms

    mysql not letting application more than 1016 connections.

    The same configuration was working for years on centos 5.

    OS limit settings are:

    core file size (blocks, -c) 0
    data seg size (kbytes, -d) unlimited
    scheduling priority (-e) 0
    file size (blocks, -f) unlimited
    pending signals (-i) 1032346
    max locked memory (kbytes, -l) 64
    max memory size (kbytes, -m) unlimited
    open files (-n) 65535
    pipe size (512 bytes, -p) 8
    POSIX message queues (bytes, -q) 819200
    real-time priority (-r) 0
    stack size (kbytes, -s) 10240
    cpu time (seconds, -t) unlimited
    max user processes (-u) 10240
    virtual memory (kbytes, -v) unlimited
    file locks (-x) unlimited

  • #2
    You didn't say anything about what the actual error is.

    Comment


    • #3
      the actual error on client is:

      (Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug)

      this is happening on new centos 6.0 os i migrated from centos 5.4

      Comment


      • #4
        http://forum.percona.com/index.php?t=msg&goto=5540

        Comment


        • #5
          i checked that posting and already verified setting suggested.

          my system have enough free memory for mysql.

          MEMORY USAGE
          Configured Max Per-thread Buffers : 21.68 G
          Configured Max Global Buffers : 90.07 G
          Configured Max Memory Limit : 111.75 G
          Physical Memory : 126.03 G

          as i told earlier same configuration working fine on centos 5 without any issue not on new server on centos 6.

          is there any process/thread/fork architecture difference between centos5/6?

          why mysql not letting me create more connections than exactly 1016. even after tweaking thread/buffer values?

          Comment


          • #6
            Where are you getting that "memory usage" stuff from? mytuner.pl? It's a joke. What it's trying to do can't be done.

            See http://www.mysqlperformanceblog.com/2009/02/12/how-much-memo ry-can-mysql-use-in-the-worst-case/

            Comment


            • #7
              this is my.cnf

              back_log = 50
              max_connections = 1200
              max_connect_errors = 20
              max_length_for_sort_data=8192
              max_sort_length=8192
              table_cache = 2048
              max_allowed_packet = 64M
              binlog_cache_size = 8M
              max_heap_table_size = 64M
              sort_buffer_size = 4M
              join_buffer_size = 2M
              thread_cache_size = 100
              thread_concurrency = 8
              query_cache_size = 32M
              query_cache_limit = 2M
              ft_min_word_len = 4
              default_table_type = INNODB
              thread_stack = 192K
              transaction_isolation = REPEATABLE-READ
              tmp_table_size = 64M
              log_warnings = 2
              binlog_format = ROW
              log-error = error.log
              log_long_format
              log-queries-not-using-indexes
              table_definition_cache = 512
              log_slave_updates
              key_buffer_size = 8M
              read_buffer_size = 2M
              read_rnd_buffer_size = 4M
              bulk_insert_buffer_size = 8M
              myisam_sort_buffer_size = 2M
              myisam_repair_threads = 1
              myisam_recover
              skip-federated
              skip-name-resolve
              #skip-bdb
              innodb_additional_mem_pool_size = 16M
              innodb_buffer_pool_size = 90G
              innodb_data_file_path = ibdata1:10M:autoextend
              innodb_data_home_dir = /var/lib/mysql
              innodb_file_io_threads = 4
              innodb_thread_concurrency = 32
              innodb_flush_log_at_trx_commit = 0
              innodb_log_buffer_size = 16M
              innodb_log_file_size = 1256M
              innodb_log_files_in_group = 2
              innodb_log_group_home_dir = /var/lib/mysql
              innodb_max_dirty_pages_pct = 90
              innodb_lock_wait_timeout = 120
              innodb_file_per_table = 1
              [mysql]
              no-auto-rehash
              [mysqld_safe]
              open-files-limit = 8192

              System is Linux Kernel 2.6.32-71.el6.x86_64 with 126 GB RAM
              Intel(R) Xeon(R) CPU X5670 @ 2.93GHz

              I reduced primary buffers memory and according to it if i calculate using standard formula:
              key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 8 + (2 + 4 ) x 1100 = 6600 + 8 = 6608 MB = 6 GB

              innodb_buffer_pool_size = 90G

              approx memory of system should be 90 + 6 = 96 GB, however i Have 126 GB of memory on system. and mysql is not able to create not more than 1016 connections.

              as i told in my earlier post on centos 5 mysql with same configuration is working.

              i have no option other than using centos 5 now.

              Comment


              • #8
                The "standard formula" is nonsense. I think it's highly likely that you are actually using much more than 96GB. InnoDB itself will add at least 5% of overhead to the buffer pool alone, for example. I would suggest watching the server under real usage and see how much memory it's using. I'd bet you are around 105-110GB, and then you're probably hitting a peak of queries that do sorts or something and running out of memory. The error message you're getting is real -- the question is why the new version is using more memory than the old one.

                Take a look at the sample chapter for High Performance MySQL 3rd edition, here: http://www.highperfmysql.com/

                Comment


                • #9
                  finally it get resolved by rebooting the system, it is system limit issue.

                  The conclusion is :

                  if we increase process and file open limit in /etc/security/limits.conf

                  mysql soft nofile 10240
                  mysql hard nofile 40960
                  mysql soft nproc 10240
                  mysql hard nproc 40960

                  mysql not taking these limits even after we restart mysql so we reboot the system and it works but this is not the case in centos 5.x

                  now we decrease these limits and restart mysql, this time mysql takes these descrased limits.

                  You need to reboot the system to commmit increased limits thats what i analyse finally.

                  Comment


                  • #10
                    xaprb wrote on Thu, 02 February 2012 17:44
                    The "standard formula" is nonsense. I think it's highly likely that you are actually using much more than 96GB. InnoDB itself will add at least 5% of overhead to the buffer pool alone, for example. I would suggest watching the server under real usage and see how much memory it's using. I'd bet you are around 105-110GB, and then you're probably hitting a peak of queries that do sorts or something and running out of memory. The error message you're getting is real -- the question is why the new version is using more memory than the old one.

                    Take a look at the sample chapter for High Performance MySQL 3rd edition, here: http://www.highperfmysql.com/
                    Baron, this case is really a problem of Centos 6: it uses default limit of 1024 maximum processes per user. Centos 5.5 uses by default 1581056. We also hit this limit shortly after upgrading the OS.

                    Comment

                    Working...
                    X