MySQL Forums
Forum List  »  Performance

Re: Performance of insert on duplicate key update
Posted by: Ravi Malghan
Date: October 27, 2014 07:44AM

Rick: Thanks again for your response.

1. In the original table with no partitions, I did not have FIRSTOCCURRENCE (timestamp field) in the primary key or the UNIQUE key. Original table shown below. I had to add the FIRSTOCCURRENCE in the primary key when I created partitions on that field.
CREATE TABLE `alarms_all_d` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SERVERSERIAL` int(16) NOT NULL,
  `SERVERNAME` varchar(64) NOT NULL,
.
.
  `FIRSTOCCURRENCE` timestamp NULL DEFAULT NULL,
.
.
  PRIMARY KEY (`id`),
  UNIQUE KEY `alarm` (`SERVERSERIAL`,`SERVERNAME`),
  KEY `TICKETNUMBER` (`TICKETNUMBER`),
  KEY `FIRSTOCCURRENCE` (`FIRSTOCCURRENCE`)
)


2. I am at 5.5.35

3. The only reason I was asking "Would checking if the row exists with the the unique key in where clause and making the decision to insert or update help? ": When I am doing the select (Step 2 of 4), I would be using FIRSTOCURRENCE in the where clause where by limiting the query's scan to fixed partitions.

4. I will remove the "KEY `FIRSTOCCURRENCE` (`FIRSTOCCURRENCE`) ".

5. The source data has SERVERNAME and SERVERSERIAL unique key requirement. SERVERNAME is a 6-64 character string and SERVERSERIAL is a number (max of 16 digits).

6. show variables
> SHOW VARIABLES LIKE 'innodb%'; 
+-------------------------------------------+------------------------+
| Variable_name                             | Value                  |
+-------------------------------------------+------------------------+
| innodb_adaptive_flushing                  | ON                     |
| innodb_adaptive_flushing_method           | estimate               |
| innodb_adaptive_hash_index                | ON                     |
| innodb_adaptive_hash_index_partitions     | 1                      |
| innodb_additional_mem_pool_size           | 16777216               |
| innodb_autoextend_increment               | 8                      |
| innodb_autoinc_lock_mode                  | 1                      |
| innodb_blocking_buffer_pool_restore       | OFF                    |
| innodb_buffer_pool_instances              | 1                      |
| innodb_buffer_pool_populate               | OFF                    |
| innodb_buffer_pool_restore_at_startup     | 0                      |
| innodb_buffer_pool_shm_checksum           | ON                     |
| innodb_buffer_pool_shm_key                | 0                      |
| innodb_buffer_pool_size                   | 42949672960            |
| innodb_change_buffering                   | all                    |
| innodb_checkpoint_age_target              | 0                      |
| innodb_checksums                          | ON                     |
| innodb_commit_concurrency                 | 0                      |
| innodb_concurrency_tickets                | 500                    |
| innodb_corrupt_table_action               | assert                 |
| innodb_data_file_path                     | ibdata1:10M:autoextend |
| innodb_data_home_dir                      |                        |
| innodb_dict_size_limit                    | 0                      |
| innodb_doublewrite                        | ON                     |
| innodb_doublewrite_file                   |                        |
| innodb_fake_changes                       | OFF                    |
| innodb_fast_checksum                      | OFF                    |
| innodb_fast_shutdown                      | 1                      |
| innodb_file_format                        | Antelope               |
| innodb_file_format_check                  | ON                     |
| innodb_file_format_max                    | Antelope               |
| innodb_file_per_table                     | ON                     |
| innodb_flush_log_at_trx_commit            | 1                      |
| innodb_flush_method                       |                        |
| innodb_flush_neighbor_pages               | area                   |
| innodb_force_load_corrupted               | OFF                    |
| innodb_force_recovery                     | 0                      |
| innodb_ibuf_accel_rate                    | 100                    |
| innodb_ibuf_active_contract               | 1                      |
| innodb_ibuf_max_size                      | 21474820096            |
| innodb_import_table_from_xtrabackup       | 0                      |
| innodb_io_capacity                        | 200                    |
| innodb_kill_idle_transaction              | 0                      |
| innodb_large_prefix                       | OFF                    |
| innodb_lazy_drop_table                    | 0                      |
| innodb_lock_wait_timeout                  | 120                    |
| innodb_locking_fake_changes               | ON                     |
| innodb_locks_unsafe_for_binlog            | OFF                    |
| innodb_log_block_size                     | 512                    |
| innodb_log_buffer_size                    | 8388608                |
| innodb_log_file_size                      | 268435456              |
| innodb_log_files_in_group                 | 3                      |
| innodb_log_group_home_dir                 | ./                     |
| innodb_max_bitmap_file_size               | 104857600              |
| innodb_max_changed_pages                  | 1000000                |
| innodb_max_dirty_pages_pct                | 90                     |
| innodb_max_purge_lag                      | 0                      |
| innodb_merge_sort_block_size              | 1048576                |
| innodb_mirrored_log_groups                | 1                      |
| innodb_old_blocks_pct                     | 37                     |
| innodb_old_blocks_time                    | 0                      |
| innodb_open_files                         | 300                    |
| innodb_page_size                          | 16384                  |
| innodb_print_all_deadlocks                | OFF                    |
| innodb_purge_batch_size                   | 20                     |
| innodb_purge_threads                      | 1                      |
| innodb_random_read_ahead                  | OFF                    |
| innodb_read_ahead                         | linear                 |
| innodb_read_ahead_threshold               | 56                     |
| innodb_read_io_threads                    | 12                     |
| innodb_recovery_stats                     | OFF                    |
| innodb_recovery_update_relay_log          | OFF                    |
| innodb_replication_delay                  | 0                      |
| innodb_rollback_on_timeout                | OFF                    |
| innodb_rollback_segments                  | 128                    |
| innodb_show_locks_held                    | 10                     |
| innodb_show_verbose_locks                 | 0                      |
| innodb_spin_wait_delay                    | 6                      |
| innodb_stats_auto_update                  | 1                      |
| innodb_stats_method                       | nulls_equal            |
| innodb_stats_on_metadata                  | ON                     |
| innodb_stats_sample_pages                 | 8                      |
| innodb_stats_update_need_lock             | 1                      |
| innodb_strict_mode                        | OFF                    |
| innodb_support_xa                         | ON                     |
| innodb_sync_spin_loops                    | 30                     |
| innodb_table_locks                        | ON                     |
| innodb_thread_concurrency                 | 16                     |
| innodb_thread_concurrency_timer_based     | OFF                    |
| innodb_thread_sleep_delay                 | 10000                  |
| innodb_track_changed_pages                | OFF                    |
| innodb_use_atomic_writes                  | OFF                    |
| innodb_use_fallocate                      | OFF                    |
| innodb_use_global_flush_log_at_trx_commit | ON                     |
| innodb_use_native_aio                     | OFF                    |
| innodb_use_stacktrace                     | OFF                    |
| innodb_use_sys_malloc                     | ON                     |
| innodb_use_sys_stats_table                | OFF                    |
| innodb_version                            | 5.5.35-MariaDB-33.0    |
| innodb_write_io_threads                   | 4                      |
+-------------------------------------------+------------------------+

7. When I started grabbing data from source application and inserting data into mysql, my script was running every 10 minutes and it would complete after about 5-6 minutes. But now (after about 2 months) more so often it takes more than 15 minutes and starts being behind.

8. innodb_file_per_table is 1. My plan is to purge data after 3 months.

9. the box has 74G RAM. innodb_buffer_pool_size is 42949672960. Checking on the RAID details.

Thanks
Ravi

Rick James Wrote:
-------------------------------------------------------
> I assume you are using at least 5.5.15?
> http://dev.mysql.com/doc/refman/5.5/en/partitionin
> g-limitations-functions.html
>
> 90 (daily for 3 months) partitions is about the
> practical limit. There are various inefficiencies
> that will not be fixed until at least 5.7. So, if
> you plan on continuing to add partitions, I
> suggest you switch to weekly (from daily).
>
> > 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?
>
> Short answer: No. Longer answer...
> As I understand it, IODKU is reasonably efficient.
> Noting that you have two UNIQUE keys (including
> the PK):
> 1. Check for duplicate PK (it won't be, since
> AUTO_INCREMENT)
> 2. Check for duplicate `alarm`
> 3. If duplicate, perform an "update"
> 4. If not duplicate, perform an "insert"
> INSERT (or INSERT IGNORE) must do steps 1,2,4
> anyway.
> By adding a SELECT for checking, you would be
> duplicating step 2. Don't bother.
>
> > int(16)
>
> The (16) provides no useful info. An INT has a
> range of +/- 2 billion; you can't store 16-digit
> numbers there.
>
> > KEY `FIRSTOCCURRENCE` (`FIRSTOCCURRENCE`)
>
> What query would use this index? (I wonder if you
> could drop it.)
>
> > `SERVERNAME` varchar(64) NOT NULL,
>
> With normalization, this field would become much
> smaller (say, 3 bytes for MEDIUMINT UNSIGNED).
> Shrinking the table would help performance in many
> ways.
>
> What is the value of innodb_buffer_pool_size?
> How much RAM is available?
> If the buffer_pool is bigger than 500M and you are
> not swapping, I would not expect it to be getting
> "slower and slower" since it can mostly run
> without any I/O.
>
> > `SERVERSERIAL` int(16) NOT NULL,
> > `SERVERNAME` varchar(64) NOT NULL,
>
> Are those redundant?? That is one-to-one?? If
> so, get rid of the name, and remove it from the
> UNIQUE key. Bulky keys are costly.
>
> > `SERVERSERIAL` int(16) NOT NULL,
>
> If that is an IP address, we need to discuss it
> further, especially in light of IPv6.
>
> If I did the math right, you have about 16 IODKUs
> per second, of which 4 create a new row. You say
> it is getting "slower and slower", but is it
> threatening to become so slow that it cannot keep
> up? Your current CREATE TABLE should be able to
> handle at least 100/second on a single, ordinary
> disk.
>
> Do you have RAID?
>
> What is the value of... Oh, heck, simply provide
> SHOW VARIABLES LIKE 'innodb%';
>
> Will you be purging old data? Do you have
> innodb_file_per_table=1?
>
> Here's another reason not to have too many
> partitions: " INSERT statements currently cannot
> be pruned. " --
> http://dev.mysql.com/doc/refman/5.5/en/partitionin
> g-pruning.html That statement is on the 5.7 page,
> too, so there is no fix currently available.
>
> I have inserted data faster 10 times as fast as
> you are doing it, and inserted into a table with
> about 120 partitions (350GB total). So, I feel
> sure that your issue can be solved. And I was
> doing it with Replication in use; that added some
> extra issues.
>
> Oh... Do you understand that the same "server"
> can show up on every day?
> UNIQUE KEY `alarm`
> (`SERVERSERIAL`,`SERVERNAME`,`FIRSTOCCURRENCE`),
> does not say UNIQUE (`SERVERSERIAL`,`SERVERNAME`),
> which may have been what you had before
> PARTITIONing.
> If you need that, then please also provide the
> various SELECTs, etc, so we can discuss other
> PARTITIONing options, etc. We have focused on
> INSERT speed, potentially at the cost of SELECT
> speed. Compromise may be needed.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of insert on duplicate key update
2465
October 27, 2014 07:44AM


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.