Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: insert (reset)

Speedup mysql inserts
+0 Vote Up -0Vote Down
By default, when connected to MySQL server, every command is executed as a distinct transaction. As a result, after each INSERT SQL statement MYSQL saves data to the database and commits transaction. When your application inserts hundreds and thousands of records, your database is busy in saving new records all the time and you are loosing in speed. The solution for this problem is very easy.
1 Billion Insertions – The Wait is Over!
+4 Vote Up -0Vote Down

iiBench measures the rate at which a database can insert new rows while maintaining several secondary indexes. We ran this for 1 billion rows with TokuDB and InnoDB starting last week, right after we launched TokuDB v5.2. While TokuDB completed it in 15 hours, InnoDB took 7 days.

The results are shown below. At the end of the test, TokuDB’s insertion rate remained at 17,028 inserts/second whereas InnoDB had dropped to 1,050 inserts/second. That is a difference of over 16x. Our complete set of benchmarks for TokuDB v5.2 can be found here.

  [Read more...]
Database Insights from Archimedes to the Houston Rockets
+0 Vote Up -0Vote Down

Archimedes, the first DBA

According to a recent MIT Sloan Management Review study, top performing organizations use analytics 5 times more than lower performers. That’s pretty astounding. And while we all know about the ocean/lake/waves/(your favorite water analogy) of Big Data we struggle with everyday, information is not knowledge. So how can we get insight from data? Recent articles from

  [Read more...]
MySQL Partitioning: A Flow Chart
+2 Vote Up -4Vote Down

In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

Here goes:

  • Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling
  •   [Read more...]
    Improving MySQL Insert thoughput
    +1 Vote Up -0Vote Down

    There are three simple practices that can improve general INSERT throughput. Each requires consideration on how the data is collected and what is acceptable data loss in a disaster.

    General inserting of rows can be performed as single INSERT’s for example.

    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?);
    

    While this works, there are two scalability limitations. First is the network overhead of the back and forth of each SQL statement, the second is the synchronous nature, that is your code can not continue until your INSERT is successfully completed.

    The first improvement is to use MySQL’s multi values capability with INSERT. That is you can insert multiple rows with a single INSERT statement. For example:

      [Read more...]
    Why “insert … on duplicate key update” May Be Slow, by Incurring Disk Seeks
    +0 Vote Up -0Vote Down

    In my post on June 18th, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. I previously explained why it would be better to use “replace into” or to use “insert ignore” over normal inserts. In this post, I explain why another alternative to normal inserts, “insert … on duplicate key update” is no better in MySQL, because the command incurs disk seeks.

    The reason “insert ignore” and “replace into” can be made fast with

      [Read more...]
    Making “Insert Ignore” Fast, by Avoiding Disk Seeks
    +0 Vote Up -0Vote Down

    In my post from three weeks ago, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. Towards the end of the post, I claimed that it would be better to use “replace into” or “insert ignore” over normal inserts, because the semantics of these statements do NOT require disk seeks. In my post last week, I explained how the command “replace into” can be fast with TokuDB’s fractal trees. Today, I explain how “insert ignore” can be fast, using a strategy that is very similar to what we do

      [Read more...]
    Making “Replace Into” Fast, by Avoiding Disk Seeks
    +0 Vote Up -1Vote Down

    In this post two weeks ago, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. Towards the end of the post, I claimed that it would be better to use “replace into” or “insert ignore” over normal inserts, because the semantics of these statements do NOT require disk seeks. In this post, I explain how the command “replace into” can be fast with fractal trees.

    The semantics of “replace into” are as follows:


    • if the primary (or unique) key does not exist, insert the new row
    • if the primary (or unique) key does exist, overwrite the existing row with the new row

    The slow, expensive way





      [Read more...]
    Turning on INSERT trace with dbmcli
    +0 Vote Up -0Vote Down
    DB User = dba
    DB Pass = dba
    DB Name = TEST
    command = trace_on
    argument = TRACE_INSERT

    Voila:

    $ dbmcli -u dba,dba -d TEST trace_on TRACE_INSERT
    Showing entries 1 to 9

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.