0

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:\Mysql\MySQL Server 8.0\Data\2016SERVER.log"
#slow-query-log=1
#slow_query_log_file="E:\Mysql\MySQL Server 8.0\Data\2016SERVER-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:\Mysql\Data\Data\"
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:\Mysql\Data\Data\2016SERVER-bin"

# Error Logging.
log-error="E:\Mysql\Data\Data\2016SERVER.err"

# Server-Id.
server-id=2

lower_case_table_names=1

# Secure File Priv.
secure-file-priv="E:\Mysql\Uploads"
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?

3
  • 2
    Is this on a spinny disk or SSD?
    – Philᵀᴹ
    Jan 19, 2019 at 13:54
  • 2
    Please don't cross-post.
    – Rick James
    Jan 20, 2019 at 0:04
  • @vishal After applying other suggestions to your config, Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report if readily available - 7 or more days uptime is helpful F) SHOW ENGINE INNODB STATUS; G) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server tuning analysis. Jan 21, 2019 at 17:42

3 Answers 3

0

UPDATEing all 40M rows will take a long time. Period. Full stop.

It must make a copy of each row in case there is a crash; that way it can roll back the change.

Depending on the requirements, it may be useful to perform the updates in chunks. Discussion here .

What is the hurry about getting the update done? Table locking? Finishing the job? Other conflicts? Different reasons can lead to different workarounds.

"the job runs every day that truncates the table and inserts new data from other sources in that table" -- If that is a live table, there is a much better way. However, since you call it temp, maybe it is not needed?

Recommend dropping the secondary indexes, loading the table, then adding back the indexes.

OTOH, "many selects coming ahead in SP that will decrease the table scans" -- If those selects need to read all the rows, there is no advantage in having the indexes. Simply do SELECT ... ORDER BY ....

But, Sch_Code needs an index since you reach for those rows one at a time.

INT(4) -- the 4 has no meaning. An INT is always 4 bytes. If you are thinking 4 digits, then consider the 2-byte SMALLINT.

1
  • @vishal Not quite sure how your table could have 40 million rows if you use SMALLINT for the data type as suggested by Mr. James for the auto incremented column. Jan 21, 2019 at 17:28
0

If you are using HDD change it to SSD, that will make a huge difference in your IOPS and thus the updation time.

0

Suggestions for your my.cnf or my.ini [mysqld] section

# Thread Specific Values
# sort_buffer_size=2147483648
# read_buffer_size=2147483648
# read_rnd_buffer_size=1073741824
# join_buffer_size=1073741824
thread_cache_size=256  # from 600 
# bulk_insert_buffer_size=42949672

to allow DEFAULTS rather than driving your RAM required through the roof, and to lower thread_cache_size to a more reasonable limit.

For additional suggestions, view my profile, Network profile for contact info and get in touch by Skype, please.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.