GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Threads are raising and sleeping. They not die!

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

  • Threads are raising and sleeping. They not die!

    Hi

    Just new here.
    Using the Percona Server for some time and all worked, but my SEO was good and the traffic raised.

    With the raising traffic, I have some problems know with the percona server.

    Server is a Athlonx X2 Maschine with 8 GB RAM, RAID1 System.
    OS: Ubuntu Lucid
    SQL Server is Percona Server 5.1 latest version.

    Here is my config:

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

    [mysqld]
    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /var/lib/mysql
    tmpdir = /tmp
    language = /usr/share/mysql/english
    skip-external-locking
    bind-address = 127.0.0.1
    init_connect='SET autocommit=0'
    max_connections = 60
    connect_timeout=10
    key_buffer = 256M
    myisam_sort_buffer_size = 8M
    join_buffer_size = 2M
    read_buffer_size = 8M
    sort_buffer_size = 8M

    table_cache = 4000
    table_definition_cache = 4000

    max_allowed_packet = 32M

    query_cache_size=0M
    query_cache_limit = 4M
    query_cache_type = 0
    tmp_table_size = 256M
    max_heap_table_size = 256M
    open_files_limit = 24576
    thread_cache = 16
    thread_stack=128k
    thread_concurrency=2
    #thread_cache_size=64
    innodb_buffer_pool_size = 2048M
    innodb_additional_mem_pool_size = 32M
    innodb_lock_wait_timeout = 300
    innodb_data_file_path=ibdata1:10M:autoextend
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_thread_concurrency=2
    innodb_flush_method = O_DIRECT
    innodb_write_io_threads=8
    innodb_read_io_threads=8
    innodb_io_capacity=2000
    innodb_max_dirty_pages_pct=90

    innodb_ibuf_active_contract = 1
    innodb_adaptive_flushing = true
    skip-name-resolve

    myisam-recover = BACKUP
    #thread_concurrency = 0
    #long_query_time = 2
    #log-slow-queries = /var/log/mysql/drupal-slow.log
    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
    default-storage-engine = InnoDB
    [mysqldump]
    [mysql]
    [isamchk]
    !includedir /etc/mysql/conf.d/



    And now my problem.
    MySQL Threads are sleeping and they not die.
    From mytop:

    758 root localhost ap24_v3 0 Sleep
    1083 root localhost ap24_v3 1 Sleep
    634 root localhost ap24_v3 2 Sleep
    908 root localhost ap24_v3 4 Sleep
    1085 root localhost ap24_v3 4 Sleep
    1087 root localhost ap24_v3 193 Sleep
    1086 root localhost ap24_v3 194 Sleep
    910 root localhost ap24_v3 493 Sleep
    760 root localhost ap24_v3 786 Sleep
    638 root localhost ap24_v3 1079 Sleep

    This is after 5 minutes of a mysql restart. It raised to 100 threads there are sleeping and Mysql runs out of memory.

    Any Tipp or help would be great.
    thx
    bennos

  • #2
    bennos wrote on Fri, 21 January 2011 10:49
    And now my problem.
    MySQL Threads are sleeping and they not die.
    From mytop:

    758 root localhost ap24_v3 0 Sleep
    1083 root localhost ap24_v3 1 Sleep
    634 root localhost ap24_v3 2 Sleep
    908 root localhost ap24_v3 4 Sleep
    1085 root localhost ap24_v3 4 Sleep
    1087 root localhost ap24_v3 193 Sleep
    1086 root localhost ap24_v3 194 Sleep
    910 root localhost ap24_v3 493 Sleep
    760 root localhost ap24_v3 786 Sleep
    638 root localhost ap24_v3 1079 Sleep

    This is after 5 minutes of a mysql restart. It raised to 100 threads there are sleeping and Mysql runs out of memory.
    I'm guessing that you have some sort of PHP site or something?

    The common problem here is that the old db connections are not closed or reused properly. Instead the application continues to open new ones.

    A common fix for this is to lower the mysql timeout values to something like:

    interactive_timeout = 300wait_timeout = 300

    That way MySQL will instead kill the connections when there hasn't been any activity on it for 300 seconds.

    Then the application will usually just open new connections and not notice anything.

    Comment


    • #3
      do you use mysql_pconnect?

      Comment

      Working...
      X