Inserting 60 000 rows taking too long

by 17 replies
20
I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting.
Shouldn't it be faster?
Code:
INSERT INTO ip2nation (ip, country) VALUES
 (687865856, 'za'),
 (689963008, 'eg'),
 (691011584, 'za'),
 (691617792, 'zw'),
 (691621888, 'lr'),
 (691625984, 'ke'),
 (691630080, 'za'),
 (691631104, 'gh'),
 (691632128, 'ng'),
 (691633152, 'zw'),
 (691634176, 'za'),
 (691650560, 'gh'),
...
#programming #inserting #long #mysql #rows #taking
  • I would think so, but try breaking it into smaller chunks, like 10,000 at a time.

    If it's not done yet after 4 + hours, something's hung up.
  • There are a few factors that affect the record insertions. I don't know much about other databases, but for PostgresSQL, the hardware matters:

    * SSD vs hdd.
    * RAID level (1,5,6, etc)
    * disk layout (storing WAL on a separate partition seems to bring better performance)
  • So it is ok for it to take it so long. Because I read that it may take up to few seconds
    • [1] reply
    • An hour is way way too long for 60,000 queries, unless your PC is very slow/weak. Even on an average PC it would not take that long.
  • Especially on localhost it should be faster.
    There should be some logging functions in your database you can turn on to profile the activities.
  • Try Bigdump by ozerov

    Saved me on a 400mb database and it imported in seconds.
  • I think that you would need to clear the query cache.

    Check this article out for more details: Clearing Cache for SQL Server Performance Testing

    Basically this is what you would need to use as mentioned in the article:

    • [1] reply
    • No need.

      He's using MySQL, not SQL Server. Your suggestions won't work.

      OP, that insert statement should take no more than a few seconds to run.

      I have the same db, just for giggles I dumped the table, truncated it, and ran the insert. 59,667 rows in 2.46 seconds.

      You have some other issue going on. Make sure your table columns are defined correctly, and not using varchar column types.
      • [1] reply
  • Banned
    [DELETED]
  • Too little information.
    It's taking too much time, that's for sure.
    Check for indexes or triggers on the table that could be slowing down the process.
    The way you are doing it, you have only one transaction involved. It could have an impact if you are already swapping.
    Make it one insert per row, just to check how it goes.

    Monitor your CPU and memory usage when you run the insert.
    Make a dump of the database, move it to a different box, check there.

    Hope this helps.
  • I don't care how many indexes you have, you're only inserting 2 fields, it should only take a few seconds even if there's 60K rows. It sounds like a table deadlock to me. When you run the following query, what does it say?
    Code:
    mysql> show processlist;
    There should be a "time" column that shows how long each query has been running. If you see any other suspect query that has been running longer than your insert has, and you don't need it, find the process id (left hand column), and kill it like so:
    Code:
    mysql> kill 123456;
    I'm really only guessing based on information at hand, but this might work.
  • why would you do that line by line? can you import from a file? it'd be much faster because the server would treat it as a table...
  • Another vote for BigDump. I don't know how it does it, but it saved my ass when I couldn't figure out how to upload a 140Mb table dump to my GoDaddy MySQL hosting plan.

    I'll also add that Perl is immensely fast at processing text files, which often comes in handy when you're doing big stuff with databases.
  • Check your indexes, sqlloader with a bit of sed/awk preprocessing can be your friend for bulk loading
Join the discussion

Next Topics on Trending Feed

  • 20

    Inserting 60 000 rows taking too long

    I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. Shouldn't it be faster? Code: INSERT INTO ip2nation (ip, country) VALUES (687865856, 'za'), (689963008, 'eg'), (691011584, 'za'), (691617792, 'zw'), (691621888, 'lr'), (691625984, 'ke'), (691630080, 'za'), (691631104, 'gh'), (691632128, 'ng'), (691633152, 'zw'), (691634176, 'za'), (691650560, 'gh'), ...