Brtdku

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows.

and the job runs every day that truncates the table and inserts new data from other sources in that table.

Here is the table:

 CREATE TABLE temp (

  NO int(4) NOT NULL AUTO_INCREMENT,

  DATE1 date DEFAULT NULL,

  CODE int(4) DEFAULT NULL,

  TYPE varchar(20) DEFAULT NULL,

  SCODE int(4) DEFAULT NULL,

  Nature varchar(25) DEFAULT NULL,

  UNITS decimal(19,4) DEFAULT NULL,

  BNITS decimal(19,4) DEFAULT NULL,

  DRECD double DEFAULT '0',

  FNO varchar(50) DEFAULT NULL,

  FLAG varchar(5) DEFAULT NULL,

  MBAL double DEFAULT NULL,

  PBAL double DEFAULT NULL,

  MTotalBal double DEFAULT NULL,

  PLNOT decimal(19,4) DEFAULT NULL,

  PLBOOK decimal(19,4) DEFAULT NULL,

  AGE int(4) DEFAULT NULL,

  RETABS decimal(19,4) DEFAULT NULL,

  RETAGR decimal(19,4) DEFAULT NULL,

  INDEX1 decimal(19,4) DEFAULT NULL,

  RETINDEXABS decimal(19,4) DEFAULT NULL,

  RetIndexCAGR decimal(19,4) DEFAULT NULL,

  CURRAMT decimal(19,4) DEFAULT NULL,

  GLOSSLT decimal(19,4) DEFAULT NULL,

  GLOSSST decimal(19,4) DEFAULT NULL,

  UNITSFORDIVID decimal(19,4) DEFAULT NULL,

  factor double DEFAULT NULL,

  LNav double DEFAULT '10',

  Date2 date DEFAULT NULL,

  IType int(4) DEFAULT NULL,

  Rate double DEFAULT NULL,

  CurrAmt double DEFAULT NULL,

  IndexVal double DEFAULT NULL,

  LatestIndexVal double DEFAULT NULL,

  Field int(4) DEFAULT NULL,

  C_Code int(4) DEFAULT NULL,

  B_Code int(4) DEFAULT NULL,

  Rm_Code int(4) DEFAULT NULL,

  Group_Name varchar(100) DEFAULT NULL,

  Type1 varchar(20) DEFAULT NULL,

  Type2 varchar(20) DEFAULT NULL,

  IsOnline tinyint(3) unsigned DEFAULT NULL,

  SFactor double DEFAULT NULL,

  OS_Code int(4) DEFAULT NULL,

  PRIMARY KEY (NO),

  KEY SCODE (SCODE),

  KEY C_Code (C_Code),

  KEY TYPE (TYPE),

  KEY OS_Code (OS_Code),

  KEY LNav (LNav),

  KEY IDX_1 (AGE,Type2),

  KEY DATE1 (DATE1)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

note: the reason for having this many indexes is we have many selects coming ahead in SP that will decrease the table scans.

  UPDATE Temp 

      INNER JOIN SchDate ON Temp.Sch_Code = SchDate.Sch_Code

    SET LatestNav = NavRs, NavDate = LDate   ;

-- SchDate table contain 41K record

 UPDATE Temp

    SET Age = DATEDIFF(NAVDATE, TR_DATE),

        CurrAmt = (LatestNav * Units),

        PL_Notional = (UNITS * (LatestNav - Rate)),

        Divd_Recd = 0;

here is my.cnf for reference

[client]

port=3307

max_execution_time = 0

local_infile = 1



[mysql]

no-beep



[mysqld]



port=3307

#skip-locking

#skip-name-resolve

default_authentication_plugin=mysql_native_password

wait_timeout = 300

interactive_timeout = 300

default-storage-engine=INNODB

sql-mode="NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"

max_execution_time = 0

innodb_autoinc_lock_mode = 0

group_concat_max_len=153600

skip-log-bin

log_bin_trust_function_creators = 1

#expire_logs_days = 3

local_infile = 1

skip-log-bin





### Cache/Buffer Related Parameters ###

table_open_cache=1024000

open_files_limit=2048000

key_buffer_size=2147483648



#myisam_max_sort_file_size=1G

#myisam_sort_buffer_size=512M

#myisam_repair_threads=1







# General and Slow logging.

log-output=FILE

#general-log=0

#general_log_file="E:MysqlMySQL Server 8.0Data2016SERVER.log"

#slow-query-log=1

#slow_query_log_file="E:MysqlMySQL Server 8.0Data2016SERVER-slow.log"

long_query_time=100





# Thread Specific Values

sort_buffer_size=2147483648

read_buffer_size=2147483648

read_rnd_buffer_size=1073741824

join_buffer_size=1073741824

thread_cache_size=600

bulk_insert_buffer_size=4294967296



### Mysql Directory & Tables ###

datadir="E:MysqlDataData"

tmp_table_size=17179869184



max_heap_table_size=8589934592







### Innodb Related Parameters ###

#innodb_force_recovery=3



## Innodb startup-shutdown related parameter

innodb_max_dirty_pages_pct = 0

innodb_buffer_pool_dump_pct = 100

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1



innodb_change_buffer_max_size = 50

innodb_file_per_table = 1

innodb_log_file_size = 10G

innodb_log_buffer_size = 4294967295

innodb_log_files_in_group = 10

#innodb_buffer_pool_chunk_size = 1024M

innodb_buffer_pool_size = 96636764160

###innodb_buffer_pool_size=90G

innodb_buffer_pool_instances = 50

#innodb_flush_method=O_DIRECT

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 100

innodb_write_io_threads = 64

innodb_read_io_threads = 64



# Binary Logging.

#log-bin="E:MysqlDataData2016SERVER-bin"



# Error Logging.

log-error="E:MysqlDataData2016SERVER.err"



# Server-Id.

server-id=2



lower_case_table_names=1



# Secure File Priv.

secure-file-priv="E:MysqlUploads"

max_connections=500

#innodb_thread_concurrency=9



innodb_thread_concurrency=0

innodb_adaptive_max_sleep_delay=150000

innodb_autoextend_increment = 2048

#innodb_concurrency_tickets=5000

#innodb_old_blocks_time=1000

innodb_open_files=1500

innodb_stats_on_metadata=0

innodb_checksum_algorithm=0

#back_log=80

#flush_time=0

max_allowed_packet=512M

table_definition_cache=1400

binlog_row_event_max_size=8K

#sync_master_info=10000

#sync_relay_log=10000

#sync_relay_log_info=10000

loose_mysqlx_port=33060



innodb_flush_method = unbuffered

###innodb_flush_method = async_unbuffered

default-time-zone = +05:30

tmpdir = "C:/TEMP"

innodb_io_capacity = 1000

plugin_dir = "C:/Program Files/MySQL/MySQL Server 8/lib/plugin"

innodb_log_write_ahead_size = 16394

mysqlx_max_connections = 500

innodb_random_read_ahead = 1

the first update takes 30 to 35 min and the second update takes 15 min.

here is explain plan of update 1

1   SIMPLE  SchDate     index   PRIMARY,Sch_Code,IDX_1  Sch_Code    4       39064   100 Using index

1   SIMPLE  temp        ref SCH_Code    SCH_Code    9   SchDate.Sch_Code    1   100 Using index condition

I am running this query on windows 10.
Is there any way to increase the speed of the UPDATE query?
any configure related changes would be helpful?

table_open_cache=1024000

NO! That is tables, not bytes. Change it to 2000.

key_buffer_size=2147483648

Assuming you are using InnoDB, not MyISAM:

key_buffer_size = 50M

innodb_buffer_pool_size is fine at 96G (for 128GB of RAM)

This is close to useless, change to 5:

long_query_time=100

And...

# Thread Specific Values

sort_buffer_size=2147483648

read_buffer_size=2147483648

read_rnd_buffer_size=1073741824

join_buffer_size=1073741824

bulk_insert_buffer_size=4294967296

tmp_table_size=17179869184

max_heap_table_size=8589934592

Read that comment! Each connection may allocate those sizes! You will run out of RAM. Swapping is sloooow or will crash! Even if you have a lot of RAM, those numbers are excessive.

innodb_flush_method = unbuffered

The manual says:

unbuffered: ... is used for internal performance testing and is currently unsupported. Use at your own risk.

innodb_random_read_ahead = 1

From the manual:

Because this feature can improve performance in some cases and reduce performance in others, before relying on this setting, benchmark both with and without the setting enabled.

Bottom line: Back out all your configuration changes except the buffer_pool.