[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

My SQL Performane ISSue

Hi

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_data_home_dir       

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.

Regards
Trambak

0
Trambak
Asked:
Trambak
1 Solution
 
FerrostiCommented:
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.
0
 
TrambakAuthor Commented:
HI thank you so much.
The performance has improved but need to check few tweaks also.

Again thank you so much.

Regards
trambak
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now