Professional Web Applications Themes

Bulk load performance - MySQL

Hi all, I'm in the process of selecting a DBMS for a project. We've narrowed down our choices to MySQL, Oracle, and PostgreSQL. One thing we're not sure is the speed of bulk loading. Some of the tables could contain tens or hundreds of millions of tuples. So, does anyone know of any benchmarks about this stuff? I tried searching online but couldn't find anything. Thank you....

  1. #1

    Default Bulk load performance

    Hi all,

    I'm in the process of selecting a DBMS for a project. We've narrowed
    down our choices to MySQL, Oracle, and PostgreSQL. One thing we're not
    sure is the speed of bulk loading. Some of the tables could contain
    tens or hundreds of millions of tuples. So, does anyone know of any
    benchmarks about this stuff? I tried searching online but couldn't find
    anything. Thank you.

    Xiaolei Guest

  2. #2

    Default Re: Bulk load performance

    On 18.12.2006 17:50, Xiaolei Li wrote: 

    There are tons of benchmarks out there but I am not sure whether you
    will find something specialized for bulk loading. Why not set up a test
    database of each brand and do your own tests?

    Kind regards

    robert
    Robert Guest

  3. #3

    Default Re: Bulk load performance

    Xiaolei Li wrote:
     

    Well I can address MySQL 5.0.27 and PostgreSQL 8.1.5 bulk loading on a freeBSD
    server. PostgreSQL is can be about twice as fast in our testing (YMMV) when
    mySQL is using InnoDB tables.

    One thing that is either good/bad about mySQL vs pgSQL loading is that pgSQL is
    extremely picky about datatypes, and the number of fields must match exactly. So
    if you are missing fields or try to cram invalid data using MySQL you might not
    get warned. At least I haven't been.

    I've used mySQL for years, so don't take this message as FUD/bashing. We run a
    large data warehouse on MySQL. It just that things that need a ton of data
    integrity and data import verification work much better for *us* on postgresql.

    I'm sure someone else has horror stories quite different from our experience.

    You may want to post at org for postgresql input on
    this topic. No idea about Oracle.

    Good luck with your search...
    --
    Walter
    Walter Guest

  4. #4

    Default Re: Bulk load performance

    Walter Vaughan <com> wrote: 
    >
    > Well I can address MySQL 5.0.27 and PostgreSQL 8.1.5 bulk loading on a freeBSD
    > server. PostgreSQL is can be about twice as fast in our testing (YMMV) when
    > mySQL is using InnoDB tables.[/ref]

    Bulk loading performance varies very much depending on how *exactly*
    it is done. The MySQL manual contains a whole chapter on that topic.

    In a nutshell:

    - turn off AUTO-COMMIT !
    - you can COMMIT in batches or just once after loading all data. I
    suggest to COMMIT every 10.000 (or so) rows.
    - if you import from SQL commands (SQL dump) - using MySQLs proprietary
    multi-value INSERT will speedup the load
    - prepared statements are good too, but can insert just one row per
    execute. Multi-value INSERT and PS are approx. equally fast.
    - LOAD DATA INFILE will be even faster, because parsing the raw file is
    faster than parsing SQL
    - keys should be deactivated for bulk loading


    A MySQL issue could be the fact that MySQL uses only one thread - that
    is: only one cpu core - per connection. So if you have multiple cpu
    cores you should split your data and load in multiple connections.
    I did so on a 16-core box and it scales quite nicely.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Question bulk emails
    By geoff0000 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: August 4th, 10:26 PM
  2. Bulk eMailing
    By Whizzzper in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: December 4th, 09:23 AM
  3. SQL Bulk Loader
    By acsdirect in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 24th, 05:12 PM
  4. BULK INSERT
    By sampsas23 in forum Coldfusion - Getting Started
    Replies: 10
    Last Post: January 12th, 01:50 PM
  5. bulk loading
    By John Miller in forum Photography
    Replies: 13
    Last Post: July 16th, 09:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139