Handling 100 million + records with Laravel and Mysql

Question

I have a Laravel application that has about 52 million records on a one table serials table below.

Schema::create('serials', function (Blueprint $table) {
        $table->id();
        $table->bigInteger('pinNumber');
        $table->bigInteger('serialNumber');
        $table->boolean('checked')->default(0);
        $table->boolean('status')->default(0);
        $table->string('lotNumber')->nullable();
        $table->Integer('checkCode');
        $table->index(['serialNumber','pinNumber']);
        $table->softDeletes();
        $table->timestamps();
    });

I am planning to have about 100 million records but the web is extremely very slow with the current 52 million records.

My insertions( autogenerated serials) is working fine but getting the counts as per below is taking more time than expected.

    $totalSerials = Serial::max('id');
    $totalDownload = Lot::sum('count');
    $appovedCodes = Serial::where('checked', true)->count();

Please advise on the best way to handle big data with laravel. I have a sytem with 8gb ram and 160gb ssd.

COUNT() operations on InnoDB tables are a known weakness. It has to visit every row to count it, which means such queries cannot be optimized. The way to optimize it is to maintain a summary table with the current count. — Bill Karwin, Jan 27 '21 at 20:34
Might be relevant: https://stackoverflow.com/questions/10524651/is-there-any-performance-gain-in-indexing-a-boolean-field — apokryfos, Jan 27 '21 at 20:35
Do you have an index on `checked` column? Anyway, may be better to change from Mysql to NoSQL. — Felippe Duarte, Jan 27 '21 at 20:50
If you rarely change the `checked` value you can also consider splitting your table into two like e.g. `unchecked_serials` and `checked_serials` since (presumably) counting everything in a table is faster than counting with a filter — apokryfos, Jan 27 '21 at 20:52
this question would help you: https://dba.stackexchange.com/questions/188667/best-database-and-table-design-for-billions-of-rows-of-data — Abilogos, Jan 27 '21 at 20:53

score 2 · Answer 1 · edited Jan 27 '21 at 20:46

For huge amount of data in order of 100 million and also your case when you don't need relational database,

it's recommended to use nosql DBMS like mogodb.
But indexing can help dbms for faster query results.
Mostly in these situations you should improve your database performance instead of relying on laravel. I suggest you to partition your table on your created_at column if date intervals are not close, let's say monthly partitioning. This will also help better performance for your database.
Also another idea is if you don't want these results like Lot::sum('count') or Serial::where('checked', true)->count() instantly, store them each night on separate temprory table and each night update it's value.

This last idea is not recommended for operational database. It's for BI process and Datawarehouse. Maybe you should consider something like this!

score 1 · Answer 2 · answered Jan 27 '21 at 20:49

1

I suggest to use Cache Servers like Redis or Memcache and store cached result instead of calculating them each time.

like :

$totalDownload = Cache::remember('total_download', 60*60 /*$seconds*/, function () {
    return Lot::sum('count');
});

answered Jan 27 '21 at 20:49

Abilogos

4,777
2
19
39

Handling 100 million + records with Laravel and Mysql

2 Answers2