How database size affects performance: Theory vs reality

Question

There is a lot out there that says database size should not affect performance to any great degree. As long as indexes on tables fit in memory the database should remain performant.

However what is the reality? If the database architecture isn't the best, indexes don't fit in memory, and there is potentially a lot of redundant data are there significant gains to be made simply by deleting redundant data? I estimate that 60-80% of data in my database could be deleted.

I believe that reducing the database size and upping the RAM so that indexes can fit in memory would give a significant performance increase which would give some breathing room for a few months to rearchitect the system.

Are there also other factors such as IO, fragmentation, working dataset etc that affect performance based on database size?

Answer 1

It depends entirely on what you are doing with the data.

For basic insert/update/delete transactions that affect just a few rows, then the growth in data size is probably not a big consideration. The database will use in-memory indexes to access the correct page. You get more cache misses when the tables no longer fit into memory. However, the overhead might be slight -- depending on the database, database configurations, and hardware configurations.

If you are doing queries that require full-table scans, then your performance is going to grow linearly or worse with the data size. Indexes can actually make the situation worse, by randomizing page accesses, which then pretty much guarantee cache misses.

An alternative to more memory is improved disk speed -- solid state disk can provide tremendous improvement.

Just having more data is unlikely to affect performance unless the tables are used in queries. Is the data redundant within a table or across tables? Having large tables that never get used is messy, but has minimal impact on performance. It is imaginable that if you have zillions of unnecessary tables, then then compiling queries could start to take more time.

Answer 2

The number one tuning rule AMM (Add More Memory) is a simple one. It is also one that is very costly and at the end one that is not effective when there are problems in selectivity. Even if a database fits completely in memory, the performance of the application can be bad. In a worst case scenario because of locking and latching during very a-selective SQL executions. Those should be fixed first. One reason is concurrency which is like hitting - and holding - the breaks if every SQL accesses all data in a table every time.

Make sure no SQL accesses more rows than needed. That is giving the most effective way to keep performance good. A normal database knows how to handle io and does some form of caching of most used data.

If your application has already minimized all possible accesses, and you already use the fastest disk systems, consider using real flash memory arrays. They can crank-up performance an other level.

Answer 3

Please refer these posts:

• How big can a MySQL database get before performance starts to degrade
• Why MySQL could be slow with large tables ?

Hints to make your Data as Small as Possible:

Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster.

MySQL supports many different storage engines (table types) and row formats. For each table, you can decide which storage and indexing method to use. Choosing the proper table format for your application may give you a big performance gain.

You can get better performance for a table and minimize storage space by using the techniques listed here: - Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

• Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. If you really need NULL in your application, you should definitely use it. Just avoid having it on all columns by default.

• For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used.

• InnoDB tables use a compact storage format. In versions of MySQL earlier than 5.0.3, InnoDB rows contain some redundant information, such as the number of columns and the length of each column, even for fixed-size columns. By default, tables are created in the compact format (ROW_FORMAT=COMPACT).The presence of the compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.

The compact InnoDB format also changes how CHAR columns containing UTF-8 data are stored. With ROW_FORMAT=REDUNDANT, a UTF-8 CHAR(N) occupies 3 × N bytes, given that the maximum length of a UTF-8 encoded character is three bytes. Many languages can be written primarily using single-byte UTF-8 characters, so a fixed storage length often wastes space. With ROW_FORMAT=COMPACT format, InnoDB allocates a variable amount of storage in the range from N to 3 × N bytes for these columns by stripping trailing spaces if necessary. The minimum storage length is kept as N bytes to facilitate in-place updates in typical cases.

• The primary index of a table should be as short as possible. This makes identification of each row easy and efficient

• Create only the indexes that you really need. Indexes are good for retrieval but bad when you need to store data quickly. If you access a table mostly by searching on a combination of columns, create an index on them. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates to obtain better compression of the index.

• In some circumstances, it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic-format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table.