Advertisement:

Author Topic: new server  (Read 1569 times)

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #20 on: January 10, 2018, 09:07:30 AM »
Our forum has 1.6 million posts  I've never uses mysql dump.

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,445
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: new server
« Reply #21 on: January 10, 2018, 09:09:56 AM »
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 140+ mods) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #22 on: January 10, 2018, 09:18:55 AM »
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!

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #23 on: January 10, 2018, 04:18:14 PM »
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

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #24 on: January 10, 2018, 06:34:10 PM »
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.

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,445
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: new server
« Reply #25 on: January 11, 2018, 01:27:30 AM »
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 140+ mods) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 943
    • sbulen on GitHub
Re: new server
« Reply #26 on: January 11, 2018, 01:52:17 AM »
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

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #27 on: January 11, 2018, 08:27:39 AM »
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.

Offline shawnb61

  • Support Specialist
  • Sr. Member
  • *
  • Posts: 943
    • sbulen on GitHub
Re: new server
« Reply #28 on: January 11, 2018, 09:33:50 AM »
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

Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 17,390
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • aleksi-kilpinen on LinkedIn
Re: new server
« Reply #29 on: January 11, 2018, 09:40:22 AM »
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/
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.

How you can help SMF

"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

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #30 on: January 11, 2018, 09:46:18 AM »
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.

Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 17,390
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • aleksi-kilpinen on LinkedIn
Re: new server
« Reply #31 on: January 11, 2018, 09:51:12 AM »
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.
« Last Edit: January 11, 2018, 10:30:20 AM by Aleksi "Lex" Kilpinen »
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.

How you can help SMF

"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

Offline Jeff B

  • Full Member
  • ***
  • Posts: 407
Re: new server
« Reply #32 on: January 11, 2018, 03:53:59 PM »
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


Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 17,390
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • aleksi-kilpinen on LinkedIn
Re: new server
« Reply #33 on: January 11, 2018, 11:57:01 PM »
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.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.

How you can help SMF

"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