Diamond Notes

Scalability: the ability for a server to grow up (and technically down…but they don’t consider this)

Performs: response time is decent (ie not 30 seconds), easy to measure in production. Make sure there is enough system capacity (system loaded with degrading in response time and failing). With system operations you need to be able to have reasonable times for activities (24 hr backup of 2 terabytes is not acceptable).

Application Growth Parameters

• load - growing from 100 q/sec to 1,000 q/sec
• database size growing from 10 GB to 100 GB
• data distribution - going from 10 friends on average to 250 average
• launching new features - adding full text search can add a heavy load

As applicatons grow all the parameters tend to grow at once. This includeds more complex queries on larger databases. To maintain performance you can optimize the application: schema, queries, caching, sharding and replication.

What is a platform?

• hardware
• OS
• MySQL

Testing done through Micro Benchmarks (simple operations designed to test some particular aspect of behavior). Peter didn’t really explain this enough for me…other than a general idea not sure about it.

Issue with Innodb

• large tables are hard to deal
• backup - physical backup is a must
• can’t move separate tables between servers
• no repair functionality (corruption means dump and restore)
• ALTER TABLE is very slow (MM replication helps)
• table maint with OPTIMIZE TABLE

Online Index Creation in InnoDB

plugin just announced today by Innodb

Innodb can now build indexes without rebuilding whole table (index build done by sort which is much faster).  It is ten times faster loading data and adding indexes (separately).

Dealing with Many Tables in Innodb

many people avoid the problem of large table by creating many small tables.

using innodb_file_per_table=1 will resolve this problem.

“Warmup” is still a problem.  In 5.0 only one table can be opened at a time.  stats update on open makes it quite slow.

Large Buffer Pool Size

In general, the more memory you can get for buffer pool the better it is

Warmup will take a long time.  With a 32GB buffer pool, it can take an hour to fill at 600 pages/sec fill rate.  SHOW STATUS and SHOW INNODB STATUS gets expensive because of global locking

In addition, shutdown will take longer with large buffer pool.

You can set innodb_max_dirty_pages_pct=0 in advance and it will help with the flushing of the buffer pool.

Peter discussed benchmarking 5.0 and 5.1.  It appears that 5.1 has some regression issues!  They tested up to 64 threads in a test.  In all cases 5.1 simply did not perform as well.  Quad-core chips did not perform as well as dual core.

Looks to me like 5.1 has bit to go in respect to Innodb.  However, I don’t believe they had the latest patches.  He kept mentioning Mark Callhan??