0

Which InnoDB parameters should I tune if disk I/O is slow?

Please give your suggestions.

I have another question regarding innodb_log_file :- What happen if innodb_log_file become full ? so what is mysql next action on this situation ?

Improve this question
4
  • Your question is a bit broad and short on specifics! What do you mean by slow? What is your hardware (CPU, RAM, HDD/SSD) and what queries are you running? How much data is in the relevant tables? What are the timings of problematic queries (give the query as formatted text as well as the output of SHOW CREATE TABLE foo\G
    – Vérace
    Jun 13, 2020 at 19:42
  • 1
    If the disk is busy due to a slow query, let's work on the query! It may be as simple as adding a composite index. There is a lot more potential gain this way than by tweaking hardware.
    – Rick James
    Jun 14, 2020 at 5:14
  • Please update your question by telling us the results of SELECT @@VERSION; Also, how much RAM, Cores available and do you have magnetic, SSD or NVME data storage?
    – Wilson Hauck
    Jun 14, 2020 at 19:55
  • @udayrajgupta Please put your SECOND question on the system as a new question. Someone will answer you.
    – Wilson Hauck
    Jun 18, 2020 at 12:28

1 Answer 1

Reset to default
0

That depends on why your disk I/O is slow and how many safeties you are willing to disable. Some examples:

sync_binlog=0 # if you use binlogs - you could lose up to typically 5 seconds of transactions that were written to the binlog

innodb_doublewrite=0 # you will end up with corrupted data if a page written to the transaction log is torn due to a crash or power failure

innodb_flush_logs_at_trx_commit=0 # you will lose up to typically 5 seconds of transactions that were written to InnoDB.

innodb_io_capacity and innodb_io_capacity_max - make sure these are set appropriately for your disk speed. Defaults are 200 and 2000 respectively, which is probably too high for regular SATA HDDs and certainly too low for SSDs, especially high end NVMe SSDs.

Improve this answer

Your Answer

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

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