My SQL Performane ISSue


In According to reduce the time taken to delete or update a MYSQL table,
How can i configure the memory structure of the My.cnf file or is there any other way.
When i am running batch actions the process is becoming stalled.

I have a 12Gram and Xeon2.2HTNahelm 8Cpus running, still process is getting stalled to remove 5mb file that i uploaded.
I am not getting where is the problem.

Some of the key variables declared are
binlog_cache_size               32768

bulk_insert_buffer_size       8388608

delayed_insert_limit               100

delayed_insert_timeout               300

delayed_queue_size               1000

innodb_additional_mem_pool_size 1048576

innodb_autoextend_increment       8

innodb_buffer_pool_awe_mem_mb       0

innodb_buffer_pool_size       6442450944

innodb_checksums       ON

innodb_commit_concurrency       0

innodb_concurrency_tickets       500

innodb_data_file_path               ibdata1:10M:autoextend


innodb_adaptive_hash_index       ON

innodb_doublewrite               ON

innodb_log_buffer_size               1048576
innodb_log_file_size               5242880

innodb_thread_concurrency       8
innodb_thread_sleep_delay       10000

nteractive_timeout                28800
join_buffer_size                131072
key_buffer_size                1073741824
key_cache_age_threshold        300
key_cache_block_size                1024
key_cache_division_limit        100

max_allowed_packet                1048576
max_binlog_cache_size            18446744073709547520
max_binlog_size                1073741824
max_connect_errors                10
max_connections                100
max_delayed_threads                20
max_error_count                64
max_heap_table_size                16777216
max_insert_delayed_threads        20
max_join_size                        18446744073709551615
max_length_for_sort_data        1024
max_prepared_stmt_count        16382
max_relay_log_size                0
max_seeks_for_key                18446744073709551615
max_sort_length                1024
max_sp_recursion_depth                0
max_tmp_tables                        32
max_user_connections                0
max_write_lock_count                18446744073709551615
multi_range_count                256
myisam_data_pointer_size        6
myisam_max_sort_file_size        9223372036853727232
myisam_recover_options                OFF
myisam_repair_threads                1
myisam_sort_buffer_size       8388608
myisam_stats_method              nulls_unequal
net_buffer_length               16384
net_read_timeout              30

Please let  me know this time if I can improve the perfromance or is there any issue withe the variable declarations.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

max_allowed_packet is set to 1MB only.
mySQL should throw an error in case packet size is too big. Try increasing this value to like 50MB.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TrambakAuthor Commented:
HI thank you so much.
The performance has improved but need to check few tweaks also.

Again thank you so much.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.