New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Is it faster to offload mysql database?
My database is slowly growing and it's currently at 3GB using WordPress as my cms with only w3 total cache plugin installed. It's starting to get slow so I'm not sure if offloading the database to a SSD vps is going to help especially when the SSD vps is located in Europe while the vps hosting the site is in USA.
Does offloading the database help improve speed if two vps aren't in the same location? or should I just split the database into smaller parts
Comments
I would not see an advantage or suggest locating your database so far away from your VPS. If you can find two VPS in the same location with a private vLan that is the way to go, in the case you are splitting them off from one another. You should get better performance.
Have you looked at your server load, memory usage, I/O and considered the possibility that you might need to upgrade your VPS package instead of purchasing a separate VPS?
In the positive, I would suggest investigating your options further and collecting some data (root cause analysis) before making a decision.
First of all, see how many IOP's you're doing on your database. Sometimes it might not be a good idea to have your database offloaded since you can't fine tune your database that's suited for your needs.
Second, offloading the database to a datacenter that has relatively high latency compared to the one you're using locally would hurt you more than it will help you.
Now if you don't mind shooting yourself in the foot, you might consider using MyISAM as the storage engine instead since you're doing mostly SELECT and INSERT queries. Sure MyISAM might give you more performance compared to InnoDB, but if anything goes wrong - you can pretty much kiss your database goodbye for all intents and purposes. Running backup operations against a MyISAM database isn't all that simple either.
Some junk in wp_options? Curious to see why the DB is so big.
I have one site that I offloaded the SQL to the provider for a while, not too impressed, I get a notification every other week telling me the connection to SQL failed (could be just temporarily rate-limited?) I think it is still better to run things under your own control if you can, otherwise you may as well, with all due respect, move to shared hosting and worry about nothing.
If your mysql instant is close to your WP, that will help. Otherwise latency between your WP and DC will adds up to your page load time.
Performance wise, since 3G of data is still consider small, scaling up your current HW/VPS still the best option. Or you can scale your mysql out to another VPS in the same network/DC.
I've tested a setup like this before and it was pretty bad. Particularly, the mysql db server was roughly 90ms away from the web server. I was using Drupal which made multiple calls to the db and so even though it was one page being loaded... the response times from the db resulted in page load times as high was 2 to 3 seconds... which believe it or not quite frustrating. Definitely recommend you doing at least ping tests to get a location with low latency like less than 10ms or perhaps offloading to another server within the same data center. Good luck!
If the provider offers the VM on the same network, and you have VM on HDD, then it would help increase the speeds, if it's 20+ ms away from that VM it would slow it down, seen a site run on 50-60ms on Remote MySQL without much speed difference as most queries are small so it would not matter much, but you are better testing it out and letting us know how it goes.
I think you should just increase an allowed RAM size for MySQL. It will be a great benefit.
Db ops/sec is hard-limited by Physics with high latency links.
Get a vps in same facility or at least same town (1ms is OK)
Perform tests before using more hardware! What's the bottleneck of your current setup (use top)? I/o? RAM? Do all your indexes stick in ram? Maybe add more ram to your mysql config file?
An SSD could help for sure, but nothing beats RAM if you can keep things cached there, that would be the best. Maybe you can upgrade the RAM on your VPS with your current provider if you don't have enough? Anyway, as others have said a database has to stay as close as possible of the main webserver.
Good luck!
If you want to offload MySQL, try to keep the database as close as possible to the servers using it. Latency ruins SQL performance a lot. On smaller setups you probably won't notice a few ms latency, but on higher loaded setups (especially with a lot of queries) you'll definitely have bottlenecks showing up.
We're managing a large and heavy loaded DB cluster with about 200GB of SQL data. Runs great with <1ms latency, but in case of an event/maintenance when a fallback cluster 30 ms away is taking over, we're seeing a lot performance degradation. Partially related to the amount of queries running - 30ms latency spread over a lot of queries that all have this latency is quite much!
So it all depends on your setup, amount of data and kind of queries.
Try to keep SQL running on the same server as long as possible. Invest in a good storage array (SSD) and plenty of memory. Besides MySQL, other services will definitely be happy with more RAM and speedy I/O.
Better to have one big powerful server doing everything, than a bunch of small boxes with a low amount of hardware resources.
On top of that - optimize, optimize, optimize. Standard MySQL configs are worthless, especially on larger setups. Those configs are rarely using all available hardware resources, and if they would, it would in a very inefficient way. There is not such a thing like a "standard" optimized config.
By just optimizing the configs to needs of your website/application, you can get a huge boost in SQL performance. This is the part where most admins fail.