There are some helpful tips in here, whether or not you use replication. In my experience, the most important thing with database performance is to minimize the time and row range over which you hold write locks -- and that's what this post is essentially about.

Quoted: Piping data through application is the best solution in many cases and is quite easy - it is trivial to write the function which will take SELECT query and the table to which store its result set and use in your application in all cases when you need this functionality.

We inadvertently provisioned a few database machines with MyISAM instead of InnoDB, and it has been a nightmare. I strongly advice against using MyISAM -- ever.

With MyISAM, we periodically get these incorrect "duplicate key" errors that don't go away until you run the lenghty "repair table" command that somehow fixes everything.

Quoted: Use MyISAM when: The data isn't too critical ( "unreliable and slow, related to table size, table repair process" )
...
Use InnoDB for tables when: The table will be big (100Mb+ - "For reliability and performance, we use InnoDB for almost everything at Wikipedia - we just can't afford the downtime implied by MyISAM use and check table for 400GB of data when we get a crash." )

Looks like a great set of tools for MySql.

Quoted: Essential command-line utilities for MySQL (formerly MySQL Toolkit)

You have to assume that there will be replication lag. Having said that, chopping up long queries into smaller ones (if using replication) is a good rule of thumb.

Quoted: And the final advice - do not assume very short lag time when planning your application. Having application which can adapt to lag time rather than break is very good idea. Especially it will be handy when you will be reaching replication capacity and will need to buy time to fix things, during which replication lag can be higher than normally.

Quoted: Today I would like to share practical example when you may use long primary key with value distribution far from sequential.
...
This table is getting much more reads than writes so transactional overhead of for writes is small price to pay for number of benefits - caching data and index in memory -so cached lookups are very fast, clustering by primary key

Quoted: Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result.

Quoted: Parallel Inserts...The most efficient approach here is not to use auto_increment but use certain partitioned sequences, for example you can have 256 growing sequences (with high byte used for sequence number) which will have 256 “hotspots” - good enough to make load parallel but still small enough to be well cached. It is also worth to note Innodb (which is storage engine which usually considered best for parallel insert/updates) has pretty much table level locks when it comes to auto_increment columns, which is however completely separate problem which can be fixed in MySQL 5.1

A good refresher on pessimistic and optimistic locking.

Quoted: The disadvantages of optimistic locking are:
* Users are not notified that someone else modified a record until they try to update it.
* Optimistic locking slows down updates.
* All applications that update a database must agree on the columns to lock on.

Not rocket science, but helpful nonetheless. We used to run into minor problems until we implemented some of the techniques (primarily the session based approach) below.

Quoted: MySQL Replication is asynchronous which causes problems if you would like to use MySQL Slave as it can contain stale data.