News:

Wondering if this will always be free?  See why free is better.

Main Menu

new server

Started by Jeff B, December 28, 2017, 02:23:35 PM

Previous topic - Next topic

Jeff B

Our forum has 1.6 million posts  I've never uses mysql dump.

aegersz

oh i see ... time for a long walk, dinner and then a movie.

wow - what a huge forum !
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

Jeff B

Im wondering if smf_log_boards  and smf_log_topics (5,023,244 records) should be emptied and I try again.  I don't think it can ever finish at this rate. Plus, I am doing this to test the new server, so I'll have to do it again when we want it to go live and switch from the old.  This is a conundrum!

Jeff B

Could my major malfunction here have been what I discovered?  That mysql is not available on the server? Only mysqli?  Would that screw up trying to restore?  I've asked them to knock me back to this instead of php7

PHP support    Yes / PHP 5.6.33
CGI support    Yes
SQL support    Yes / MySQL 5.5.58-0+deb8u1

Jeff B

Okay, this is what I know. the database restore seemd to go to a crawl when ever it hits a InnoDB table. namely, the logs type tables, which are in the millions of records. Any advice?  empty them?  can I convert them?   I'm lost here.

aegersz

there are so many ways to do this with varying degrees of success and i am no DBA so when i came across this very simple and novell approach without using any DB dump/restore tool, i thought i would pass it on.

see http://ilyasemin.com/how-to-quickly-copy-mysql-innodb-database-without-using-mysql-dump/

this sounds like it might be worth considering as it simply copies the DB's underlying files from like-to-like OS architecture.

just a thought.
The configuration of my Linux VPS (SMF 2.0 with 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

shawnb61

I suspect something else is going on here...   

My DB is a little less than half that size -  log_topics has 2M records.  All of my tables are InnoDB.  But backups & restores only take a few minutes. 

Two ideas to look into... 

First - If you slow down on InnoDB records, then maybe your DB isn't configured for InnoDB.  You may need to do some research & configuration there.  If you started with MyISAM, it is probable your environment is not configured to do InnoDB - you have to do that yourself.  Pay attention to the fields like innodb_log_file_size and innodb_log_buffer_size. 

Second - Take a look at your search tables - log_search_*.  THOSE tables can get HUGE.  Sometimes I exclude them & rebuild the search index on the target system instead.  That can be 1/3 to 1/2 of your backup right there. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Jeff B

At some point years ago, I had help optimizing my forum. I beleive that is when they converted the log_* tables to innobd, the others are myislam Using the nyqldumper tool, it shows what is going on when trying to restore, that is why I know that is it is the innobd tables that come to a screeching crawl.   It shows the speed of the creation of the tables and records.  You see it  from from 20-30,000 records per refresh f the program to 100.

shawnb61

It sure sounds like more tuning is needed.

What are your values for innodb_log_file_size and innodb_log_buffer_size?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Aleksi "Lex" Kilpinen

That is how MySQLDumper works. It is meant to limit the speed, in order to allow for different types of hosting environments.

https://www.tecmint.com/mysqldumper-a-php-and-perl-based-mysql-database-backup-tool/
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Jeff B

Yes, I understand that, but what is my solution?  Doing the math it would take a week for the database to restore, assuming nothing happened to stop the process in the middle.

Aleksi "Lex" Kilpinen

#31
If it slows down conciderably, it is likely either hitting memory limits or row limits given to it.
What you can do, is see if you have room to raise them, or trim the original contents ( For example set your search to use no index before making the backup ) or import in another way ( such as through SSH or hosting control panel ).

Edit: When I last imported a backup of my almost 3 million post forum, I left it running over night... Took hours, but it got done.
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Jeff B

It must have been something in the way that mysqldumper was saving the DB.   I taught myself how to run sql dump from th command lind to backup and then restore a database and all went well.   I have a test site on the new server and seems to be fine.  Thanks for the comments everyone.  I think I am good, so I will mark this topic as solved


Aleksi "Lex" Kilpinen

Yeah, MySQLDumper works through your webserver, and has to cope with many limitations like timelimits, memorylimits, and so on. For example a database with 3 million rows, at 50000 rows per refresh would take 60 refreshes if no other limits than rows/refresh come to play. A database with 2 million messages can easily have multiples of that as their row count. Import through shell will bypass all those limitations, and finish much much quicker.
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Advertisement: