Collapse

Announcement

Collapse
No announcement yet.

Mysql Optimization

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql Optimization

    How to optimize mysql server ? We have been constantly hit with high mysql cpu usage on the server. Take a look at the following stats.
    top - 18:56:38 up 1:43, 2 users, load average: 62.97, 45.77, 39.71
    Tasks: 642 total, 8 running, 633 sleeping, 0 stopped, 1 zombie
    %Cpu(s): 47.5 us, 25.5 sy, 0.0 ni, 5.9 id, 21.0 wa, 0.0 hi, 0.1 si, 0.0 st
    KiB Mem : 32765428 total, 226884 free, 27133108 used, 5405436 buff/cache
    KiB Swap: 8388604 total, 425508 free, 7963096 used. 5009972 avail Mem

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    1957 mysql 20 0 27.517g 0.017t 5696 S 1490 54.6 1261:38 /usr/sbin/mysqld
    Sometimes the load on the server goes above 100 which suggests something is surely wrong with mysql. Any suggestion ?

  • #2
    You need to monitor SQL queries that are running when the load is high, Particularly if you notice Copying to tmp table on disk state for the queries while running the mysqladmin Process list. Basically what is happening here is MySQL writes temporary tables to disk thus degrading server performance & causing high I/O wait on disk.

    Second thing you need to make sure if the resource usage is high ( check %CPU & %Memory with top command ) It's better to reduce server's resource usage by moving mysql temporary directory to tmpfs ( i.e RAM Disk or Virtual memory).

    Here are the steps for creating RAM disk that you can use, and you have to create one.

    Step 1: Create the tmp directory.

    Code:
     mkdir -p /var/mysqltmp
    Step 2: Set permissions.

    Code:
    chown mysql:mysql /var/mysqltmp
    Step 3: Determine mysql user id

    Code:
     id mysql
    Step 4 : Edit /etc/fstab

    Add the following line, replacing your specific mysql user id and group id.

    Code:
    tmpfs /var/mysqltmp tmpfs rw,gid=989,uid=992,size=4096M,nr_inodes=50k,mode=0700 0 0
    Step 5: Mount the new tmpfs partition.

    Code:
    mount -a
    Step 6: Change your MySQL configuration.

    Code:
    vi /etc/my.cnf
    Step 7: Change, or add the following line.

    Code:
    tmpdir = /var/mysqltmp
    Step 8: Restart MySQL service.

    Code:
    /etc/init.d/mysql restart
    or

    Code:
    service mysql restart
    Using RAM disk you will see a noticeable difference in the servers resource usage. The disk input/output %ratio will also improve significantly. There are other parameters which might need optimizations and for that i will suggest you to follow mysqlmymon.com with mysqlmymonlite.sh bash script.

    Comment


    • #3
      Every database server has dependence on four key resources, which are critical to its functionality.
      -Memory
      -CPU
      -Disk/storage
      -Network
      Their steps in centos 7
      mkdir -p /var/mysqltmp
      chownmysql:mysql /var/mysqltmp
      id mysql
      edit in /etc/fstab
      add the line in /etc/fstab
      tmpfs /var/mysqltmptmpfsrw,gid=989,uid=992,size=4096M,nr_inod es=50k,mode=0700 0 0
      mount -a #for mounting
      vi /etc/my.cnf
      tmpdir = /var/mysqltmp
      systemctl restart mysql

      Comment

      Working...
      X