MySQL optimization notes

I didn't pay attention to MySQL optimization during installation. I 'd like to optimize MySQL first. first, let's take a look at the parameters before optimization: MySQL pre-compiled parameters :. configure -- prefixusrlocalmysql -- with-ssl -- with-readline -- with-big-tables -- enable-assemblertop32553mysql200125m17m4064S0.

I didn't pay attention to MySQL optimization during installation. I 'd like to optimize MySQL first. first, let's take a look at the parameters before optimization: MySQL pre-compiled parameters :. /configure -- prefix =/usr/local/mysql -- with-ssl -- with-readline -- with-big-tables -- enable-encryption er top 32553 mysql 20 0 125 m 17 m 4064 S 0.

I didn't pay attention to MySQL optimization before installation. I first want to optimize MySQL. First, I want to see the parameters before optimization: MySQL pre-compilation parameters:

 ./configure  --prefix=/usr/local/mysql --with-ssl --with-readline --with-big-tables --enable-assembler

Top

32553 mysql     20   0  125m  17m 4064 S  0.0  1.7   5:13.01 mysqld

Use mysqlreport to obtain MySQL running parameters:

MySQL 5.0.40-log         uptime 15 22:1:21      Sat Feb  4 10:04:23 2012__ Key _________________________________________________________________Buffer used    62.00k of  16.00M  %Used:   0.38  Current       1.90M            %Usage:  11.89Write hit      22.29%Read hit       99.83%__ Questions ___________________________________________________________Total          85.58k     0.1/s  DMS          77.61k     0.1/s  %Total:  90.69  Com_          5.37k     0.0/s            6.28  COM_QUIT      2.52k     0.0/s            2.95  +Unknown         78     0.0/s            0.09Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log: OFFDMS            77.61k     0.1/s           90.69  SELECT       72.77k     0.1/s           85.03         93.76  UPDATE        2.68k     0.0/s            3.13          3.45  INSERT        1.09k     0.0/s            1.27          1.41  DELETE        1.07k     0.0/s            1.25          1.38  REPLACE           0       0/s            0.00          0.00Com_            5.37k     0.0/s            6.28  set_option    2.60k     0.0/s            3.04  change_db     2.52k     0.0/s            2.94  show_fields      77     0.0/s            0.09__ SELECT and Sort _____________________________________________________Scan            5.17k     0.0/s %SELECT:   7.10Range           2.75k     0.0/s            3.77Full join           0       0/s            0.00Range check         0       0/s            0.00Full rng join       0       0/s            0.00Sort scan       5.97k     0.0/sSort range      4.30k     0.0/sSort mrg pass       0       0/s__ Table Locks _________________________________________________________Waited             24     0.0/s  %Total:   0.03Immediate      91.00k     0.1/s__ Tables ______________________________________________________________Open               36 of   64    %Cache:  56.25Opened             42     0.0/s__ Connections _________________________________________________________Max used            5 of  100      %Max:   5.00Total           2.52k     0.0/s__ Created Temp ________________________________________________________Disk table      4.15k     0.0/sTable           7.11k     0.0/s    Size:  32.0MFile                5     0.0/s__ Threads _____________________________________________________________Running             1 of    1Cached              0 of    0      %Hit:   0.04Created         2.52k     0.0/sSlow                0       0/s__ Aborted _____________________________________________________________Clients             0       0/sConnects            0       0/s__ Bytes _______________________________________________________________Sent          226.71M   164.8/sReceived       12.59M     9.2/s__ InnoDB Buffer Pool __________________________________________________Usage         304.00k of   8.00M  %Used:   3.71Read hit       84.42%Pages  Free            493            %Total:  96.29  Data             19                      3.71 %Drty:   0.00  Misc              0                      0.00  Latched           0                      0.00Reads              77     0.0/s  From file        12     0.0/s           15.58  Ahead Rnd         1     0.0/s  Ahead Sql         0       0/sWrites              0       0/sFlushes             0       0/sWait Free           0       0/s__ InnoDB Lock _________________________________________________________Waits               0       0/sCurrent             0Time acquiring  Total             0 ms  Average           0 ms  Max               0 ms__ InnoDB Data, Pages, Rows ____________________________________________Data  Reads            25     0.0/s  Writes            3     0.0/s  fsync             3     0.0/s  Pending    Reads           0    Writes          0    fsync           0Pages  Created           0       0/s  Read             19     0.0/s  Written           0       0/sRows  Deleted           0       0/s  Inserted          0       0/s  Read              0       0/s  Updated           0       0/s

First, optimize the pre-compiled parameters.

. /Configure -- prefix =/usr/local/mysql \ -- without-debug \ # cancel debugging mode to improve performance -- with-extra-charsets = utf8, gbk \ # only specify the required default character set to Improve the Performance -- enable-javaser \ # Use the Assembly mode to Improve the Performance -- with-mysqld-ldflags =-all-static \ # increase the compilation speed in static mode performance -- with-client-ldflags =-all-static \ -- with-unix-socket-path =/tmp/mysql. sock \ # Use unix socket to improve performance -- with-ssl

After the installation is complete, further optimize my. cnf: because MySQL only caches indexes (*. MYI), so you only need to add all the MYI files in the database to the total value of key buffer, and calculate the total size of MYI files:

du -hc `find /usr/local/mysql/var/ -name *.MYI`4.0K    /usr/local/mysql/var/myblog/wp_term_taxonomy.MYI8.0K    /usr/local/mysql/var/myblog/wp_posts.MYI8.0K    /usr/local/mysql/var/myblog/wp_usermeta.MYI8.0K    /usr/local/mysql/var/myblog/wp_commentmeta.MYI16K     /usr/local/mysql/var/myblog/wp_options.MYI12K     /usr/local/mysql/var/myblog/wp_postmeta.MYI8.0K    /usr/local/mysql/var/myblog/wp_comments.MYI4.0K    /usr/local/mysql/var/myblog/wp_links.MYI4.0K    /usr/local/mysql/var/myblog/wp_term_relationships.MYI4.0K    /usr/local/mysql/var/myblog/wp_users.MYI8.0K    /usr/local/mysql/var/myblog/wp_terms.MYI16K     /usr/local/mysql/var/mysql/help_relation.MYI4.0K    /usr/local/mysql/var/mysql/time_zone_name.MYI16K     /usr/local/mysql/var/mysql/help_keyword.MYI4.0K    /usr/local/mysql/var/mysql/func.MYI4.0K    /usr/local/mysql/var/mysql/time_zone.MYI20K     /usr/local/mysql/var/mysql/help_topic.MYI4.0K    /usr/local/mysql/var/mysql/columns_priv.MYI4.0K    /usr/local/mysql/var/mysql/procs_priv.MYI4.0K    /usr/local/mysql/var/mysql/time_zone_leap_second.MYI4.0K    /usr/local/mysql/var/mysql/user.MYI4.0K    /usr/local/mysql/var/mysql/tables_priv.MYI4.0K    /usr/local/mysql/var/mysql/host.MYI4.0K    /usr/local/mysql/var/mysql/time_zone_transition_type.MYI4.0K    /usr/local/mysql/var/mysql/proc.MYI4.0K    /usr/local/mysql/var/mysql/help_category.MYI4.0K    /usr/local/mysql/var/mysql/db.MYI4.0K    /usr/local/mysql/var/mysql/time_zone_transition.MYI192K    total

Modify the size of the my. cnf parameter:

Vi/etc/my. cnf # reduce the value of key_buffer = 4 M

Restart MySQL to execute the top command:

18125 mysql     20   0  109m  11m 2152 S  0.0  1.1   0:00.08 mysqld

We can see that the memory usage of MySQL is reduced to 1.1. At this time, it is not suitable to execute mysqlreport to view the results after one day of startup. because the current traffic is low, so the parameter amount is reduced, You need to monitor the MySQL running status in real time appropriate running parameters.

Original article address: MySQL optimization notes, thanks to the original author for sharing.