317295 - [Enh] Specify read-only database connection for SELECT queries

Status: VERIFIED WORKSFORME

(Bugzilla :: Database, enhancement)

Description

[Denis Roy]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050922 Fedora/1.0.7-1.1.fc3 Firefox/1.0.7
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050922 Fedora/1.0.7-1.1.fc3 Firefox/1.0.7

It would be nice to have an optional database connection to have SELECT queries processed by a slave MySQL database.  Some SELECTs take a long time (1+ minute) to execute and block INSERT and UPDATE queries from executing.

We only have 116,000 bugs in the table, and some basic searches can lock the tables for up to 2 minutes.  An added bonus to this would be a definite performance increase when reads and writes are concurrent.

Our master database server is an IBM POWER5 server w/ 4 ppc processors and 16 GB of RAM, and our slave is identical.

Below is a piece of 'show processlist' which illustrates a locked WRITE because of a long select:

| 34242232 | bugs    | node2.eclipse.org:50085 | bugs    | Query          | 2      | Copying to tmp table                                           | SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.delta_t |
| 34242237 | bugs    | node4.eclipse.org:50089 | bugs    | Query          | 1      | Locked                                                         | LOCK TABLE profiles WRITE, user_group_map WRITE, group_group_map READ, groups READ                   |


Reproducible: Always

Steps to Reproduce:
1. load up tables with 116,000+ bugs
2. run a complex search
3. try to update a bug while Bugzilla ponders away
4. run 'show processlist' in the mysql console to see numerous Locked queries

Comment 1

[Dave Miller [:justdave]]

Bugzilla already does this, and has since version 2.8 or so.  Look for the shadowdb related parameters on editparams.cgi.

Comment 2

[Olav Vitters]

PS: This wasn't working on 2.20, see bug 313695 (fixed on 2.20 branch).

Comment 3

[Denis Roy]

Well I'll be darned, it works. I was looking in localconfig for such a setting. Sorry for the oversight.

D.