MySQL insert problem

[sanket044]

Hello,

I am right now having a dedicated server and when i try to execute a INSERT query in table of 2 Lakh rows it takes about 0.02 seconds. so i am having a website where i need to update all 2 Lakh rows every 15 mins. but it sims impossible for this rate of execution. Here are my innodb settings, please help. I read somewhere that 5000 queries per second is normal speed. and in my setting it will execute only 50 queries. Also tell me your suggetions. Thanks a ton.

have_innodb YES
ignore_builtin_innodb OFF
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 8388608
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 2147483648
innodb_change_buffering all
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
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_force_load_corrupted OFF
innodb_force_recovery 0
innodb_io_capacity 200
innodb_large_prefix OFF
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_buffer_size 8388608
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 75
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_old_blocks_pct 37
innodb_old_blocks_time 0
innodb_open_files 300
innodb_print_all_deadlocks OFF
innodb_purge_batch_size 20
innodb_purge_threads 0
innodb_random_read_ahead OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads 4
innodb_replication_delay 0
innodb_rollback_on_timeout OFF
innodb_rollback_segments 128
innodb_spin_wait_delay 6
innodb_stats_method nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages 8
innodb_strict_mode OFF
innodb_support_xa ON
innodb_sync_spin_loops 30
innodb_table_locks ON
innodb_thread_concurrency 0
innodb_thread_sleep_delay 10000
innodb_use_native_aio ON
innodb_use_sys_malloc ON
innodb_version 5.5.42
innodb_write_io_threads 4

[diafol]

2 Lakh being 200,000?

Are you able to group these so that you run fewer queries?

Can you give an example of how these reccords are updated? For example increments? If updates are just simple increments, then you could probably do the updates in one query. That's the best case scenario of course. So give us an idea.

[sanket044]

@diafol

Thanx for reply. Actully its not updated, i am deleting all rows first and inserting all new records.

[diafol]

THis seems a bit extreme, heh heh. Can't you do this in a second or two? I you're inserting, you said that you can insert 200,000 rows in 0.02 seconds. WOW! SO what's the problem? Inserting means you can do it with ONE query.

Are you going to give an example of a query?

[sanket044]

@diafol

No bro, it takes 0.02 second to insert one row, i am having 200,000 in table.

[diafol]

You can insert ALL rows in one query. See https://www.daniweb.com/web-development/php/code/495371/insert-multiple-records-with-pdo-prepared-statement for an example.

[diafol]

Also deleting 200,000 records - could be slow. ALso if using DELETE FROM the autoincrementer is not reset, so if creating 200K records every 15 minutes, your key values are going to get huge (if using them).
Using TRUNCATE TABLE may be useful. It drops and re-creates the table (v.fast) if there are no constrained FKs related to it, otherwise it deletes records one by one (slow). Either way, the autoincrementer will reset to 0.

[pritaeas]

What is the reason that you use DELETE/INSERT instead of UPDATE, are the rows that different?

[diafol]

What is the reason that you use DELETE/INSERT instead of UPDATE, are the rows that different?

I've asked to see the query twice, but the OP, for whatever reason has not wanted to show it. I guess we gotta wait.

@sanjet: If you could show a typical row, (before delete) and the corresponding record after new insert?

[sanket044]

@diafol and @pritaeas

As i said the data changes rapidly so i cant update the data, their are most the cases that i dont need old rows here. as i have signed NDA i cant show the queries or any code so sorry for that. but @diafol your both tips were helpfull now i have used multiinsert and i can insert 250,000 rows per second. and its fast for me enought. thank you so much for help.

I also want to ask if i need to change anything in server settings ??

[pritaeas]

Am not sure what you do with your records after inserting, but if you need speed, make sure you remove any foreign keys or indexes. Check if you even need a primary key. The less constraints you have, the faster it will go.

[sanket044]

@pritaeas

thanx for reply, but i dont use any FK or PK, other than autoincreement column, and i also reset it whenever it goes high.

[pritaeas]

other than autoincreement column

That's why I asked what you do with the data, perhaps even the PK is not necessary.