3
require_once("docutil.php");
5
page_head("Configuring MySQL for BOINC");
8
A fast-and-easy script that makes recommendations
9
for tuning server variables is
10
<a href=http://www.day32.com/MySQL/>here</a>.
13
The mysqlreport utility gives a variety of information
14
that can be useful for adjusting buffer pool allocations.
16
<a href=http://www.hackmysql.com/mysqlreport>http://www.hackmysql.com/mysqlreport</a>.
21
The note discusses how MySQL may be configured for BOINC Projects.
22
BOINC-based projects have varying DB traffic characteristics
23
and this note relates to our experiences with SETI@home,
24
so it may not be entirely applicable to all projects.
25
SETI@home currently uses MySQL 4.0+
26
and we expect to upgrade to 4.1 shortly and 5.0 later.
27
Our project uses only a single instantiation of the MySQL code file
28
and this note does not discuss the operation of multiple instances of MySQL
33
All MySQL products and documentation are available at http://www.mysql.com/.
34
Our experience has been of using MySQL with Sun Solaris and Linux OSes.
35
MySQL on MS Windows or Mac OS X may be somewhat different.
38
<h2>MySQL DB Engines (or Table Types)</h2>
41
The MySQL software comprises a number of DB engines.
42
For SETI@home DB only 2 are used, Innodb and MyISAM.
43
They have different features and are used according to
44
the performance requirements of the project.
45
One can use all of the different engines (or table types)
46
or just a single one in a MySQL DB,
47
just depending on the query activity against each table in the project
51
MySQL software is available in 32 bit and 64 bit binaries for downloading.
52
Using 32 bit MySQL requires that all RAM resources
53
that are assigned to the various DB engines,
54
must sum to no more than 2GB of RAM.
55
There is no such limitation with 64 bit MySQL and large amounts
56
of RAM help Innodb performance.
60
The MyISAM engine requires the least amount of computer resources
61
can be used where there is a low DB activity requirement.
62
For example with query rates lower that 5/sec this table type may be adequate.
63
Also if one does not have a dedicated DB server this may be a good choice
64
for all the tables since it consumes much less computer resources.
65
It has the advantages of allowing long text indices against
66
tables which Innodb does not allow.
69
MyISAM creates an OS file for each table and one for all the
70
indices related to the specific table (and another for the table format info).
73
On the other hand it tends to suffer from consistency glitches
74
so will occasionally trash indices and will need rebuilding.
75
In commercial banking environments it would not be a good idea
76
to keep account balances in this table type since there is
77
no guarantee that transactions even if completed and printed will
79
MyISAM updates its tables synchronously and uses memory locks to avoid data
81
In SETI@home, MyISAM is used for the forum tables and logging
82
that have relatively low query rates.
86
The Innodb engine is used for most of the tables in SETI@home project.
87
It processes multiple simultaneous queries against its tables.
88
It is a versioning DB engine that holds an image of the table
89
at the start of a query and maintains it until that query is completed.
90
Other updates are allowed during queries and in general for short queries
92
Innodb uses the Innodb log to store changes to its tables until
93
it flushes these changes to the actual tables at syncpoints.
94
If for any reason there is a server event that causes a system failure,
95
Innodb will use this log to recover the Innodb tables to consistency.
96
There are a minimum of 2 transaction log files
97
with a total maximum size of 4GB.
100
Innodb tables/indices are usually stored in large OS physical files
101
and the tables and indices are managed internally within these OS/Innodb files.
102
It is important that these files are located on high performance devices.
103
The transaction log files should be located on independent high performance
104
media (away from the Innodb files) for sustained high transaction rates.
105
At DB shutdown all modified buffers have to be flushed into the transaction
106
logs before MySQL goes away, so slow performance drives for
107
the transaction log could delay shutdown for over 30 minutes
108
when there are a large number of .modified buffers. to be flushed.
111
<h2>Physical Requirements</h2>
114
Assuming the need for more than 70,000 users and 250K hosts
115
with an average workunit turnaround of about 10 hours
116
then one should get an Opteron dual-core class CPU.
117
It is a 64-bit architecture and can access up to 32GB of RAM.
118
It is qualified to run Solaris, Linux and Windows XP 64-bit (?) .
119
There are 64-bit versions of MySQL for Linux and Solaris OSes.
122
This is by no means the only hardware that will work with BOINC/MySQL,
123
however SETI@home uses this type of hardware and serves over 350K user
125
If your requirements are smaller,
126
then many 32bit hardware and OSes may be perfectly adequate.
130
The RAM requirement is related to the number of active subscribers
131
who are expected to volunteer for the project and the number
132
of threads that will be connected to the MySQL server.
133
We recommend a minimum of 2GB dedicated to MySQL for about 20,000 .
134
30,000 volunteers growing to servers with much larger RAM sizes,
135
say 6GB for up to 450K volunteers.
136
This is also related to disk IO rates that are available for use
137
by the data and log files.
138
For example Innodb will store modified data in RAM until a syncpoint
139
at which time data is flushed to disk;
140
during this time update transactions are paused until the flush is completed.
141
If there is large RAM and slow disk IO,
142
the pause can last for several minutes.
143
A similar delay can be noted when attempting to shutdown the
144
project database when all the modified buffers must be flushed
145
to disk before MySQL will shutdown, this delay could be 30 minutes or more.
147
<h3>IO Subsystem</h3>
149
Assuming a high performance requirement of more than 200 DB queries/sec
150
there should be separate controllers for for the data and the log files.
151
In the case of Innodb log files it is very important that
152
they are on very reliable media for example mirrored (RAID 1) drives.
153
The tables and indices require wide band or high throughput disk configuration
157
Some consideration should be given to having online spare
158
disk drives since this will help to minimize down times in case of failures.
160
<h2>Normal Operations</h2>
163
For normal operations or production there are some considerations that
164
should be addressed to enable the project personnel to
165
provide reliable service.
166
For example there should be a reliable power supply with UPS protection
167
to avoid uncontrolled shutdowns.
168
The temperature of the hardware operations room should be regulated
169
to hardware specifications to avoid premature aging/failure
170
of hardware components.
173
And the MySQL software has to be set up to take advantage
174
of the hardware resources that are available.
176
<h3>Config File (my.cnf)</h3>
178
The config file needs to be set up for production environment.
179
MySQL has defaults for where it allocates the files that it needs;
180
where they are placed depends on the OS on which it is running.
181
For greater control, space management and performance the user should
182
define where these files are assigned.
183
For example the base data directory for MySQL tables etc
184
in Linux is /var/lib/MySQL.
185
For SETI@home we assigned this to directory to another data partition
186
/mydisks/a/apps/mysql/data/,
187
to ensure that there was enough space and performance.
188
It made it easy to do physical backups without including
189
additional files that were not related to the database.
192
Here are some other file directory assignments for the SETI@home environment:
194
innodb_data_home_dir = /mydisks/a/apps/mysql/data/
195
innodb_data_file_path = ibdata1:16G;ibdata2:16G;ibdata3:16G;
196
ibdata4:16G;ibdata5:16G; ibdata6:16G;ibdata7:16G;ibdata8:16G;
197
ibdata9:16G;ibdata10:16G;ibdata11:16G;ibdata12:16G;
198
innodb_log_group_home_dir = /mydisks/a/apps/mysql/mysql_logs/innodb_logs/
199
innodb_log_arch_dir = /mydisks/a/apps/mysql/mysql_logs/innodb_logs/
203
Example of a MySQL config file:
206
#datadir=/var/lib/mysql
207
#datadir=/home/mysql/data/
208
datadir=/mydisks/a/apps/mysql/data/
209
join_buffer_size = 84M
210
#log-bin ##/// this comment line disables replication
211
log-slow-queries = /mydisks/a/apps/mysql/jocelyn_slow.log
213
socket=/tmp/mysql.sock
215
set-variable = delay_key_write=all
216
set-variable = key_buffer= 750M
217
set-variable = max_allowed_packet=2M
218
set-variable = table_cache=256
219
set-variable = sort_buffer=2M
220
set-variable = record_buffer=2M
221
set-variable = myisam_sort_buffer_size=512M
222
set-variable = query_cache_limit=2M
223
set-variable = query_cache_size=16M
224
set-variable = thread_cache=128
226
# Try number of CPU's*2 for thread_concurrency
227
set-variable = thread_concurrency=8
228
set-variable = max_connections=256
229
set-variable = max_connect_errors=1000
231
## more changes for slave replicant
232
#master-host = xxx.ssl.berkeley.edu
233
#master-user = slavexxx11
234
#master-password = masterpwxxx11
235
#replicate-do-db = SETI_BOINC
236
#replicate-ignore-db = mysql
238
# Uncomment the following if you are using Innobase tables
239
innodb_data_home_dir = /mydisks/a/apps/mysql/data/
240
innodb_data_file_path = ibdata1:16G;ibdata2:16G;ibdata3:16G;
241
ibdata4:16G;ibdata5:16G; ibdata6:16G;ibdata7:16G;ibdata8:16G;
242
ibdata9:16G;ibdata10:16G;ibdata11:16G;ibdata12:16G;
243
innodb_log_group_home_dir = /mydisks/a/apps/mysql/mysql_logs/innodb_logs/
244
innodb_log_arch_dir = /mydisks/a/apps/mysql/mysql_logs/innodb_logs/
245
set-variable = innodb_mirrored_log_groups=1
246
set-variable = innodb_log_files_in_group=4
247
set-variable = innodb_log_file_size=1000M
248
set-variable = innodb_log_buffer_size=16M
249
set-variable = innodb_flush_method=O_DIRECT
250
set_variable = innodb_fast_shutdown=1
251
innodb_flush_log_at_trx_commit=0
253
set-variable = innodb_buffer_pool_size=4584M
254
set-variable = innodb_additional_mem_pool_size=8M
255
set-variable = innodb_file_io_threads=64
256
set-variable = innodb_lock_wait_timeout=50
260
basedir=/mydisks/a/apps/mysql
263
err-log=/mydisks/a/apps/mysql/jocelyn.err
264
pid-file=/mydisks/a/apps/mysql/jocelyn.pid
272
During normal operations it is useful to monitor the MySQL IO traffic,
273
memory usage and connection activity to various client applications.
274
Mytop application script give useful realtime status for the MySQL engine.
275
Here is a sample of the first lines of its output:
277
MySQL on localhost (4.0.23-max-log)
278
up 18+00:32:55 [10:50:21]
279
Queries: 641.7M qps: 432 Slow: 71.4k Se/In/Up/De(%): 51/01/43/03
280
qps now: 382 Slow qps: 0.0 Threads: 413 ( 2/ 28) 43/01/46/09
281
Cache Hits: 58.2M Hits/s: 39.2 Hits now: 17.3 Ratio: 17.9% Ratio now: 10.6%
282
Key Efficiency: 99.4% Bps in/out: 1.7k/ 1.6k Now in/out: 63.5k/338.1k
285
It shows the historic queries/sec is 432 qps and the current
286
sample was measured at 382 qps.
287
The query cache hit rate is 17.9% historically and
288
for the current sample period it is 10.6% and the cache
289
fulfillment rate is 39.2 qps.
292
Useful Innodb information from Mytop is shown towards the end of the
294
The buffer pools information in given in number
295
of pages that are 16KB in size. See example below:
301
I/O thread 0 state: waiting for i/o request (insert buffer thread)
302
I/O thread 1 state: waiting for i/o request (log thread)
303
I/O thread 2 state: waiting for i/o request (read thread)
304
I/O thread 3 state: waiting for i/o request (write thread)
305
Pending normal aio reads: 0, aio writes: 0,
306
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
307
Pending flushes (fsync) log: 0; buffer pool: 0
308
1470930 OS file reads, 543461 OS file writes, 53800 OS fsyncs
309
1 pending preads, 0 pending pwrites
310
228.88 reads/s, 21594 avg bytes/read, 185.98 writes/s, 13.50 fsyncs/s
311
-------------------------------------
312
INSERT BUFFER AND ADAPTIVE HASH INDEX
313
-------------------------------------
314
Ibuf for space 0: size 335, free list len 283, seg size 619,
315
219535 inserts, 211776 merged recs, 45660 merges
316
Hash table size 9097667, used cells 2711301, node heap has 4751 buffer(s)
317
1573.54 hash searches/s, 5752.12 non-hash searches/s
321
Log sequence number 557 540674217
322
Log flushed up to 557 540451369
323
Last checkpoint at 556 4020363027
324
0 pending log writes, 0 pending chkp writes
325
39114 log i/o's done, 0.70 log i/o's/second
326
----------------------
327
BUFFER POOL AND MEMORY
328
----------------------
329
Total memory allocated 5032392104; in additional pool allocated 8386560
330
Buffer pool size 280576
332
Database pages 275825
333
Modified db pages 186393
335
Pending writes: LRU 129, flush list 0, single page 0
336
Pages read 2143598, created 23058, written 694488
337
301.17 reads/s, 4.40 creates/s, 216.68 writes/s
338
Buffer pool hit rate 991 / 1000
342
6 queries inside InnoDB, 0 queries in queue
343
Main thread process no. 12155, id 1147140464, state: sleeping
344
Number of rows inserted 9780, updated 1039701, deleted 60084, read 159846476
345
0.10 inserts/s, 374.56 updates/s, 63.69 deletes/s, 1116.99 reads/s
346
----------------------------
347
END OF INNODB MONITOR OUTPUT
348
============================
353
Iostat is the UNIX type utility that provides a display of
354
the IO statistics for peripherals on a server or workstation.
355
For continuous displays of extended information for all devices.
356
Iostat should be invoke as follows:
360
(this will produce an updated display every 5 seconds for all devices and give data in KB)
365
This program is making changes and getting the status of various MySQL
367
It is not interactive but can be made to repeat a given
368
function by using number repeat option. For example
370
mysqladmin extended-status 10
373
This will show the status display and repeat the display every 10 seconds.
374
Adding the .r option will give followup displays that show delta
375
differences with the first display values.
377
</h2>Performance Tweaking</h2>
380
An often overlooked area of performance is the requirement
381
for reliable power and air conditioning.
382
Power failures can eliminate all the benefits accrued
383
by careful planning for hardware and software installations.
384
Experience is that unreliable power can lead to days
385
of recovery with data loss and subscriber discontent.
386
Similarly, insufficient cooling accelerates the aging of
387
hardware components and can cause data corruption and
388
downtime more frequently than the one would expect given the hardware specs.
390
There are several parameters in my.cnf that can be adjusted (within limits)
391
for better throughput.
392
Then the distribution of MySQL files to specified disk subsystems,
393
allocation of RAM and Config: my.cnf options for files, RAM, IO options
397
<h3>MySQL Configuration</h3>
399
Multi threads, query caching
401
<h3>Files. Distribution</h3>
403
Innodb files, transaction log files, bin-log files, MyISAM data/index files
405
<h3>Slow Query Log</h3>
407
Turn on Slow Query log to monitor slow queries.
409
<h3>RAM Allocation</h3>
added
removed
doc/mysql_config.php
