Performance tuning for MySQL database (read only)

Question

We are developing a reporting tool that is connected to MySQL database of size 500 GB size. Reporting tool will be fetching the millions of rows from the database. The tool is used only for reports.

1. It won't be updating, inserting or deleting any data.
2. There won't be any join we need to use, at a time tool will be interacting with a single table exclusively.
3. Tool will use only "order by", "where", "limit (for paging)" clause on the the database.
4. All the table will be having a column "rowno" as a primary key. There is no FK relationship between the tables.

Edit:

MySQL version: '5.7.16-log'

Database Engine: MyISAM

Tool Used: MySQL Workbech

What's best way of performance tuning for this database (like using specific row_format etc.)?

Answer 1

MyISAM is going to be deprecated (Bill Karwin is a big hitter on stackexchange and previously worked for Percona!) - you should change MyISAM to InnoDB NOW. You can do that by following the instructions here or here

AIUI, even the system tables in MySQL have4/are been/being moved to InnoDB, see here and here. The latter reference appears to indicate that most system tables have moved to InnoDB in the upcoming 8 release.

You can read some of the pros/cons of MyISAM/InnoDB here.

Even if you're not using the capabilities of the InnoDB engine now, you may need to use them in the future.

To answer the question, you have two performance tuning options:

• you can set your InnoDB TRANSACTIONs to READ ONLY from here,

or you can

• set the entire server to be READ ONLY as per here.

You may want to test these, but it is my belief that any small performance penalty is worth paying for the advantages that the InnoDB engine brings!

It won't be updating, inserting or deleting any data.

This is good for performance - there's no need for the engine to maintain linked lists of pointers to new records and/or other transactions in this case. You should set your transactions to read only as indicated above.

There won't be any join we need to use, at a time tool will be
interacting with a single table exclusively.

Hmmm... a database without joins - always been a puzzling concept to me. You do realise that the optimiser can make better decisions about which tables to process in which order if there are explicit joins between the tables - those who argue that FOREIGN KEYs incur a performance penalty don't understand RDBMSs.

Tool will use only "order by", "where", "limit (for paging)" clause
on the the database.

And GROUP BY surely? Otherwise, why not simply use a spreadsheet? In any case, presenting a human user with thousands/millions of lines of data (or in this case apparently, records) is pretty pointless - humans will just scan and skip through pages of numbers.

All the table will be having a column "rowno" as a primary key. There is no FK relationship between the tables.

See above about FOREGIN KEYs. No problem with having a simple integer PRIMARY KEY as your surrogate KEY.

Answer 2

Don't use LIMIT for pagination, instead remember where you left off.

Reporting on a 500GB table -- sounds like "Data Warehousing". Plus no updates/deleted -- a prime candidate for Summary Tables -- That will give you more performance boost than anything else. Consider summarizing, then tossing the raw data.

No JOINs? Sounds like the Fact table will be cluttered with long strings. This, if nothing else, is a good reason to do some Normalization. That 500GB might shrink to 200GB, thereby speeding up many tasks.

"Rowno" -- think AUTO_INCREMENT. Or, think about whether there is a "natural" PRIMARY KEY. We can debate this further when I see the schema. (I find that "natural" wins out 2/3 of the time.)

Don't argue, just use InnoDB. And set innodb_buffer_pool_size to about 70% of available RAM. Nothing else is likely to need changing from the defaults.