Re: Ease My Pain: InnoDB in a Memory-Constrained Environment

[Authorized User]

Peter Brawley Wrote:
-------------------------------------------------------
> InnoDB does so much more than MyISAM, comparing
> them is like comparing a car with a bicycle.
>
> The InnoDB errors you cite look like results of
> design and coding errors. MariaDB tried their
> best, for years, to mount a viable ACID-compliant
> alternative to InnoDB. They gave up. If Monty
> couldn't materially improve upon it, furious
> criticism of the sort you posted looks
> misdirected. So I suggest your frustration with
> InnoDB looks less about InnoDB, more about how
> you're using it.
>
> There may be tables and operations in your system
> that don't need the overhead of ACID compliance.
> There are probably design issues with your InnoDB
> tables, and/or with queries on them.
>
> So your solution, I suspect, will be a mixture of
> optimising your DDL and code, and buying enough
> server hardware to give InnoDB what it needs. If
> it's me, I start with a representative DB, analyse
> it for bottlenecks and possible optimisations (a
> possible starting point would be the optimisation
> cheatsheet described at
> https://www.artfulsoftware.com/infotree/mysqltips.php,
> but there are advanced tools out here too), and go
> from there.


The application is a canned package from an international vendor. Their product supports MSSQL and MySQL, and for the past 8 years or so their recommended engine has been InnoDB. We do not have access to the code.

The constraints are what they are. I don't have enough RAM and cannot increase it, so queries are going to hit the disk. The question then becomes, can anything else be done to ease the performance pain somewhat?