1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Mysqld creating big i/o

Discussion in 'Site & Server Administration' started by postcd, May 2, 2014.

  1. #1
    Hello, i think mysqld doing big i/o, but myslqd.log , access logs, messages dont show anything

    I reinstalled httpd, mysqld and it contonue happening:
    [root@* script]# tail -n30 /var/log/mysqld.log
    140503 05:38:32  mysqld ended
    
    140503 05:45:41  mysqld started
    140503  5:45:41 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.0.96'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
    Code (markup):
    The linux centos (redhat) server %wa value (from command "top") is usually 30% which is high.

    iotop
    [​IMG]

    Please can anyone help discover what causes it? what commands to do? thx
     
    postcd, May 2, 2014 IP
  2. postcd

    postcd Well-Known Member

    Messages:
    1,037
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    190
    #2
    i already did and its optimised according to mysqltuner and accoring to tunning primer mysql tuning script.
     
    postcd, May 6, 2014 IP
  3. Jason James

    Jason James Peon

    Messages:
    12
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #3
    Eventually it all comes back to IOPS, and what your disk(s) can really handle. What kind of raid setup do you have on this server?
     
    Jason James, May 6, 2014 IP
  4. postcd

    postcd Well-Known Member

    Messages:
    1,037
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    190
    #4
    Disk can be problem. There is no raid and it is 7k RPM. But still most of the time its rarelly 20% used max write / max read value.
     
    postcd, May 7, 2014 IP
  5. Jason James

    Jason James Peon

    Messages:
    12
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #5
    Have you done any profiling to figure out the query/stored proc running at the time of the loads? You either have something poorly written or your disk are just overwhelmed. The other thing you could do is start caching some of the DB calls where possible.
     
    Jason James, May 7, 2014 IP
  6. postcd

    postcd Well-Known Member

    Messages:
    1,037
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    190
    #6
    Thx for ideas, im unsure how to do profilling, if there are any simple commands or tutorial or keyword to search google, i will try. not sure if there is any better tool beside iotop for realising if "your disk are just overwhelmed"
     
    postcd, May 7, 2014 IP
  7. Jason James

    Jason James Peon

    Messages:
    12
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    3
    #7
    www.sysdig.org is probably the best linux profiler out right now, it's pretty amazing.
     
    Jason James, May 7, 2014 IP
    postcd likes this.
  8. jeffatrackaid

    jeffatrackaid Active Member

    Messages:
    168
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    75
    #8
    The short version is your server's disk IO is insufficient to keep up with your application's demands. Typically, there are 3 ways to fix this issue:
    • Get faster disks. RAID 10 Solid State handles high read rates well.
    • Optimize your queries to reduce disk writes.
    • Use caching (ram disks or various database cache techniques)
    One or all of these these options may be needed.

    I am not sure how you ran iotop but I recommend running it with:

    iotop -a

    Which will displace accumulated stats which are better for diagnosing issues.

    In the data you provided, you see a MySQL thread is writing >50MB/sec . This is likely the source of your issues. That is a lot of data being written.

    Web applications are usually read not write intensive. So you will need to identify what is triggering MySQL to write so much data. Poor queries are often the cause.

    Some queries may require a temporary table. If this is large, then a lot of data can be dumped to the disk causing high disk IO on the write side.

    To try to track this down, you will want to enable slow query logging and review your queries.

    See:

    http://stackoverflow.com/questions/6848821/mysql-finding-cause-of-temp-disk-tables

    For more details.
     
    jeffatrackaid, May 7, 2014 IP
    postcd likes this.