Search code examples
mysqldatabaseheidisql

Mysql is sucking for large data

I have a MySQL database with around 30gb data. Some of the tables contain over 40 million rows. Ｉam using InnoDB. I query by only use "select count(*) from table_name" in local PC takes me around 5 minutes. I think it's impossible for me to the joining of the table. I would like to ask would there anything I could do to improve the performance. Or do I need to switch to another database? I never encounter such large data in DB. Please help.

Solution

  • I have run mysql instances with over 100 million entries, and delivering over 30 million queries per day. So it can be done.

    The problems you are experiencing will occur with any other database system if similarly configured.

    I can only give you a few tips, if this is mission critical consider hiring a professional to tweak your system.

    Basics that you need to look at;

    • This size database is best run on a dedicated server with SSD disks, and at least 2 cores;
    • Your going to need a lot of RAM in your server, at least your total database size + 20% for other system resources;
    • Make sure mysql has been configured with enough memory, 80% of your total RAM. The primary setting that does this will be innodb_buffer_pool_size;
    • Optimize your queries, and index where needed - this is a fine art but can drastically improve performance, learn to use EXPLAIN ... on your queries.