Announcement

Collapse
No announcement yet.

backup and restore: need an advice

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

  • backup and restore: need an advice

    Hi,
    I am about to change server (and server farm).
    Our POST table is ~1.5GB with ~1.9GB index.

    The dump is pretty fast, the restore is not...
    What do you suggest to speed up the restore and bring the forum up sooner (that is always better than later )?

    - use the -k option during the dump
    - use the -k option during the dump and open the forum before the indexing is completed (besides temporary missing search results, is that any other issue with that?)
    - drop the POST INDEX table before the dump
    - any other?

    Besides, we are also going to change the STOPWORDS list, because our forum is not in English and words like FIVE or ZERO should be searchable.

    Thank you

  • #2
    Are you using the mysqlfull text search or the old standard vB search? You could just rebuild after.

    Comment


    • #3
      Originally posted by Zachery View Post
      Are you using the mysqlfull text search or the old standard vB search? You could just rebuild after.
      I am using the mysql search.

      >>You could just rebuild after.

      do you mean drop (empty?) the table first?

      Comment


      • #4
        Yes, you could empty it, or not back it up and re-create the table after the move.

        Comment


        • #5
          Originally posted by Zachery View Post
          Yes, you could empty it, or not back it up and re-create the table after the move.
          empty would be a problem because I need to make a test first leaving the forum opened.
          How can I exclude the post index from the backup? After the restore, how can I add them (using 3.8)?

          Thank you!

          Comment


          • #6
            You can exclude a table from a dump...
            Code:
            mysqldump -uUSER -pPASSWORD --ignore-table=DB_NAME.TABLE_NAME DB_NAME > BACKUP_FILE.SQL
            Ref: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
            Lats...

            Comment


            • #7
              Originally posted by Lats View Post
              You can exclude a table from a dump...
              Code:
              mysqldump -uUSER -pPASSWORD --ignore-table=DB_NAME.TABLE_NAME DB_NAME > BACKUP_FILE.SQL
              Ref: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
              Thanks Lats.
              I don't want to ignore the whole POST table, but just its index (post.MYI). Is it possible or the only way is to empty it and then rebuild?

              Comment


              • #8
                Oh, i thought you were talking about the postindex table. The fulltext indecies should rebuild moderately quickly. At least compared to the standard vBulletin postindex table.

                Comment


                • #9
                  I mis-understood your question too.
                  Lats...

                  Comment


                  • #10
                    sorry if the question was not clear enough.
                    BTW, I haven't found a way yet to exclude the indexes from the backup so they can be rebuilt from scratch during the restore (since we have also changed the stopwords list, we have to do it anyway). Just finding out if there is a way to speed things up.

                    Comment


                    • #11
                      They don't get backed up, the actual index data doesn't. While its getting restored, it does rebuild it though. The best way to speed up the restore is to give mysql enough memory to handle it. What does your my.cnf look like?

                      Comment


                      • #12
                        Originally posted by Zachery View Post
                        They don't get backed up, the actual index data doesn't. While its getting restored, it does rebuild it though. The best way to speed up the restore is to give mysql enough memory to handle it. What does your my.cnf look like?
                        ah ok!

                        PHP Code:
                        [mysqld]
                        port        3306
                        socket        
                        = /var/run/mysql/mysql.sock
                        # Change following line if you want to store your database elsewhere
                        datadir    = /dati/mysql
                        skip
                        -external-locking
                        key_buffer_size 
                        16M
                        max_allowed_packet 
                        1M
                        table_open_cache 
                        64
                        sort_buffer_size 
                        512K
                        net_buffer_length 
                        8K
                        read_buffer_size 
                        256K
                        read_rnd_buffer_size 
                        512K
                        myisam_sort_buffer_size 
                        8M

                        ft_stopword_file
                        =/dati/mysql/stopwordlist.txt

                        [safe_mysqld]
                        log-error    = /var/log/mysql/mysqld.log
                        socket        
                        = /var/run/mysql/mysql.sock

                        [mysqldump]
                        socket        = /var/run/mysql/mysql.sock
                        quick
                        max_allowed_packet 
                        16M

                        [mysql]
                        no-auto-rehash
                        # Remove the next comment character if you are not familiar with SQL
                        #safe-updates

                        [myisamchk]
                        key_buffer_size 20M
                        sort_buffer_size 
                        20M
                        read_buffer 
                        2M
                        write_buffer 
                        2M

                        [mysqlhotcopy]
                        interactive-timeout

                        [mysqld_multi]
                        mysqld     = /usr/bin/mysqld_safe
                        mysqladmin 
                        = /usr/bin/mysqladmin
                        log        
                        = /var/log/mysqld_multi.log 

                        Comment


                        • #13
                          If you have the resources, Doubling the buffers and cache sizes temporarly would help

                          Comment


                          • #14
                            Originally posted by Zachery View Post
                            If you have the resources, Doubling the buffers and cache sizes temporarly would help
                            thanks Zachery! We should have enough resources to do so.
                            Are you reffering to

                            PHP Code:
                            [mysqld]
                            key_buffer_size 16M
                            max_allowed_packet 
                            1M
                            table_open_cache 
                            64
                            sort_buffer_size 
                            512K
                            net_buffer_length 
                            8K
                            read_buffer_size 
                            256K
                            read_rnd_buffer_size 
                            512K
                            myisam_sort_buffer_size 
                            8M 


                            or

                            PHP Code:
                            [myisamchk]
                            key_buffer_size 20M
                            sort_buffer_size 
                            20M
                            read_buffer 
                            2M
                            write_buffer 
                            2M 
                            or both?

                            Comment


                            • #15
                              mysqld,

                              I would also recomend a larger packet size, 16mb at least.

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X