Author Topic: local or remote mysql  (Read 7711 times)

Offline phirestalker

  • Junior Indexer
  • **
  • Posts: 49
  • Helpful: +0/-0
local or remote mysql
« on: 2016-05-18, 11:16:00 AM »
I have a "server" set up now with nzedb. It only has 2 cores at 2.8GHZ and 4GB of RAM. nzedb is all it is doing and it can barely handle 20 groups. It is much faster since I converted to cinnodb tables.

Here is my question. Will it benefit me to move just the mysql server to a machine on my local 100MBs network if it has 4 cores at 3.3GHZ and 16 GB of RAM? or will the network be too much of a bottleneck to see improvement? Or is mysql even 50% of the resources of running nzedb?
Would your answer be different if I upgraded to 1GBs network?

Thanks

Offline PREngineer

  • Junior Indexer
  • **
  • Posts: 30
  • Helpful: +3/-0
Re: local or remote mysql
« Reply #1 on: 2016-08-18, 11:28:45 PM »
MySQL is handling MOST of the CPU load.  I'm planning on switching my SQL instance to an outer server to improve the response times from the webserver.

If you try this, please post your results.

Offline brumsky

  • Junior Indexer
  • **
  • Posts: 17
  • Helpful: +0/-0
Re: local or remote mysql
« Reply #2 on: 2016-12-28, 08:47:32 AM »
It would help if you posted more information regarding your DB size, number of releases et cetera.  I'd say start by increasing the RAM 4 Gigs is a bit on the light side.  At least 8 gigs if not 16gigs then try again.  The more you can fit in RAM the better.

Offline phirestalker

  • Junior Indexer
  • **
  • Posts: 49
  • Helpful: +0/-0
Re: local or remote mysql
« Reply #3 on: 2017-03-09, 03:05:01 PM »
I am thinking of tackling this today. What is the easiest way to move the mysql over to another server? Can I shut down mysql dump it and load the dump on the new server, then change the config file to point to the new server? Is there a better way?

Offline ThePeePs

  • Overlord
  • ******
  • Posts: 44
  • Helpful: +7/-0
  • Hardware mod'er and p/t coder
    • nZEDb by ThePeePs
Re: local or remote mysql
« Reply #4 on: 2017-03-09, 07:50:14 PM »
You could do that, or just copy the files (usually located in /var/lib/mysql/<db_name>).  I would use rsync for this, and it should work, if not, you can always do a dump, and then import onto your new box.

Offline bobtongue

  • Prolific Indexer
  • ****
  • Posts: 109
  • Helpful: +7/-0
  • Wherever you go, There you are!
Re: local or remote mysql
« Reply #5 on: 2017-03-10, 05:13:57 AM »
rsync is excellent for that. I just moved my server to a new box and rsync'ed the whole thing over. Worked like a charm

Offline phirestalker

  • Junior Indexer
  • **
  • Posts: 49
  • Helpful: +0/-0
Re: local or remote mysql
« Reply #6 on: 2017-03-12, 08:42:53 PM »
ya after 2 days of frustration and googling, I realize I left out some important information....

I am using Innodb file per table with mariadb

Innodb cannot be imported from just moving files, and as yet I still don't have a working answer on how you DO move them. I tried percona-xtrabackup which is supposed to be made to move Innodb, yet it didn't work.

There are inconsistencies in the log even though I did the backup with the server stopped, and somehow the mysql database is missing all it's tables.

Does anyone know how to move a database with Innodb tables???

Offline kaibosh

  • Overlord
  • ******
  • Posts: 161
  • Helpful: +18/-0
Re: local or remote mysql
« Reply #7 on: 2017-03-13, 12:44:08 AM »
A simple copy of the DB files will generally work for a single database if both the target and destination DB instances are configured identically and the ibdata1 file is copied along with the DB directory.

A "cleaner" way to migrate the database is to export/dump to an SQL file and import that into the new instance.

Edit: nzedb has recently started using stored procedures. Use the --routines option to ensure these are copied as well.

« Last Edit: 2017-03-13, 11:57:20 PM by kaibosh »

Offline phirestalker

  • Junior Indexer
  • **
  • Posts: 49
  • Helpful: +0/-0
Re: local or remote mysql
« Reply #8 on: 2017-03-13, 07:50:13 AM »
OK, competed mysqldump and imported. Everything is working GREAT!!!
« Last Edit: 2017-03-13, 01:32:36 PM by phirestalker »