MySQL Users Conference 2005
I learned about replication, storage engines, tuning, and MySQL 5.* features.
Notes Overall ThoughtsWe should work a migration to InnoDB into our overall plan. It is more stable, and has quicker responsiveness in high-load situations due to row level locking. There are also tools which make it easier to do hot backups and replication of data.
We should try replication again. More care can be taken to ensure that we don't write to the slave or get off track. We can be much more selective with replication as well. If we are willing to put more time into it, a dual-master configuration would probably serve us fairly well, allowing for easier failover and spread-out writes. This could be made possible by using offsets in the auto_increment fields to ensure that primary keys remained unique across the servers.
There is more we can do to tweak the behavior of the server. Both MyISAM and InnoDB tables have variables that affect their overal performance. There are still buffer and behavior settings in the server that we can pay attention to, and even the DBI has a cache-local or cache-server setting that could help us. The system could stand some tuning as well - many disks have write-cache which causes double-buffering and can trick MySQL into thinking a transaction is written when it is still in cache. Also, the removal of swap might be a good idea.
Also, we need to watch the 5.0 series develop. Their backup, clustering, and central logic (triggers and views) features could do alot for us.
An Unorganized List of "Hilights"- MyISAM - deep storage, logging, scanning only.
- InnoDB - Everything else.
- DRBD innodb clustering strategy
- Dual master replication
- MySQL's ui tools for monitoring
- perlbal sounds really neat for load-balancing, fault hiding
- Brian answered my question about memcached.
- security auditing tools - Nessus, Osiris, samhain
- monitoring tools - nagios.
- use tmpfs to put heavyily used stuff in memory
- heh - rss sites get hammered at the top of every hour
- there is a server variable, join_buffer_size, that I need to learn about
- We need to read all server variables and check what they do.
- MySQL Cluster will try to allow non-RAM fields without indexes in 2006.
- We should chat about partitioning, but nothing automatic is happening yet.
- meh views. Didn't get a warm feeling out of them i guess, though they seem to have really impressive security implications.
- use blackhole storage engine for a no-storage replicator
- Embedded server allows you to use/administer a database from within your compiled app.
- never perform calculations on columns in the where. no index is used. only compare against the stored value. Check the how-it-optimizes queries doc for info and use explain to check if this is happening.
- Don't set a large attribute (string) as primary key in innodb. Huge space-waste.
- Run slaves in read-only mode or give the login user no write privs.
- you can set maximum join size
- Joins on non-equal data types require casting and can cause indexes to be skipped.
- check out DBI's mysql_use_result and mysql_store_result - can this affect sql performance
- investigate DBI's connect_cached instead of Apache::DBI
- Check into how to create multipletablespaces with InnoDB to make sure we do this... and make sure it matures.
- not sure we should use fulltext for epost, lucene perhaps
- scrum? mysql worklog at freshmeat.net... this is how they do PM, firebox, feature track, etc. controlchaos.com
- offsets in auto-incrementing keys for multi-master clusters
- fancy livejournal script that continually checks if the slave is up-to-date, when it is, it swaps it to be the master. Runs when it's time to switch.
- turn of write-cache on the hard-disks/OS