MySQL ver 5 Update

This page describes rational for upgrading to version 5 of Mysql.

Change log for 5.0.x can be found here....
dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html

Upgrade Rational:

1) 4.1.x will be deprecated very shortly - bugs no longer pursued.
2)Current release is 5.1 - 5.0. is one still major release behind with a shorten shelf life
3) 5.0 is in production mode at PDSF and RCF there are compatibility issues.

Issues with Client:

1) the only comment I would have that native
SL3 mysql version includes version 4 of mysql client (4.0.26 to
be exact) aka the same than under SL3.0.5.  SL5 (much later in the
game) 4.1.11 so again from the V4 series.

2) Possibly, you will have to deal with special patching procedure
on nodes where you deploy V5 as it will unlikely be part of the
baseline erratas for V4 ... but that is probably a detail [as mysql
release the patches too]

3) There seem to be a few issues with the clients which need
upgrading (perl DBI being one).

--This issue is with the 4.1 client accessing the 5.0 servers...(Pavel trying to access rcf) I was not able to reproduce

The question is compatibility with
SL native distribution versions ... It would be an issue if our
PHP tools would break ... A full evaluation welcomed (with caveats).
Do you think you could get a PHP view of things?
--Tested and our PHP works fine with 5.0

4)  With regard to prepared statements....

- client MySQL libs version 5.x: LIMIT ? would pass a quoted
string.
- client MySQL libs version 4.x: LIMIT ? would pass an integer
and ensure type SQL_INTEGER in bind

Additional information

+ server version 5.x: would allow LIMIT ? and treated differently
than in version 4.x (hence the client version behavior difference)
+ client version 4 / server version 4: all is fine
+ INSERT NOW()+0 used to work but now fails with MySQL version 5 and up

Evaluations:

So far I have tested the 4 client with 5 server and 5 client with 5 server - with regard to PHP, perl

and command line (script) access.  Modulo the prepared statement issue stated above (limit ?) tests are successful and are in fact in production i.e., DRUPAL is php accessing a 5 server and the file catalog on heston is perl from a 4 client accessing a 5 server.

"old" vs "new" nodes comparison, Jan-Feb 2011

2. Database Performance: First Pass, mostly Synthetics + random STAR Jobs

Test results :
 - unconstrained *synthetic* test using SysBench shows that new nodes
perform ~x10 times better than old ones (mostly due to x2 more ram
available per core => larger mysql buffers, fs cache etc);
 - real bfc job tests (stress-test of 100 simultaneous jobs, random
stream daqs) show performance similar to old nodes, with ~5% less time
spent in db (rough estimation, only able to run that 100 jobs test twice
today). Similar disk performance assumes similar db performance for
those random streams, I guess..
 - nightly tests do not seem to affect performance of those new nodes
(negligible load vs. twice the normal load for old nodes).

MySQL Performance Tuning

MySQL performance tuning

Primary purpose:
  a) performance optimization of the existing Offline and FileCatalog databases at MySQL level (faster response time, more queries per second, less disk usage etc);
  b) future db needs projections, e.g. what hardware we should use (or buy) to meet desired quality of service for STAR experiment in next few years;
  c) STAR DB API performance bottlenecks search;

 Expected speed-up factor from MySQL optimizations (buffers, query modes) is about 20-50%, API optimization gain *potentially* could be much higher than that.

Test node(s)

OFFLINE: dbslave03.star.bnl.gov, which is not included in Offline DB activies (backup-only); OFFLINE DB is READ-HEAVY.
FILECATALOG:  dbslave04.star.bnl.gov, which is not included in FC DB activies (backup-only); FILECATALOG DB is WRITE-HEAVY.

Measured db response values

1. Queries Per Second (QPS) - defined as QPS = (Total Queries / Uptime ) for cold start, and QPS = (dQueries/dUptime) for hot start;
2. Time to process typical reco sample - a) 5, 50, 500 events single job; b) several parallel jobs 500 events each (N jobs - tbd) - defined as TPS = (first job start time - last job stop time) for both cold and hot regimes;

Both parameters would be estimated in two cases :
a) cold start - sample runs just after mysql restart - all buffers and caches are empty;
b) hot start - 1000 events already processed, so caches are full with data;

MySQL varied parameters

per thread buffers : preload_buffer_size, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, thread_stack;
global buffers : key_buffer_size, query_cache_size, max_heap_table_size, tmp_table_size;
insert parameters : bulk_insert_buffer_size, concurrent_insert [e.g.=2 vs =1], pack_keys (enabled, disabled)
compression features : do we need compression for blobs at API level?
DNS parameters : HOST_CACHE_SIZE (default = 128);

Initial parameter values are :

"concurrent_insert" is interesting for FileCatalog database - we do many inserts quite often.

DNS

Quote from MySQL manual : "If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld." - we definitely have more than 128 hosts accessing our databases simultaneously, which means that we need to increase it to something like 2048 or so (to account for RCF farm nodes). Currently, both stock mysql distribution and HighPerformance version have 128 hosts set as buffer default.

Node FileSystem parameters

1. We should check "dir_index" feature of ext3 filesystem, which allows
 Brando $> tune2fs -l /dev/hdb1 | grep "dir_index" => dir_index enabled
 Duvall $> tune2fs -l /dev/sdc1 |grep "dir_index" => dir_index disabled

2. "noatime" mount option for /db0X disks, to reduce IO load a little bit. Both Duvall and Brando have no "noatime" attribute set (11 May 2009).

3. /tmp location (same disk, separate disk, RAM disk).
FC Duvall at duvall is using /dbtemp (ext. disk, raid?);
FC Brando is not using /dbtemp, because there's no /dbtemp defined in system yet;

Node varied parameters

Number of CPUs  scaling (1,2,4);
CPU power dependence;
RAM capacity dependence (1 Gb, 2Gb, 2+ Gb);
HDD r/w speed;

================================================================================
 Sample | 50 BFC events, pp500.ittf chain 
--------+----------------+------------------------------------------------------ 
 Host   | dbslave03.star.bnl.gov:3316 - Offline 
--------+----------------+------------------------------------------------------ 
 Date   | Fri, 08 May 2009 16:01:12 -0400 
================================================================================

 SERVER GLOBAL VARIABLES

================================================================================
 Variable Name	 |	Config Value	| Human-Readable Value
================================================================================
 preload_buffer_size 	 |	32768		| 	32 KB 
-------------------------+------------------------------------------------------
 read_buffer_size    	 |	1048576		| 	1 MB 
-------------------------+------------------------------------------------------
 read_rnd_buffer_size	 |	262144		| 	256 KB 
-------------------------+------------------------------------------------------
 sort_buffer_size    	 |	1048576		| 	1 MB  
-------------------------+------------------------------------------------------
 myisam_sort_buffer_size |	67108864	| 	64 MB  
-------------------------+------------------------------------------------------
 join_buffer_size	 |	131072		| 	128 KB  
-------------------------+------------------------------------------------------
 thread_stack		 |	196608		| 	192 KB  
================================================================================
 key_buffer_size	 |	268435456	| 	256 MB  
-------------------------+------------------------------------------------------
 query_cache_size	 |	33554432	| 	32 MB  		
-------------------------+------------------------------------------------------
 max_heap_table_size	 |	16777216	| 	16 MB  
-------------------------+------------------------------------------------------
 tmp_table_size	 |	33554432	| 	32 MB  
================================================================================
 bulk_insert_buffer_size |	8388608		| 	8 MB  
-------------------------+------------------------------------------------------
 concurrent_insert	    |	1		| 	1  
================================================================================

 PERFORMANCE PARAMETERS

================================================================================
 PARAMETER		 |	Measured Value	| 	Acceptable Range
================================================================================
 Total number of queries | 	6877 		| 	> 1000. 
-------------------------+------------------------------------------------------
 Total time, sec  	    |	1116		| 	any
-------------------------+------------------------------------------------------
 Queries/second (QPS)	    | 	6.16		| 	> 100.
-------------------------+------------------------------------------------------
 Queries/second - SELECT | 	5.89		|	> 100.0	 
-------------------------+------------------------------------------------------
 Queries/second - INSERT | 	0.17		|	any	 
-------------------------+------------------------------------------------------
 Queries/second - UPDATE | 	0		|	any	 
-------------------------+------------------------------------------------------
 Query cache efficiency  | 	45 %		|	> 20%	 
-------------------------+------------------------------------------------------
 Query cache overhead    | 	6 %		|	< 5%	 
-------------------------+------------------------------------------------------

PLOTS - Query Cache

 Query cache does not seem to help under 150 connections.....

Note different Y axis for Bogart

Bogart - No Query Cache

Bogart - Query Cache DEMAND Size = 26255360

DB02- No Query Cache

DB02- Query Cache DEMAND Size = 26255360

Plots - DB02 old/new Compare

Penguin Relion 230 vrs. Dell Precision 530 (specs here link to be added....)

Note the different time scales on the Y axis. 

Summary: The new penguin has faster db access under 150 connection but responds much slower than the dell

when connections are greater than 150 connection.

There is an additional point under the htemp box for 350 connections, @~11000 seconds. This point was taken with 8 clients (instead of 4 clients) to see if the clients where influencing results, slope is the same, apparently not.

There are two plots of the Penguin each showing a differnent RAID array.  The results are essentially identical under 150 connections.  Over 150 connections the first RAID array is much better.

The first plot is of the Penguin with first RAID array (Wayne please edit with specifics:

Penguin with Second RAID Array 

Dell:

Plots - Key Buffer = 52

This plot shows x=number of connections , y=time it takes for the StDbLib to retrun a complete data set for all the Star Offline databases.  This set of plot returns the key_buffer_size  from the STAR defualt of 268435456 to the mysql defualt of 523264.

Bogart

DB02

DB03

DB08

Plots - Star Default

This plot shows x=number of connections , y=time it takes for the StDbLib to retrun a complete data set for all the Star Offline databases.  This set of plot uses the STAR defualt parameters as described one page up.  This is to be used as a baseline for comparisions.

Bogart

DB02

DB03

DB08

Plots = Table Cache 64

This plot shows x=number of connections , y=time it takes for the StDbLib to retrun a complete data set for all the Star Offline databases.  This set of plot returns the table_cache from the STAR defualt of 1024 to the mysql defualt of 64.

Bogart

DB02

DB03

DB08

Fixing Slave Replication In A STAR Offline Database Mirror

Mike's trick for fixing an offline database slave when it won't sync with the master is to be carried out as follows:

cd /var/run/mysqld

ls -lh | grep "mysqld-relay-bin"

Find the relay file with the largest number at the end (this is the latest one)

Make sure the first line of the text file "/db01/offline/data/relay-log.info" contains that file

ie:

ls -lh | grep "mysqld-relay-bin"

mysqld-relay-bin.000001

mysqld-relay-bin.000002

so this means that /db01/offline/data/relay-log.info should contain:

"/var/run/mysqld/mysqld-relay-bin.000002"

as the first line

Then login to mysql via "mysql -S /tmp/mysql.3316.sock -p -u root"

and issue the command "slave start;"

then check the status with "show slave status \G"

Slave_IO_Running should show "Yes"

Slave_SQL_Running should show "Yes"

You may need to repeat this procedure if you get an error the first time.

Master/Slave syncronization issues,tools,solutions etc...

This page is dedicated to variety of master/slave syncronization issues, tools and solutions used at STAR. It is made for internal usage, tutorial for offsite facilities would be created later.

Maatkit (formerly MySQL Toolkit) is a bunch of Perl scripts, designed to make MySQL daily management tasks easier. STAR makes use of 'mk-table-checksum' to get lock-free reliable checksumming of FileCatalog and Offline database tables, and 'mk-table-sync' to fix inconsistent tables on slaves.

I. FileCatalog replication checksums

Master server: robinson.star.bnl.gov:3336
mk-table-checksum uses "repl.checksum" (database.table) to store checksum results. If FC slave was added after Nov.2008, then such table should be created manually. If not done exactly as described, checksums will not work properly, and slave will stop replicating with error message stating that "table repl.checksum does not exist".  

CREATE DATABASE repl;
CREATE TABLE checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     chunk      int          NOT NULL,
     boundaries char(64)     NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int          NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int              NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db, tbl, chunk)
  );

HOW-TO calculate checksums for all FileCatalog tables on Master, results and queries will propagate to slaves :

shell> nohup ./mk-table-checksum --emptyrepltbl --replicate=repl.checksum --algorithm=BIT_XOR \
h=robinson.star.bnl.gov,P=3336,u=root,p=XYZ -d FileCatalog_BNL >> checkrepl.log.txt 2>&1 

At this moment (Nov.2008), it takes about 20 min to checksum all FC tables.
HOW-TO show if there are any differences between master (robinson) and slaves (duvall, brando), when checksumming process is finished :

shell> mysql -u root --host duvall.star.bnl.gov --port 3336 -pXYZ -e "USE repl; SELECT db, 
 tbl, chunk, this_cnt-master_cnt AS cnt_diff, this_crc <> master_crc 
OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM checksum 
WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR 
ISNULL(master_crc) <> ISNULL(this_crc);" > duvall.tablediff.txt


shell> mysql -u root --host brando.star.bnl.gov --port 3336 -pXYZ -e "USE repl; SELECT db, 
tbl, chunk, this_cnt-master_cnt AS cnt_diff, this_crc <> master_crc 
OR ISNULL(master_crc) <> ISNULL(this_crc) AS crc_diff FROM checksum 
WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR 
ISNULL(master_crc) <> ISNULL(this_crc);" > brando.tablediff.txt

HOW-TO show the differences (by row) between master and slave for selected table. In the example below, changes for table "TriggerWords" is requested for duvall.star.bnl.gov :

shell> ./mk-table-sync --print --synctomaster --replicate repl.checksum 
h=duvall.star.bnl.gov,P=3336,u=root,p=XYZ,D=FileCatalog_BNL,t=TriggerWords

To make an actual syncronization, one should replace "--print" option with "--execute" (please make sure you have a valid db backup first!). It is often good to check how many rows need resync and how much time it would take, because it might stop replication process for this time.

 

Replication monitoring

Following tools are available for replication check:

1. Basic monitoring script, which allows to see what is going on with replication at this very moment. It does not send any notifications and cannot be thought as complete solution to replication monitoring. Mature tools (like Nagios) should be used in near future.

2. Server-side replication check made by M.DePhillips. This script checks which slaves are connected to master (robinson) on daily basic. List of connected slaves is compared to the known slave list (stored at dbAdmin database at heston.star.bnl.gov).

Server list and various parameters

Here will be a server list (only those servers participating in various replication scenarios)

1. FileCatalog master db server : fc1.star.bnl.gov:3336, ID: 880853336
slaves:

• fc2.star.bnl.gov:3336, ID: 880863336
• fc3.star.bnl.gov:3336, ID: 880873336 [ backup server, not used in round-robin ]
• fc4.star.bnl.gov:3336, ID: 880883336

2. Offline master db server: robinson.star.bnl.gov:3306, ID: 880673316
slaves:

• db01.star.bnl.gov:3316, ID:  880483316
• db02.star.bnl.gov:3316, ID: 880493316  - FAST rcf network
  
• db03.star.bnl.gov:3316, ID: 880503316 [ 23 Jul 09, new ID: 881083316 ] - FAST rcf network
  
• db04.star.bnl.gov:3316, ID: 880513316 [ 14 Jul 09, new ID: 881043316 ] - FAST rcf network
  
• db05.star.bnl.gov:3316, ID: 880523316 [ 16 Jul 09, new ID: 881053316 ] - FAST rcf network
• db06.star.bnl.gov:3316, ID 881023316  [old ID: 880533316 ]
• db07.star.bnl.gov:3316, ID: 880543316
• db08.star.bnl.gov:3316, ID: 880613316
• db10.star.bnl.gov:3316, ID: 880643316 [ 20 Jul 09, new ID 881063316 ]
• db11.star.bnl.gov:3316, 881033316 [ old ID: 880663316 ]
• db12.star.bnl.gov:3316, ID: 881013316, as of Apr 28th 2010, ID: 880253316
  
• db13.star.bnl.gov:3316 (former bogart), ID: 880753316 [ 22 Jul 09, new ID 881073316 ]
• omega.star.bnl.gov:3316, ID: 880843316 [3 Dec 09, backup server]
• db14.star.bnl.gov:3316, ID: 541043316
• db15.star.bnl.gov:3316, ID: 541053316
• db16.star.bnl.gov:3316, ID: 541063316
• db17.star.bnl.gov:3316, ID: 541073316
• db18.star.bnl.gov:3316, ID: 541083316
• onldb.starp.bnl.gov:3601, ID: 600043601 | resides in ONLINE domain
• onldb2.starp.bnl.gov:3601, ID: 600893601 | resides in ONLINE domain

External slaves :
Seo-Young Noh <rsyoung@kisti.re.kr>? KISTI (first server) : 990023316
Xianglei Zhu <zhux@tsinghua.edu.cn> KISTI (second server) : 991013316
Richard Witt <witt@usna.edu> : hvergelmir.mslab.usna.edu, 992073316 | USNA
Kunsu OH <kunsuoh@gmail.com> : 992083316 | Pusan National University
Peter Chen <pchen@sun.science.wayne.edu> : rhic23.physics.wayne.edu, 991063316| Wayne State University
Matt Walker <mwalker@mit.edu> : glunami.mit.edu, 992103316 | MIT
Jeff Porter <rjporter@lbl.gov>, pstardb1.nersc.gov, 1285536651 | PDSF
Douglas Olson <dlolson@lbl.gov> :  992113316 | LBNL

Server ID calculation :
SERVER_ID is created from the following scheme: [subnet][id][port], so 880613316 means 88 subnet, 061 id, 3316 port...
subnet '99' is reserved for external facilities, which will have something like 99[id][port] as server_id.

Setting Up A Star Offline Database Mirror

Set up an environment consistent with the STAR db environments.

1. Create a user named 'mysql' and a group 'mysql' on the system.
2. Create a directory for the mysql installation and data.  The standard directory for STAR offline is /db01/offline
3. In /db01/offline place the tarball of mysql version 5.0.51a (or greater) downloaded from here. 
4. Untar/zip it.  Use "--owner mysql" in the tar command, or recursively chown the untarred directory so that mysql is the  owner (eg. 'chown mysql:mysql  mysql-5.0.51a-linux-i686-glibc23').  Delete the tarball if desired.
5. Still in /db01/offline, create a soft link named 'mysql' that points to the mysql directory created by the untar (ln -s mysql-5.0.51a-linux-i686-glibc23 mysql').  /db01/offline should now have two items:  mysql (a link), and mysql-standard-5.0.51a-linux-i686-glibc23.
6. Create a directory /var/run/mysqld and set the owner to mysql, with 770 permissions.  (This may be avoideable with a simple change to the my.cnf file (to be verified).  If this change is made to the "official" my.cnf, then this step should be removed.)

Next, acquire a recent snapshot of the offline tables and a my.cnf file

There will be two files that need to be transfered to your server; a tarball of the offline database files (plus a handful of other files, some of which are discussed below) and a my.cnf file.  

If you have access to RCF, a recent tar of the offline databases can be found here: /star/data07/db/offline_08xxxxx.tar.gz.  Un-tar/zip the tarball (with "--owner mysql") in /db01/offline on your server to get a 'data' directory.  (/db01/offline/data). 

The my.cnf file can be found in /star/data07/db/my.cnf, and should be put in /etc/my.cnf.  Before your server is started, either the master information should be commented out, or the server-id should be adjusted to avoid clashing with an existing production slave.  Contact Dmitry Arkhipkin to determine an appropriate server-id.

Start the server

At this point you should be able to start the mysql server process.  It should be as simple as the following command:

cd /db01/offline/mysql && ./bin/mysqld_safe --user=mysql &

The data directory

The databases

You will now find a complete snapshot of STARs offline data. We use MYSQLs MYISAM storage engine which stores its tables in three files under a directory that is the database name. For example, for database Calibrations_ftpc you will see a directory of the same name. If you descend into that directory you will see three files for each table name. The files are MYD which contain the data, MYI which contain the indexes and FRM which describes the table structure.

Other files

• master.info - This files contains information about the slaves master (aptly named). This includes who the master is, how to connect, and what was the last bit of data passed from the master to the slave. This is stored as an integer in this file.
• You will also find log files, bin files and their associated index files.