Laravel uploading 25,000 records to database, database freezes???

Question

MySQL

Hello everyone, I ask for help, the experience is not so hot, but I really want to, in general the situation is this: I parse xml data into several arrays, everything happens in flight, then forech writes lines to the database (25,000) and it freezes, such uploads are needed regularly, how competently this task is solved, in the direction of the queues to read or know, from the documentation I realized that the queues also write to the database, then what's the point?, in a word, if you are not too lazy to share your experience, I will be very grateful. Thank you for your attention.

Answer 1

It is advisable never to access the database in a loop. Try inserting records with one query insertAll($items)

abstract class Model extends BaseModel
{
    /**
     * @return string
     */
    public static function table()
    {
        return with(new static)->table;
    }

    /**
     * Insert each item as a row. Does not generate events.
     *
     * @param  array  $items
     *
     * @return bool
     */
    public static function insertAll(array $items)
    {
        $now = \Carbon\Carbon::now();
        $items = collect($items)->map(function (array $data) use ($now) {
            return array_merge([
                'created_at' => $now,
                'updated_at' => $now,
            ], $data);
        })->all();

        return \DB::table(static::table())->insert($items);
    }
}

Answer 2

The queue is needed so that you can either use resources more evenly (perform resource-intensive tasks not simultaneously, but sequentially), or so that you can create a task from the web interface and continue working with the site without waiting for its completion.
You need to look at what you have and why it does not stand up. Most likely, there are simply not enough resources for such an operation. Maybe you are using them inappropriately. See if you have a memory leak in PHP.

Answer 3

It is necessary to wrap all requests in a transaction, or form one large request. Instead of the 25,000 that you are currently inserting.