Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Server Really High Load Question

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

  • MySQL Server Really High Load Question

    Hey Guys, How are you?

    I am running a starting-to-get-really-popular community website and, although my server specs are pretty high, starting to have mysql problems. At times when I get around 500 people on the site, mysql process jumps to 170% and the site becomes unresponsive. The Database Engine is InnoDB. I have tried numerous configurations but still the problem remains the same. I think i am simply overlooking something and i hope you can point me in the right direction.

    Server:
    PowerEdge 2950
    2 x Quad Core Intel® Xeon® E5310, 2x4MB Cache, 1.60GHz
    16 Gb RAM

    OS & MySQL:
    Running FreeBSD 6.2 with mysql community server 5.1.22-rc

    Here are some of the important specs/config variables:

    Total buffers: 3.4G global + 58.2M per thread (512 max threads)
    Reads / Writes: 89% / 11%
    InnoDB data size / buffer pool: 1.9G/3.0G

    query_cache_min_res_unit = 1024
    thread_cache = 64
    query_cache_size = 128M
    wait_timeout = 60
    interactive_timeout = 120
    connect_timeout = 10

    max_connections = 512
    max_allowed_packet = 64M
    max_heap_table_size = 256M

    tmp_table_size = 256M
    max_tmp_tables = 256

    thread_cache_size = 64
    thread_concurrency = 16

    innodb_buffer_pool_size = 3G
    innodb_data_file_path = ibdata1:200M:autoextend
    innodb_flush_method=O_DIRECT
    innodb_lock_wait_timeout = 15
    innodb_thread_concurrency = 16

    Anyone can suggest improvements without going master-slave direction?

    PS: There is already a layer of caching implemented.

  • #2
    What do 'iostat' and 'gstat' show for disk usage?

    Comment


    • #3
      during moderate stress (CPU at about 88%)
      everything is at 0

      gstat

      dT: 0.501s w: 0.500s
      L(q) ops/s r/s kBps ms/r w/s kBps ms/w %busy Name
      0 0 0 0 0.0 0 0 0.0 0.0| acd0
      0 0 0 0 0.0 0 0 0.0 0.0| mfid0
      0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1
      0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1a
      0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1b
      0 0 0 0 0.0 0 0 0.0 0.0| mfid0s1c


      iostat
      tty mfid0 cpu
      tin tout KB/t tps MB/s us ni sy in id
      0 85 25.10 10 0.26 0 0 0 0 99

      Comment


      • #4
        i think the issue is with innodb_thread_concurrency = 16

        i changed the value to 0, will see during next stress time how it will perform.

        Comment


        • #5
          I think ,your Os need 64-bit!
          Because in the 32-bit operating system, the largest single process of addressing space for the 4GB

          Comment


          • #6
            we r actually running 64 bit OS

            $ uname -a
            FreeBSD 6.2-RELEASE #0: Fri Jan 12 08:43:30 UTC 2007 root@portnoy.cse.buffalo.edu:/usr/obj/usr/src/sys/SMP amd64

            Comment

            Working...
            X