Menu
jimmytu
By:
jimmytu

mySQL droplet size

July 25, 2017 201 views
MySQL Data Analysis DigitalOcean Server Optimization API Ubuntu 16.04

I am looking to start a small project and looking into reading data from mutiple sources and inserting the data into a sql database. I plan to run my script every hour to start and it will add about 500~ records which each have about 10 fields. On a daily there will be about 12,000 records inserted into the database.

I currently have a $10 droplet. Do you think this will be sufficate for my requirements and how far down the line do you think I will need to upgrade to handle more data?

Log In to Comment
1 Answer
hansen July 25, 2017

Hi @jimmytu

It all depends. I have a project, where there's currently about 2 million records across 17 tables with 124 columns. Most columns are bigint and varchar, but there are a few text. There is roughly 1,000 new records every day.
All columns are indexed, so there are no "joins without index" and there's no cache in the system, so everything is "real time".
The system does about 1 million queries per day, where 98% is read and 2% is write.

This is being hosted on a single $10. My current estimate is that I can get to 4-5 million records before I need more RAM to avoid swapping.

Without knowing the exact data structure and how everything is tuned, then it's difficult to know the precise resource demands of your project.
I did a test a few months ago, where I just created a droplet with changing any configuration (meaning no tuning done at all) and removed all the indexes from the database (besides Primary Key). This resulted in me having to scale the droplet to $80, but it was still slower than the properly configured and tuned $10 droplet. At $160 it was a bit faster.

Reply
  • jimmytu July 25, 2017

    Thanks that help me get a general idea how much space I need.

    My columns will be a 2-3 short varcharand about 5 will be doublesthat will store exchange rate values. So sounds like I will be able to use my droplet for at least the first year without worrying about scalability.

    Currently I will be storing it all into one table without any joins. I may need to rethink that going forward but for current needs I think just one table alone will work.

    • hansen July 25, 2017

      @jimmytu

      You can probably really fast create a script that will insert 4,320,000 (500×24×30×12) records with random data. And then you know how much storage and RAM a single year would be. Then you can also see if it's still fast to do queries.
      And then run it again to add another years worth of data. And continue until you're satisfied with the knowledge you have about your data.

      Remember to tune MariaDB/MySQL, since it will become dreadfully slow if it's not using indexes correctly, or if you haven't adjusted the InnoDB pools to be greater than the amount of data you have in the database.

      https://tools.percona.com/ - the wizard is a good guiding tool
      https://github.com/major/MySQLTuner-perl - gives some pointers, but don't follow them blindly

      • luciele July 26, 2017

        @hansen

        i'm working on a website project developed in lemp stack and used phpmyadmin to manage mysql. if i migrate the database into dedicated droplets, can i still managed it using phpmyadmin (if yes, then i still need to install nginx/php in these droplets)? i don't want to use a mysql terminal interface via ssh if possible.

        after the database droplets up and ready i planned to create another droplet for the web application (nginx-php). if i then install the phpmyadmin here, how could i connect it to the mysql droplets?

        thank you

        • hansen July 26, 2017

          @luciele

          As long as your database is connected to the web server, then you can use phpMyAdmin on the web server - without also installing the full stack on the database server.

          But you need to configure the file config.inc.php located in the root folder of phpMyAdmin to be able to connect to a different server than localhost. If you installed phpMyAdmin via the apt command, then I think the config file is located somewhere else, but I'll have to check that. And of course, I would recommend using the DataCenter Network when connecting the web server and database (10.x.x.x) and you would be using the same connection for the phpMyAdmin config.

          Anyhow, if you have further questions, then please create a new question here in the forum, so we don't steal this thread :)
          You can always notify other users by using the @ if you want specific users to have a look at a question/comment.

Have another answer? Share your knowledge.

Related Questions