id	summary	reporter	owner	description	type	status	priority	milestone	component	resolution	keywords	cc
150	load-balance SQL reads where possible	geofft		"Applications that are read-only (for instance, most blogs and many wikis and Tracs before login) don't need to make those reads from the primary, and it will probably greatly improve perceived performance/responsiveness to direct some or all of those requests at the secondary based on the servers' relative load. This is a tricky problem, though, and largely reduces to the ""you can't properly DBA sql.mit.edu"" problem: we run user-controlled webapps, so we can't optimize queries or client behavior, and all cleverness needs to be server-side.

In particular, the first problem is that we can't tell if a connection is going to be reads or writes, so we'll need to add a new name/IP (e.g., ""sql-ro"") for users to opt-in connections that they know will only be issuing read queries.

This means that for this to be truly productive, we'll need to do a lot of that opting in in autoinstallers, for instance, patching WordPress and MediaWiki to hit sql-ro instead of sql for non-logged-in browsing of pages. We'll especially need to do so in a way that can be backported to existing apps as part of our autoupgrade infrastructure, but without running the risk of user plugins that do need to write to SQL on each page access getting into trouble. Note that even without plugins, anonymous users can generally at least request account creation, and comment on WordPress blogs or use the state-modifying MediaWiki ""random page"" functionality.

Note that you'll need to be a little clever about handling those cases as well as cases of users logging in. Because the secondary can lag behind the primary in terms of query updates, once you switch from sql-ro to sql for a user, you generally need to stick with sql for their session, defining ""session"" in some useful way for non-logged-in users (e.g., 10 minutes of inactivity, which is what LVS uses). This is trivially true for logins and certainly true for comments and anonymous edits, since users want to see those, but you could likely get away not ""condemning"" the IP of a user who visits Special:Randompage to not using sql-ro until their session times out.

Finally, we'll need to be all sorts of paranoid about the fact that we've been doing query-based replication instead of row-based replication and see if the major auto-installed apps have user-visible nondeterministic queries. Updating random page scores is nondeterministic, but it should also, I hope, not be user-visible in a way we care about. But there may be other cases where, say, a random subset of rows was removed, and we need to be careful about those."	enhancement	new	normal		sql