Help with large, slow performing database

I have a mysql database that is about 22GB in size and has multiple tables with hundreds of thousands of records, my biggest table has 1.8 million records. The Database was not professionally designed by a DB admin, it was designed by a php developer with not a lot of experience in dealing with that much data. I know its not the largest amount of data but it is giving is very slow performance, some of it is the code written.

My question is, does my system currently support the DB of that size? I have a dedicated managed server from go-daddy, 32GB of RAM, quad core processor, 1TB of storage. We have about 100 users actively using our application looking for a single record within a specific table.

I am thinking about restructuring the DB and also going into cloud instead of dedicated server and going with nosql db management rather than our current relational.

However I am not a DB admin, I am a developer. I would love to hear your thoughts.

If you are looking at the database size, it is a small one, and the server you have, should be able to serve 100 concurrent users without a problem (running both the webserver and database server on the same machine). So the issues you are facing is architectural and/or code issues on your side.

The first thing I would look at is what engine is being used, if its MySQL database, does it use MyISAM or InnoDB? When you know this, you can review the MySQL database settings, and verify that it is tuned for the engine you use.

Then you need to check if you have the correct indexes setup for the queries that is being used. This is the part that will most probably give you most performance gain.

Finally review the code for the parts of the website that is slow. Remember that sometimes several separate queries are faster than one query with lots of joins/subqueries.

In regards to restructuring the DB, unless you have experience with modeling databases on this size or larger, you will most probably run into exactly the same issues you currently have. So if you do not have this experience, it might be better to just update the parts that is causing the current issues.

For moving into the Cloud and NoSQL, it feels like you are mentioning “hyped” words, without understanding the differences of them compared to what you use today. Using cloud hosting has its benefits and cons, but for the issue you mention, moving to the cloud will not solve your problem. The same with using NoSQL storage, depending on your database structure, doing this could potentiality give you worse performance and redundancy.

My recommendation is that you try to apply the points I mention above to the current code base, and then make another review. I.e. is the system responding fast enough now, or is a complete rewrite still justified.

2 Likes

Long ago I signed up with NewRelic, my partner couldn’t be talked into using the service.
Finally after hounding him, he broke down and started using it and it pinpointed slow and resource heavy queries and now everything is much more optimized.

Just a thought.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.