Support the ongoing development of Laravel.io →
umarcel
posted 10 years ago
Database

Hi I have the following Problem:

I have to insert more than 75 000 rows into one myisam table. I'm using mysql-driver. The first 1 000 rows are inserted relatively fast in one second, but with increasing number of inserts it's going to be slower and slower (example: when 62000 rows are insert the next 1000 rows need 2:38 minutes!) For all inserts the test system on my webhosting needs more than 100 Minutes! Here is the log of the last 63000 rows (just for showing the inrease of the time):

[2014-08-21 21:40:50] syhost.INFO: memory_limit:256M
[2014-08-21 21:40:50] syhost.INFO: Memory-Usage:5
[2014-08-21 21:40:52] syhost.INFO: Memory:5
[2014-08-21 21:40:53] syhost.INFO: Saving Data to DB...
[2014-08-21 21:40:53] syhost.INFO: Number of products: 75315
[2014-08-21 21:40:53] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:40:53] syhost.INFO: Produkt:0
[2014-08-21 21:40:54] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:40:54] syhost.INFO: Produkt:1000
[2014-08-21 21:40:55] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:40:55] syhost.INFO: Produkt:2000
[2014-08-21 21:40:57] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:40:57] syhost.INFO: Produkt:3000
[2014-08-21 21:40:59] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:40:59] syhost.INFO: Produkt:4000
[2014-08-21 21:41:03] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:03] syhost.INFO: Produkt:5000
[2014-08-21 21:41:07] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:07] syhost.INFO: Produkt:6000
[2014-08-21 21:41:11] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:11] syhost.INFO: Produkt:7000
[2014-08-21 21:41:16] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:16] syhost.INFO: Produkt:8000
[2014-08-21 21:41:22] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:22] syhost.INFO: Produkt:9000
[2014-08-21 21:41:28] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:28] syhost.INFO: Produkt:10000
[2014-08-21 21:41:34] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:34] syhost.INFO: Produkt:11000
[2014-08-21 21:41:41] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:41] syhost.INFO: Produkt:12000
[2014-08-21 21:41:49] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:49] syhost.INFO: Produkt:13000
[2014-08-21 21:41:58] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:41:58] syhost.INFO: Produkt:14000
[2014-08-21 21:42:07] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:42:07] syhost.INFO: Produkt:15000
[2014-08-21 21:42:18] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:42:18] syhost.INFO: Produkt:16000
[2014-08-21 21:42:29] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:42:29] syhost.INFO: Produkt:17000
[2014-08-21 21:42:42] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:42:42] syhost.INFO: Produkt:18000
[2014-08-21 21:42:55] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:42:55] syhost.INFO: Produkt:19000
[2014-08-21 21:43:12] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:43:12] syhost.INFO: Produkt:20000
[2014-08-21 21:43:28] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:43:28] syhost.INFO: Produkt:21000
[2014-08-21 21:43:45] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:43:45] syhost.INFO: Produkt:22000
[2014-08-21 21:44:02] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:44:02] syhost.INFO: Produkt:23000
[2014-08-21 21:44:20] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:44:20] syhost.INFO: Produkt:24000
[2014-08-21 21:44:42] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:44:42] syhost.INFO: Produkt:25000
[2014-08-21 21:45:07] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:45:07] syhost.INFO: Produkt:26000
[2014-08-21 21:45:36] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:45:36] syhost.INFO: Produkt:27000
[2014-08-21 21:46:03] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:46:03] syhost.INFO: Produkt:28000
[2014-08-21 21:46:34] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:46:34] syhost.INFO: Produkt:29000
[2014-08-21 21:47:14] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:47:14] syhost.INFO: Produkt:30000
[2014-08-21 21:48:06] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:48:06] syhost.INFO: Produkt:31000
[2014-08-21 21:48:59] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:48:59] syhost.INFO: Produkt:32000
[2014-08-21 21:49:44] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:49:44] syhost.INFO: Produkt:33000
[2014-08-21 21:50:41] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:50:41] syhost.INFO: Produkt:34000
[2014-08-21 21:51:42] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:51:42] syhost.INFO: Produkt:35000
[2014-08-21 21:52:49] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:52:49] syhost.INFO: Produkt:36000
[2014-08-21 21:53:57] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:53:57] syhost.INFO: Produkt:37000
[2014-08-21 21:55:08] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:55:08] syhost.INFO: Produkt:38000
[2014-08-21 21:56:23] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:56:23] syhost.INFO: Produkt:39000
[2014-08-21 21:57:44] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:57:44] syhost.INFO: Produkt:40000
[2014-08-21 21:59:11] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 21:59:11] syhost.INFO: Produkt:41000
[2014-08-21 22:00:35] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:00:35] syhost.INFO: Produkt:42000
[2014-08-21 22:02:05] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:02:05] syhost.INFO: Produkt:43000
[2014-08-21 22:03:35] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:03:35] syhost.INFO: Produkt:44000
[2014-08-21 22:05:13] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:05:13] syhost.INFO: Produkt:45000
[2014-08-21 22:06:55] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:06:55] syhost.INFO: Produkt:46000
[2014-08-21 22:08:40] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:08:40] syhost.INFO: Produkt:47000
[2014-08-21 22:10:23] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:10:23] syhost.INFO: Produkt:48000
[2014-08-21 22:12:14] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:12:14] syhost.INFO: Produkt:49000
[2014-08-21 22:14:09] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:14:09] syhost.INFO: Produkt:50000
[2014-08-21 22:16:13] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:16:13] syhost.INFO: Produkt:51000
[2014-08-21 22:18:18] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:18:18] syhost.INFO: Produkt:52000
[2014-08-21 22:20:20] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:20:20] syhost.INFO: Produkt:53000
[2014-08-21 22:22:27] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:22:27] syhost.INFO: Produkt:54000
[2014-08-21 22:24:33] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:24:33] syhost.INFO: Produkt:55000
[2014-08-21 22:26:51] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:26:51] syhost.INFO: Produkt:56000
[2014-08-21 22:29:11] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:29:11] syhost.INFO: Produkt:57000
[2014-08-21 22:31:31] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:31:31] syhost.INFO: Produkt:58000
[2014-08-21 22:33:55] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:33:55] syhost.INFO: Produkt:59000
[2014-08-21 22:36:23] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:36:23] syhost.INFO: Produkt:60000
[2014-08-21 22:38:54] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:38:54] syhost.INFO: Produkt:61000
[2014-08-21 22:41:28] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:41:28] syhost.INFO: Produkt:62000
[2014-08-21 22:44:06] syhost.INFO: Memory-Peak-Usage:97
[2014-08-21 22:44:06] syhost.INFO: Produkt:63000

And here is the code snipped:

\DB::connection()->disableQueryLog();
        set_time_limit(0);
        ini_set('memory_limit', '256M');
        ...
        //log memory usage
        $memoryUsage = ceil(memory_get_usage() / 1024 / 1024);
        Log::info('Memory:' . $memoryUsage);
        //load simple xml data
        $simpleXmlproductsData = simplexml_load_file($productsFile);
        Log::info('Saving Data to DB...');
        $productsCount = count($simpleXmlproductsData->product);
        Log::info('Number of products: ' . $productsCount);
        //insert data to db
        for ($i = 0; $i < $productsCount; $i++) {
            \DB::table('properties')->insert(array(
                array(
                    'property_name' => (string) $simpleXmlproductsData->product[$i]->name, 
                    'property_program' => (string) $simpleXmlproductsData->product[$i]->name, 
                    'property_number' => (string) $simpleXmlproductsData->product[$i]->number, 
                    'property_zupid' => (string) $simpleXmlproductsData->product[$i]->zupid, 
                    'property_description' => (string) $simpleXmlproductsData->product[$i]->description, 
                    'property_description_code' => (string) $simpleXmlproductsData->product[$i]->longDescription, 
                    'property_price' => (string) $simpleXmlproductsData->product[$i]->price,
                    'property_terms' => (string) $simpleXmlproductsData->product[$i]->terms, 
                    'property_last_modified' => (string) $simpleXmlproductsData->product[$i]->lastModified, 
                    'property_large_image_url' => (string) $simpleXmlproductsData->product[$i]->largeImage, 
                    'property_currency_code' => (string) $simpleXmlproductsData->product[$i]->currencyCode, 
                    'property_extra_description' => (isset($simpleXmlproductsData->product[$i]->extra1)) ? (string) $simpleXmlproductsData->product[$i]->extra1 : NULL, 
                    'property_postcode' => (isset($simpleXmlproductsData->product[$i]->extra3)) ? (string) $simpleXmlproductsData->product[$i]->extra3 : NULL, 
                    'property_city' => (isset($simpleXmlproductsData->product[$i]->extra4)) ? (string) $simpleXmlproductsData->product[$i]->extra4 : NULL, 
                    'property_additional_costs' => (isset($simpleXmlproductsData->product[$i]->extra5)) ? (string) $simpleXmlproductsData->product[$i]->extra5 : true,
                    'property_living_area' => (isset($simpleXmlproductsData->product[$i]->extra6)) ? (string) $simpleXmlproductsData->product[$i]->extra6 : NULL, 
                    'property_parcel' => (isset($simpleXmlproductsData->product[$i]->extra7)) ? (string) $simpleXmlproductsData->product[$i]->extra7 : NULL, 
                    'property_nr_rooms' => (isset($simpleXmlproductsData->product[$i]->extra8)) ? (string) $simpleXmlproductsData->product[$i]->extra8 : NULL, 
                    'property_build_year' => (isset($simpleXmlproductsData->product[$i]->extra9)) ? (string) $simpleXmlproductsData->product[$i]->extra9 : NULL, 
                    'property_merchant_category' => (string) $simpleXmlproductsData->product[$i]->merchantCategory, 
                    'property_deep_link' => (string) $simpleXmlproductsData->product[$i]->deepLink, 
                ))
            ); 
            
            \DB::disconnect();//just tried if the perfomance does not slow down, when disconnecting -> but does not help
            
            //log only 1000 inserts
            if ($i % 1000 == 0) {
                $memoryPeakUsage = ceil(memory_get_peak_usage() / 1024 / 1024);
                Log::info('Memory-Peak-Usage:' . $memoryPeakUsage);
                Log::info('Produkt:' . $i);
            }

First I tried with Eloquest Model and then with Query Builder. All shows the same speed down problem. So what could I do? I hope you can help me.

Last updated 3 years ago.
0
awsp
replied 10 years ago

My 2 cents,

Option 1 - Split your inserts to let's say 10,000 every time.

Option 2 - I sometimes loop thru and echo out the insert statement to something like less than 1000 rows and insert directly into MySQL (I don't think this is a good idea for 75k rows tho ... ) lol

insert into table (...) values (...); 
insert into table (...) values (...); 
insert into table (...) values (...);

Option 3 - If you are importing from .sql you should really try the following, this way is way faster than using PHP

mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

Option 4 - Looks like you are having an XML document, I never tried this tho, but you could take a look at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file

But I mean, ultimately for importing big data, PHP is not a very good choice. :-(

Last updated 3 years ago.
0
umarcel
replied 10 years ago

Thank you awsp. I will try to increase the amount of values inserted at once. My last try was with 25 Values each insert, because I read an article that this value has a good performance. Option 3 is not a choise, you are correct I'm importing data from xml. Option 4 seems also not really helpful.

Any other ideas?

Last updated 3 years ago.
0
umarcel
replied 10 years ago

I tested it now with 1000 rows each insert. But I get the same slowing down performance. When I stop importing and start the script again, then again the first thousands inserts go fast and slow then down no matter how many products are already in the table before starting the script. So indexes should not be the problem.

So what could I do? One idea is to stop after 10000 inserts and then run the script again. But I want to create a cronjob and just have a webspace without ssh and shell_exec-function is disabled, soI can't use this to start and finish scripts in a loop.

Any other idea?

Last updated 3 years ago.
0
awsp
replied 10 years ago

Looks like you are doing insertion directly to your web host. (?)

Maybe you should first import XML file to your local machine and export to .sql file and do option 3 I mentioned before and insert to your web host database.

Last updated 3 years ago.
0
umarcel
replied 10 years ago

Like I wrote I want to do a cronjob, that runs every day to import the products from a webservice. So at least I will have to use a "own" webserver with ssh, because it does not end with 75000 products...at least it could be over 1 000 000 products.

Last updated 3 years ago.
0
kompas
replied 10 years ago

I just worked on a importer which had to import data from 8 MsSql tables with 180k-450k rows each. At the moment we can do the full import in about 20 minutes.

What we have learned:

  • Do not edit data on the fly.
  • Select data in blocks of 500 rows
  • If you want to edit the data: do it directly after the import
  • We needed 4gb memory just for the importer (that is a lot!)
  • test for fastest solutions. It is all about milliseconds but you spare minutes at the end.

We used Zend but it shouldn't make a difference.

Last updated 3 years ago.
0
umarcel
replied 10 years ago

@Kompas: Thanks for your advices. :) Can you tell me what webserver (dedicated or virtual, size of ram, nr cores, etc) you are using for yur application?

Last updated 3 years ago.
0
barryvdh
replied 10 years ago

And don't forget to disable the query log (\DB::disableQueryLog();)

Last updated 3 years ago.
0
iansirkit
replied 10 years ago

You should see if switching to mysqli driver speeds things up. It is supposed to be faster but I have never benchmarked it.

Last updated 3 years ago.
0

Sign in to participate in this thread!

PHPverse

Your banner here too?

umarcel umarcel Joined 21 Aug 2014

Moderators

We'd like to thank these amazing companies for supporting us

PhpStorm NativePHP
Fathom
Forge
Envoyer
Beyond Code
BairesDev
N-iX

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

Laravel.io

Forum Articles Pastebin

Socials

X (Twitter) Bluesky Bluesky GitHub

The community

Laravel Laravel Laravel News Laravel News
Laracasts Laracasts Laravel Podcast Laravel Podcast
© 2025 Laravel.io - All rights reserved.
Terms of service Privacy policy Advertise