MySQL Database Enhancement and Design
Posted by:
Dan Di
Date: April 18, 2020 10:49AM
Hi everyone,
I hope someone can chime in and help me out as I've recently created a SAAS based tool and I'm running in to some performance related issues. I'm trying to design and optimize the tool, and my bottleneck right now is at the database level.
A small background about the tool. It's web based and provides social media profile cards which are useful for internet/social media marketers. I have python scripts running in the background adding new profile cards to the same database every few seconds. When the tool is loaded on a web page, the most recently added profiles display first so the very first page load is dynamic. Only 28 profile cards display when the page loads, as I don't want the page load speed to take too long.
My issue at the moment is that web page takes 2-3 seconds to load those 28 profiles, which is extremely long, as there are no users on the platform also. I also cannot scale my jobs which are running in the background (the jobs which are responsible for adding new profiles and database entries) which is a also a big concern. As of now, 3 jobs are running in the background, but I need to scale to a level where dozens are running. I upped my job count to approx 15 the other day and the front end page load speed increased from 2-3 seconds to over 10 seconds, so I immediately had to kill them.
I have two big questions. Is there a way to optimize the database and front end to improve speed and performance. My second question is, what is the most optimal design.
In terms of the current design. My saas tool (front end) and the database are both hosted on the same VPS from DigitalOcean. This eliminates any network related issues pertaining to latency, speed, etc. My python jobs which update the database also run on the same server. I thought my server was overloaded with work, so I spun up a new VPS and moved the jobs over to it and I allowed database access from the second server to the main server. I ran my jobs on the second server and once the data was collected, it updated the remote database. This actually didn't solve anything, I assume bc the database was overloaded with dozens of connections and becoming overwhelmed. So I eliminated the servers performance being the problem.
At this point, I believe the database needs to be optimized, along with my design to support multiple user reads, and to support many different scripts running and adding db entries simultaneously 24/7.
1. Does anyone have any suggestions in terms of optimizing and configuring the SQL database side for my application?
2. Does anyone have any design suggestions?
2a. The design options I thought about was to move the database to a managed database in digitalocean, or spin up a dedicated VPS for the database alone, etc. Or, have a database just for reads (front end) and a second database which is used for data insertions and then the two database sync up overnight, etc. But I still feel like these solutions wont solve my problem and I'm really not an expert in this area.
Thank you much