Member Avatar for jkon

I am posting it here because I would really like to read other opinions.
I am creating a module for a framework that anyone could install in its system and behave the similar (almost) way. I am starting with that because it is important that I don't control system settings nor could advise anyone for using the module to alter their my.cnf or my.ini .

Recently I have start suspecting that something is terribly wrong about InnoDB. I made the fallowing test in 3 servers (I don't go further analyzing servers because the results where almost the same and the servers very different, if anyone like I could).

I created two tables with the same structure one in InnoDB “persons1” and one in MyISAM “persons2” . Here is the SHOW CREATE TABLE persons1 (the same is for persons2 in MyISAM

CREATE TABLE persons1 (
id int(10) NOT NULL AUTO_INCREMENT,
firstName varchar(255) COLLATE utf8_bin NOT NULL,
lastName varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

And now it is time to write some data to it , and the socking result was that in every server INSERT in InnoDB was around 1000 times slower than in MyISAM. (I am using prepared statements). That wasn't the case only to the first INSERT but in any that fallowed (a bit less time in both engines).

In fact inserting just one row in InnoDB was around 0.1 seconds !!! ( around 0.0001 for MyISAM). Meaning that if with InndoDB I have 10 very simple inserts in my program than the user should wait a whole second more.

Of course that is not an option , so I am almost decided to abandon InnoDB. I liked many of the features but with that performance it has a serious issue. Am I missing something here ?

  • 2 Contributors
  • 1 Reply
  • 266 Views
  • 8 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Brief summary: reports that InnoDB INSERTs were orders of magnitude slower than MyISAM across several servers, and correctly points out that default InnoDB settings can be suboptimal. The behaviour described is common when InnoDB is used with one-row commits and default durability settings — it is not a sign that InnoDB is fundamentally broken, but that disk-sync and transaction semantics are doing exactly what they were designed to do.

Immediate diagnostic steps and quick fixes:

  • Verify whether each INSERT runs in its own transaction (autocommit on). Single-row commits cause an fsync per commit on many default configurations.

  • Test grouping multiple INSERTs in one transaction to compare throughput. Example:

    START TRANSACTION;
INSERT INTO table (...) VALUES (...);
INSERT INTO table (...) VALUES (...);
COMMIT;

  • Try multi-row INSERT statements where possible (INSERT ... VALUES (...), (...), (...)).

  • Confirm whether the client library uses server-side prepared statements or emulates them; emulation can add round-trips.

If server tuning is allowed (or for benchmarking):

  • Increase innodb_buffer_pool_size so hot data and indexes fit in memory.
  • Make innodb_log_file_size and innodb_log_buffer_size large enough to avoid frequent log flushes.
  • Review innodb_flush_log_at_trx_commit (1 = safest; 2 or 0 = faster but accepts some durability risk) and sync_binlog, understanding the crash-safety trade-off.
  • Check innodb_flush_method and the underlying storage (HDD vs SSD) because fsync costs vary a lot.

When server configuration cannot be changed:

  • Implement application-side batching/queueing so the module emits bulk writes rather than many isolated commits.
  • Reserve MyISAM (or other non-transactional storage) only for truly non-critical, append-only tables (logs/analytics) and accept the lack of transactions/crash-safety.

Bottom line: abandoning InnoDB wholesale is usually premature. Measure with grouped transactions and a simple benchmark first, then choose between careful tuning, application batching, or selective use of MyISAM based on the module’s durability and concurrency requirements.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.