1

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.

closed as too broad by RolandoMySQLDBA, RLF, Philᵀᴹ, Michael Green, Max Vernon Oct 25 '15 at 19:13

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    I appreciate your effort at asking this question, but unfortunately, this is not enough information, or to be more precise, enough specific information. You would need a db admin to look over and determine if the server is adequate or could be made adequate through optimization. 22 GB and 1.8 million records is actually on the small side, so I would think it should be plenty on the server side. So much depends on the types of queries, output, hot-spots, I/O, network, etc that it is not so simple as a yes or no. Also, NoSQL solves a particular problem that I don't even know if you have. – Thronk Oct 23 '15 at 18:32
  • When your table gets too big for disk, we can discuss "too big". – Rick James Nov 3 '15 at 19:45
  • "Nosql" needs some expertise, too. What makes you think you will be any better off with it instead of what you have? – Rick James Nov 3 '15 at 19:46
  • Things are slow? Let's fix them. Please provide a slow query together with SHOW CREATE TABLE for any table(s) that query uses. The "fix" may be as simple as adding a composite index. – Rick James Nov 3 '15 at 19:47
4

I would like to echo @Thronk 's remarks about not having enough information to fully answer your question.

However, take a look at this article by a guy who sold his business to eBay and is still senior there.

This suggests to me that you would be far better off redesigning/refactoring your database according to proper relational principals rather than jumping on the NoSQL bandwagon which will involve more work and greater risk. As @Thronk points out, 22GB is not a large system.

If you're using 5.6, try using the performance_schema (p_s) to pick the low-hanging fruit first, which may give you the breathing space you need to properly redesign.

Don't be afraid to come back here with design questions and problem SQL - don't forget that the more detail you put into your question, the better your answers will be. It may be that some reindexing may help - again, don't hesitate to ask here. p.s. welcome to the forum!

4

Without a doubt don't even look at changing to NoSQL yet. Please be aware of the other considerations such as the benefits/downfalls of these systems. They are often JSON based, offer less ACID compliance, and are at various maturity levels; however for some data systems they are great.

NoSQL Benefits and Pitfalls (Cliff Notes):

They are often volume, throughput, or concurrency but often do that at the expense of ACID compliance on the DB Engine so you have to be very careful with your data governance. Often, you are fully responsible that your code has proper transaction controls, isolations, rollback capability, etc. You're not going to get that from the DB Engine anymore. Similar to the 2 DB engines of MySQL (MyISAM vs InnoDB) but on a much bigger level.

Systems such as Hadoop are good at processing petabytes upon petabytes, maybe scouring 10% of the internet every night and indexing it. This is not your scenario.

Others are more throughput based such as MongoDB which gives you a lot of parallel concurrency through dropping a lot of ACID compliance in the DB engine and putting the onus on the developer; which is great in some scenarios. However you only have 100 concurrent users so that is not even near an issue yet.

Google Elastic would be more useful for something that requires massive throughput. I mean 50,000 servers reporting into a cluster for near real time data for example. This requires tons of RAM usually, and this also is not your use case.

Your Scenario:

In your case, you're not even near the realm of big data when your entire data set can fit in $350 worth of RAM. You'll probably want to start with index analysis and what queries are the worst offenders that are often used. Try a free installation of a program like SolarWinds or many of the other ones. Install it on a new AWS machine, then delete it when you're done. Use it to profile your database server and see what is going on. Then you can diagnose the problem a lot easier and come up with solutions. Some are more complicated and require you to engage with a sales engineer while others can be installed themselves. I find these tools great for non SQL specialists.

See a bigger list here.

  • 2
    I would remove the "yet" from the first line. Or change it to "ever" ;) – ypercubeᵀᴹ Oct 24 '15 at 9:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.