Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
zabbix recreate DB Engine

  • Data-Articles
    • Labuanbajo2 (67) Sat09,12:10pm

      Hari pertamaSailingKomodo, kapal kami menuju ke pulau Kanawa. Bukan Kenawa yang di Sumba itu, ya. Karena biasanya sering tertukar. Pulau Kanawa sendiri sebuah pulau kecil yang memiliki dermaga dan sebuah resort. Kalian bisa menginap di resort ini dengan fasilitas antar jemput dari pelabuhan Labuan Bajo-Kanawa. Bisa dilihat paketnya di situs-situs penjualan reservasi hotel. Kami akan mampir untuksn

      Read More...

zabbix recreate DB Engine

Enter the new villain of our story: working with a 90GB ibdata1 (myISAM) file for conversion to individual innodb files.

The process was (specific for Zabbix; you can draw from it to generalize it):

  1. Get some extra storage (You窶决e going to need it). ツI mapped a network share temporarily.
  2. Dump the database into an SQL file:
    1. mysqldump -u [your db user] -p zabbix 窶殿llow-keywords 窶兎vents > [path to backup location]/zabbix.sql

    2. Note: this file was MUCH smaller than the innodb1 file (35GB vs 95GB).
  3. Stop MySQL
    1. sudo service mysql stop

  4. Copy the existing innodb files to a backup location (just in case).
    1. sudo chown [youruser]:[yourgroup] /var/lib/mysql -R

    2. This is so you can easily read/write to that folder as you. ツWe窶冤l change it back to mysql:mysql when we窶决e done. ツDon窶冲 leave it like this!
    3. cp /var/lib/mysql [path to backup location] -R
      sudo chown mysql:mysql /var/lib/mysql -R

  5. Start MySQL
    1. sudo service mysql start

  6. Drop the existing database
    1. mysql -u [your db user] -p
      drop database zabbix;

    2. Note: As expected: ibdata1 is still 90GB+ for me!ツ

  7. Stop MySQL as in Step 3.
  8. Make changes to your MySQL my.cnf
    1. nano /etc/mysql/my.cnf

    2. Add the following lines to the [mysqld] section:

      innodb_file_per_table
      innodb_flush_method=O_DIRECT
      innodb_log_file_size=1G
      innodb_buffer_pool_size=4G

    3. Please note: buffer pool should be ~4x log file size. ツAdjust as necessary for your server parameters.
  9. Delete the ibdata1, ib_logfile0 and ib_logfile1
      1. sudo rm /var/lib/mysql/ibdata1
        sudo rm /var/lib/mysql/ib_logfile0
        sudo rm/var/lib/mysql/ib_logfile1

  10. Start MySQL as in Step 5.
  11. Reload your database data.
    1. mysql -u [ツ-p zabbix < [path to backup location]/zabbix.sql

    2. Note: it窶冱 smaller! A lot smaller. ツBecause the tables are the correct size now and not filled with bloat.
  12. (Optional steps for Zabbix)
    1. It窶冱 going to hurt, but you窶决e going to need to drop some data from some tables or be willing to sit there for hours and wait.
    2. mysql -u [your db user] -p
      use zabbix;
      CREATE TABLE 窶鷲istory_uint_new窶 LIKE 窶鷲istory_uint窶;
      CREATE TABLE 窶trigger_uint_new窶 LIKE 窶trigger_uint窶;
      DROP TABLE 窶鷲istory_uint窶;
      DROP TABLE 窶trigger_uint窶;
      ALTER TABLE 窶鷲istory_uint_new窶 RENAME 窶鷲istory_uint窶;
      ALTER TABLE 窶trigger_uint_new窶 RENAME 窶trigger_uint窶;

    3. Please note: We do this instead of just deleting the data with a MySQL delete command because it would have to iterate through each record. ツThese tables are HUGE (of the 35GB import, roughly 32GB was in these two tables).
  13. Alter the table structure to ensure it is now innoDB
    1. mysql -u [your db user] -p
      use zabbix;

    2. For each table in your database run the command:
      1. ALTER TABLE [tablename] ENGINE=窶僮nnoDB窶;

    3. For Zabbix 2.4: here is a text file with all the alter table commands. ツJust copy paste. [Link]
  14. Time to check and optimize
    1. mysqlcheck -c -u [a root db user] -p 窶殿ll-databases

    2. If it finds problems, run:
      1. mysqlcheck -r -u [a root db user] -p 窶殿ll-databases

    3. mysqlcheck -o -u [a root db user] -p 窶殿ll-databases

  15. Schedule weekly optimizes
    1. Otherwise it窶冱 all moot: innodb will continue to grow and grow and won窶冲 give back space unless you schedule optimizes. ツI used crontab to do it.

Well, that窶冱 it.

On 2/22 at 10:30AM my server was using 96GB of it窶冱 120GB.

Today at 8:00AM my server was using 37GB of it窶冱 120GB. ツIt窶冱 collecting data constantly.

Comments   

 
0 #1 Guest 2020-04-05 12:28
Hello, yes this paragraph is really pleasant and I
have learned lot of things from it concerning blogging.
thanks.

Feel free to surf to my web-site; File Sharing free
Quote
 

Articles by Date

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email