nZEDb

nZEDb => General Talk => Topic started by: phirestalker on 2016-05-18, 11:16:00 AM

Title: local or remote mysql
Post by: phirestalker 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
Title: Re: local or remote mysql
Post by: PREngineer 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.
Title: Re: local or remote mysql
Post by: brumsky 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.
Title: Re: local or remote mysql
Post by: phirestalker 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?
Title: Re: local or remote mysql
Post by: ThePeePs 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.
Title: Re: local or remote mysql
Post by: bobtongue 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
Title: Re: local or remote mysql
Post by: phirestalker 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???
Title: Re: local or remote mysql
Post by: kaibosh 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.

Title: Re: local or remote mysql
Post by: phirestalker on 2017-03-13, 07:50:13 AM
OK, competed mysqldump and imported. Everything is working GREAT!!!