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.