GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Max Concurrent Connections

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

  • Max Concurrent Connections

    We have a database Server Configuration:
    4GB RAM
    600GB Hard Disk
    Xeon Processor 1.3 Ghz.

    We are barely able to have 100 concurrent users!!! What are we doing wrong.

    I know I need to configure mysql_query cache, mysql_limit_size and table_cache. But what should be the formula, and how do we go about checking the same.

    Below is the details of our my.ini file.

    [mysqld]
    datadir=/database/data
    socket=/var/lib/mysql/mysql.sock
    set-variable=max_connections=2000
    set-variable = max_allowed_packet=64M
    default-storage-engine = innodb
    log-bin=/database/data/mysql-bin
    #skip-networking
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

  • #2
    Most important are the data buffers.

    I'll assume you're using MyISAM so try this to get the size of cache for MyISAM indexes:

    SHOW VARIABLES LIKE 'key_buffer_size'

    That's the size in bytes.

    Then you can guess the optimum value (making sure it's with limits of your available RAM) by doing:

    SHOW TABLE STATUS WHERE Engine='MyISAM';

    The sum of all the "Index_length" columns would be your optimum key_buffer size (plus a few megs).

    Comment


    • #3
      Ammended configuration:

      While simulating 500 concurrent users on our Drupal installation we made the following observations:


      1) In 500 users test, 415 users passed and 85 users failed. Users failed due to database constraints.
      2) Row locks and table scan occurs throughout the test.
      3) Time consuming queries also occurs throughout the test.
      4) After users completed their actions, Connections are not closed and tables are opened because table cache value is not set properly.
      5) CPU usage on database reaches maximum of 96% and average of 76%. In specific, the maximum of 90% occurs for particular period of time.
      6) CPU usage is normal for web server
      7) The Following were recorded at runtime:

      Slow-queries 2,064
      Buffer-pool-reads 8220
      Row-lock waits 361
      Handler-read-rnd 2134
      Tmp_disk_tables 256
      Opened-tables 896
      Max-connections 250

      My.ini:

      [mysqld]
      key_buffer = 128M
      join_buffer_size = 2M
      read_buffer_size = 1M
      sort_buffer_size = 8M

      table_cache = 2000
      thread_cache_size = 32

      interactive_timeout = 25
      wait_timeout= 3600
      connect_timeout = 4

      max_allowed_packet = 64M
      max_connect_errors = 100

      query_cache_limit = 32M
      query_cache_size = 96M
      query_cache_type = 1

      tmp_table_size = 64M
      max_heap_table_size = 64M
      read_rnd_buffer_size = 524288
      bulk_insert_buffer_size = 8M
      query_prealloc_size = 65536
      query_alloc_block_size = 131072
      open_files_limit = 8196
      key_buffer_size = 64M
      thread_stack = 128K

      set-variable=long_query_time=1
      log-slow-queries = /database/data/log_slow_queries.log

      myisam_sort_buffer_size =32M

      datadir=/database/data
      socket=/var/lib/mysql/mysql.sock
      set-variable=max_connections=300
      set-variable = max_allowed_packet=64M
      default-storage-engine = innodb
      log-bin=/database/data/mysql-bin

      [mysql.server]
      user=mysql
      basedir=/var/lib

      [mysqld_safe]
      log-error=/var/log/mysqld.log
      pid-file=/var/run/mysqld/mysqld.pid

      What should be the configurational changes you recommend?

      Comment


      • #4
        I still have no idea how large your MyISAM indexes are, so I can't make an absolutely accurate value for your settings. But these might help:

        MyISAM key buffer:
        key_buffer = 512M // I'm just guessing your indexes are <= 512 Mb in size

        (You list two key_buffer's, one being an alias in you config, remove one).

        I have no idea what size you tmp tables are when created, nor do I know if they contain syntax that forces them to be written on disk, but to possibly reduce tmp tables being written to disk you could try:

        tmp_table_size = 128M
        max_heap_table_size = 128M

        Comment


        • #5
          Hi shyamala,

          I agree with Speeple, and I am not the SQL expert, but may be I think you will get the right solution on the SQL forum. Just search on the internet "sql forum".

          John Philips

          Foreclosed Homes

          Comment

          Working...
          X