MySQLの挿入/更新を5〜10回高速化

ちょっとした理論。 UNIXオペレーティングシステムには、RAMに物理的に配置されているファイルシステムパーティションがありますが、通常のディスクドライブのように操作できます。 ハードドライブブロックへのアクセス速度は約1 msです。 メモリアクセス速度-0.001ミリ秒。 これをMySQLデータベースに適用して、挿入/更新操作を最大限に活用してみましょう。



まず、ハードドライブへのランダム書き込みの速度を確認します。

time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare 1000 files, 102Kb each, 99Mb total real 0m47.682s
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

共有メモリ（/ run / shmまたは/ dev / shm）でも同じです：

 time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare 1000 files, 102Kb each, 99Mb total real 0m0.083s
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果を比較して、1000ファイルの作成時間が574倍減少したことを確認してください。 いいね そのため、データベースの書き込み速度が向上することを期待する必要があります。



次のことを行います。



1）/ run / shmのサイズと空き容量を確認します

 df -h | grep -P "Filesystem|shm" Filesystem Size Used Avail Use% Mounted on none 16G 782M 15,2G 4.8% /run/shm
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2）データベースが使用するスペースを確認する

 sudo ls -lh /var/lib/mysql/ | grep ibdata1 -rw-rw---- 1 mysql mysql 4.5G  14 05:21 ibdata1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これは、ベースを/ run / shmに転送できることを意味します



3）MySQLを停止します。

 sudo service mysql stop
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

4）ディレクトリを作成し、データをコピーします。

 sudo mkdir /run/shm/mysql-lib sudo chown mysql:mysql /run/shm/mysql-lib sudo cp -rp /var/lib/mysql/* /run/shm/mysql-lib/
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

5）構成を編集します。

 sudo nano /etc/mysql/my.cnf [mysqld] #datadir = /var/lib/mysql datadir = /run/shm/mysql-lib
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

6）AppArmorを修正します。

 sudo nano /etc/apparmor.d/usr.sbin.mysqld #/var/lib/mysql/ r, #/var/lib/mysql/** rwk, /run/shm/mysql-lib/ r, /run/shm/mysql-lib/** rwk,
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

7）MySQLを起動します

 sudo service mysql start
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

*サービスが開始しない場合は、/ var / log / mysql / error.logを見てください



今から楽しい部分です。 何が起こったかを確認してください。



事前にハードドライブでテストを実施したため、すぐに結果を示しました。

  HDD Shared mem Insert 10 000 000  202  66  (bulk = 1000) Update 100 000  122  16  (id = rnd)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

更新は、主キー（id）に対してランダムな間隔[1,000,000-9,000,000]で実行されました。 極端な値は破棄されるため、エンジンはテーブル内で「発掘」します。



INSERTの速度が大幅に向上し、UPDATEの速度がさらに向上します。

挿入する必要が少ない MySQLはインデックス変換とデータ編成を実行します。



MySQL設定で：

innodb_buffer_pool_size = 1024M

設定値を小さくすると、HDDの更新速度は自然に低下します。



innodb_flush_log_at_trx_commit = 2

そのため、ここにはトランザクションがなく、これは速度に影響しません。 ただし、この値は2のままにします。



このようなスキームでは、バイナリログを作成し、定期的にバックアップすることが重要です。 バイナリに別のハードドライブを指定することによってのみ、記録コストを可能な限り削減できます。 ハードドライブへの順次書き込み速度は、ランダムよりもはるかに高速です。 したがって、追加のハードドライブをシステムに入れて、たとえば/ mnt / hddbin /にマウントし、my.iniでバイナリログのパスを指定します。log_bin = /mnt/hddbin/mysql-bin.log



システムを再起動してシャットダウンするスクリプトを追加することを忘れないでください。 フォルダ/ etc / rc *を見てください。 通常、0（システムのシャットダウン）と6（再起動）です。 スクリプトの追加方法に関するマニュアルは、Googleで簡単に見つけることができます。 スクリプトは、システムを再起動またはシャットダウンする前にMySQLを停止し、/ run / shm / mysql-libフォルダーをハードドライブにコピーします。 システムの電源を入れると、スクリプトはデータをハードドライブから/ run / shm / mysql-libフォルダーに復元し、MySQLを起動します。



また、単純なbashまたはperlスクリプトを追加して、/ run / shmの空きメモリを監視します。 Zabbixを接続できます。