MySQL Forums
Forum List  »  Performance

Re: Performance of insert on duplicate key update
Posted by: Ravi Malghan
Date: October 24, 2014 11:54AM

Rick: thanks for your response. Below is my table with the relevant info. If I don't use the partitioning, the single file which holds the data is about 27G (3 months of data). When partitioned using the timestamp (daily partition), each day is about 500M.

I am not batching. I am doing one "insert on duplicate key update" at a time. Would checking if the row exists with the the unique key in where clause and making the decision to insert or update help?

Let me know what you think?
CREATE TABLE `alarms_all_d` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SERVERSERIAL` int(16) NOT NULL,
  `SERVERNAME` varchar(64) NOT NULL,
.
.
  `FIRSTOCCURRENCE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
.
.
  PRIMARY KEY (`id`,`FIRSTOCCURRENCE`),
  UNIQUE KEY `alarm` (`SERVERSERIAL`,`SERVERNAME`,`FIRSTOCCURRENCE`),
  KEY `TICKETNUMBER` (`TICKETNUMBER`),
  KEY `FIRSTOCCURRENCE` (`FIRSTOCCURRENCE`)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(FIRSTOCCURRENCE))
(
PARTITION p20140630 values less than (UNIX_TIMESTAMP('2014-07-01 00:00:00')),
PARTITION p20140701 values less than (UNIX_TIMESTAMP('2014-07-02 00:00:00')),
..
)

Rick James Wrote:
-------------------------------------------------------
> > As table grows these inserts are becoming slower
> and slower.
>
> Sounds like the secondary indexes are the problem.
> Let's see
> SHOW CREATE TABLE
>
> > I added partitioning to the table by a timestamp
> field.
>
> That won't necessarily help.
>
> > This version doesn't support explain plan for
> inserts and updates.
>
> Probably would not say much exciting.
>
> Are you "batching" the rows? Or doing one INSERT
> at a time? Are they in a single transaction?
>
> > Is there a way to limit the number of partitions
> the query scans for updates?
>
> Unfortunately, it opens all the partitions before
> deciding which partition to use. For this reason,
> having a lot of partitions can actually slow
> things down. Which version are you using? 5.5.28
> had a fix in this area.
>
> > In other words is there a way to include the
> timestamp field in my insert on duplicate key
> update query?
>
> I hope your PRIMARY KEY is not just a timestamp.
> Such a value is not very unique.
>
> Here is how to set the timestamp instead of
> letting it default.
> INSERT INTO tbl (ts, ...) VALUES (NOW(), ...);
>
> However, if you are batching, NOW() will have the
> _same_ value for all rows:
> INSERT INTO tbl (ts, ...) VALUES (NOW(), ...),
> (NOW(), ...), (NOW(), ...), ...;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of insert on duplicate key update
3119
October 24, 2014 11:54AM


Sorry, you can't reply to this topic. It has been closed.

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.