Page 1 of 1

Insertion 500000 records causing problem !

PostPosted: 22. November 2020 10:34
by lonelywalker
Hello.
I'm using xampp to develop my laravel project.
I'm trying to insert data of 532742 records and inserting stopping for some reason at 530987.
I already made some changes in the mysql.ini increased speed of insertion
innodb_buffer_pool_size=2000M
innodb_log_file_size=400M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2

I got this error in apache>logs
[Sun Nov 22 10:26:12.680261 2020] [php7:error] [pid 15776:tid 1816] [client 127.0.0.1:62369] PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 20480 bytes) in D:\\Users\\OMEN\\xampp\\htdocs\\priceagregator\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Query\\Builder.php on line 2774, referer: http://127.0.0.1/priceagregator/public/Volvo
[Sun Nov 22 10:26:12.688238 2020] [php7:error] [pid 15776:tid 1816] [client 127.0.0.1:62369] PHP Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 32768 bytes) in D:\\Users\\OMEN\\xampp\\htdocs\\priceagregator\\vendor\\symfony\\error-handler\\Error\\FatalError.php on line 1, referer: http://127.0.0.1/priceagregator/public/Volvo

I'm not big expert in mysql settings, may be someone can help ?

Re: Insertion 500000 records causing problem !

PostPosted: 22. November 2020 11:33
by Nobbie
Its not a Mysql issue, its a PHP problem. You have to increase memory_limit in php.ini. It looks as currently it is set to 512 MB or similar. If unsure, set it to -1 in php.ini, that leads to no limit at all (i.e. all memory from the system may taken).

Restart Apache after changing php.ini

Re: Insertion 500000 records causing problem !

PostPosted: 22. November 2020 13:28
by lonelywalker
Hello.
I set memory_limit to -1 as you suggested.
Now behaviour of the program changed. Earlier it showed page "server is not responding" after inserting of data stopped.
Now page with the form stays unchanged, but inserting of the data stopped already, i can check it with phpmyadmin, total records not increasing.
May be problem little deeper.

Re: Insertion 500000 records causing problem !

PostPosted: 22. November 2020 14:11
by Nobbie
Then you have to ask the programmer of your scripts, it needs to much memory. You should try to insert the records without the help of PHP scripts (directly using mysql command interpreter).