Tagged with mysql

How to fix the Percona repo failure when installing Percona Toolkit

Here’s a solution to the not-so-long-standing issue of the Percona yum repo being broken for the CentOS 6 x86_64 version of the Percona-toolkit package. The repo listing is reporting an older version of the RPM which is not available on the site, so to fix this you just have to download the newer file and tell yum to add it locally. The side benefit is that you can use Yum to manage the RPM without adding the Percona repo, since the default settings for their repo could/have/had caused conflicts with Base Repo versions of MySQL packages; the Percona repo instructions set ‘enabled=1′ — not a great idea if you’re not setup to use the Yum priorities method of repo weighting.

So, if you see this after installing the repo via the instructions on their site:
Downloading Packages:
http://repo.percona.com/centos/6/os/x86_64/percona-toolkit-2.1.9-1.noarch.rpm: [Errno 14] PYCURL ERROR 22 – “The requested URL returned error: 404″
Trying other mirror.

Error Downloading Packages:
percona-toolkit-2.1.9-1.noarch: failure: percona-toolkit-2.1.9-1.noarch.rpm from percona: [Errno 256] No more mirrors to try.

The solution is as follows and is only two simple commands:
wget http://repo.percona.com/centos/6/os/x86_64/percona-toolkit-2.2.1-1.noarch.rpm
yum install ./percona-toolkit-2.2.1-1.noarch.rpm

Or you could even be so bold as to combine it via a seamless call without downloading locally, but I didn’t bother.

Tagged , , , , ,

Cloudflare, now offering to be your Single Point of Failure

There have been many articles about the downtime issue with Cloudflare last week, so I won’t get into the technical details of that. However, there’s the fine print to remember. Consider this a subtle reminder that core Internet infrastructure services like Cloudflare’s DNS-based “Always Online” caching and packet inspection security services do not come with Service Level Agreements even at the “Pro” account level. Even with a Pro account you are paying for a service with no uptime guarantee and you must only hope that it resolves your sites the majority of the time. This is fine, this is what the contract says: no SLA unless you pay for the Business account. An odd naming convention given that most Professionals are using their websites for business and would want the SLA, but I digress.

So, the SLA is not really the issue if you look at the architectural alternatives to building an architecture that desires availability when your primary and secondary DNS servers potentially going offline. The typical design involves using more than one and certainly more than two DNS servers for your domain so that your domain addresses will still resolve if the primary and even the second go offline. Typically these servers will be on separate subnets and even in separate geographical regions so that events like tsunamis and dataceneter fires do not take out both your primary and secondary name servers; so there are options for a third and fourth resolver; but not with Cloudflare.

Cloudflare limits the user to only using their DNS servers for your domain – of which they only provide two resolvers, not three or four like most DNS services. So if you wish to have a third or fourth name server entry to ensure that even if the primary and secondary Cloudflare DNS servers go offline, well sorry, you cannot do so. Cloudflare will disable your domain in their system if you use any DNS entries that are not their own – which includes a third or forth setting. So now you have your “Professional” websites using a DNS and security service that has no SLA but which you are paying for “Professional” level services. If your “Professional” grade sites go offline because Cloudflare botched the router upgrade or was hacked, you’re SOL and you do not get downtime credits, sorry. You can’t even design your architecture to resolve with alternate name servers or they will disable your domain. So if Cloudflare ever goes offline your sites will go offline with them and there are no alternatives. If you use Cloudflare then their service becomes your Single Point of Failure.

I am not one to create drama but this is an issue that none of the other users of Cloudflare “Pro” account users that I’ve talked to were aware of. So, here is a recent email exchange with Cloudflare regarding a credit for having caused all of my sites to be offline on more than one occasion — this is not limited to the recent event with their routers.

Cloudflare: “I’ve reviewed your account and note that you currently have 3 Pro subscriptions with us. At this time we do not offer a guaranteed level of service or SLA for our Free or Pro plans… We are also investing a great deal of time and resources to ensure the resiliency of the network even in the event of localized failures that may happen from time to time.” — So not only is there no SLA but there could be localized failures from time to time that you also do not get credit for; that explains the monitoring failures for some of my sites that are in the same rack and some even running on the same servers but only the Cloudflare enabled domains are shown as being offline at the same time as the others being available.

My response to Cloudflare. “I will not keep Cloudflare running for my sites. There are many reasons but another one has recently made itself know to me when I decided to add tertiary and quaternary DNS servers, yet I run into the following technical limitation that precludes my ability to rely on Cloudflare for my back-end infrastructure domain: if I want to specify a 3rd or 4th DNS server as a backup resolver (like Route53 or my own servers running PowerDNS for example) then the Cloudflare system complains and disables my domain. I understand why the system is designed this way — you want all traffic going through the CF system so that the features are executed and so forth. However, in the event that an issue occurs like the previous outage then there is no fall back for users/systems to resolve my domain via an alternate DNS system. I am limited to two Cloudflare DNS servers and nothing more.

The way that the DNS requirement is setup makes Cloudflare an all or nothing solution – you either use Cloudflare for the domain or you do not. And, as 785,000+ sites experienced, this makes Cloudflare (no matter how resilient and improved after this incident) a single point of failure that system engineers and architects cannot design failover services around.

This is the second time that I have had issues with Cloudflare services not working correctly. The first was when one of my servers went offline and the “Always On” feature didn’t do anything, the site was not kept online via cache even though there had been plenty of time for the crawlers to get the content (which is static unchanging, non dynamic, non-database driven = simply a front page that is supposed to load fast and act as a click portal to our primary systems).

And now I have been seeing users connect to my site from countries that I have setup in the block list. I have a number of ‘trouble’ countries configured in Cloudflare to disallow access to my site yet these users are connecting anyway. Clearly the country blocking feature is broken as well.

I want to use Cloudflare. I want to love it. I want to tell everyone I know how great and useful it it. But after six months of using it on several sites it has done nothing more than cause me a lot of time trying out different configurations and wondering why feature x/y/z isn’t working as stated. Then there have been the outages from human error and incorrect ITIL process adherence.

So I will be setting up some alternate caching servers at different datacenters and moving some of my content onto a CDN. Cloudflare has failed and I am tired of wanting to like it.”

Tagged , , , ,

Building a MySQL Private Cloud: Step 1

Building clusters is usually a fun time. Here’s one of my setups at the Equinix LAX1 facility that is being used for VPN services, OpenVZ clustering, and general RADIUS and MySQL clustering integration. Once the clustering design is finalized, it’s still in flux state while I try out different setups, I’ll post some physical+logical architecture diagrams to show “How to Build a Fault Tolerant Infrastructure for Virtualized MySQL NDB Cluster + Python-based VPN systems.” Stay tuned for more.

LAX1-rack-front

Tagged , , , , ,

OpenVZ and Amazon S3: how to solve the dreaded connection throttle failure

Sometimes we encounter odd application responses that seem to make no sense. One of these such issues is related to running virtual server instances (OS Containers not Para-Virtualized VMs) and attempting to back up their data to Amazon’s S3 cloud storage. For moderately sized virtual machines running MySQL databases or Python/PHP based websites and code repositories this can be an inexpensive, quickly provisioned, and easy way to provide disaster recovery backups in numerous geographic locations, since we generally want DR content to be located in a physically distant location. Nevertheless, we can encounter errors if using an S3 mount in a distance location from our server if the timezone/sync data is incorrect.

The commonly seen error is as follows – and it doesn’t give much information for troubleshooting and resolution.

WARNING: Upload failed:  ([Errno 32] Broken pipe)
WARNING: Retrying on lower speed (throttle=0.00)
WARNING: Waiting 3 sec...

The solution is seemingly unrelated to any network related or file-system settings on the virtual machine or the host server. It has to do with running S3 storage buckets in different time zones than your server and not having the system sync’d to NTP pools. So, the solution for Redhat/CentOS/Fedora/Scientific (for other Linuxes just replace the package management commands as needed):

First we have to enable the ability for the OpenVZ container to utilize NTP. Add the following line to your /etc/vz/conf/101.conf file (where 101 in this example is the ID of your own container, which you can find via the command “vzlist”).

CAPABILITY=" SYS_TIME:on"

Then restart the container(s) to get the setting to take and login to the container. You can either SSH or enter the container from the main host.

$ vzctl restart 101
$ vzctl enter 101

On the VM itself, install ntpdate package to be able to sync time data.

$ sudo yum install ntpdate

Sample ntp.conf file for NTP pool servers on CentOS 6.3. There are plenty of other configuration settings but these are the basics. This file goes on the VM server, not the host server.

$ sudo cat /etc/ntp.conf
driftfile /var/lib/ntp/drift
restrict default kod nomodify notrap nopeer noquery
restrict -6 default kod nomodify notrap nopeer noquery
restrict 127.0.0.1 
restrict -6 ::1
server 0.centos.pool.ntp.org
server 1.centos.pool.ntp.org
server 2.centos.pool.ntp.org
includefile /etc/ntp/crypto/pw
keys /etc/ntp/keys

Restart the ntpdate service on the VM to sync to the pool.

$ sudo service ntpdate restart
ntpdate: Synchronizing with time server:                   [  OK  ]

Add a cron job to the VM (either in /etc/crontab or via “crontab -e”) for automatic ability to sync the time every day.

# sync date/time with ntp pool
05 01 * * *	root /usr/sbin/ntpdate 2>&1 | /usr/bin/tee -a /var/log/messages

Now you can run S3 backups with throttling errors. Done and done. No more errors.

Tagged , , , , ,

[updated] Free book February returns – Get a copy of the InnoDB Quick Reference Guide

This month is a special month. It’s not because of President’s Day or even the exciting day where we revel in groundhogs. No, this month is special because the free book give-away is happening again. This is where you, the reader, gets to win something free for doing nothing more than posting a comment saying that you want a copy of my recently published book – The InnoDB Quick Reference Guide from Packt Publishing. The book is a great reference for DBAs, PHP, Python, or Perl programmers that integrate with MySQL and want to learn more about the InnoDB database engine.

So, all you have to do is post a comment here saying that you want a copy and write out a single (or more) sentence about how you use InnoDB in your development or production environment. At the end of the month two readers will be chosen via a random list sorting script that I’ve whipped up for just this purpose. You will then get an email from the publisher who will send a brand new e-copy of the book free of charge. It’s that simple. Free book February! Comment now!

Update:
Here are the winners of the book contest for the InnoDB Quick Reference Guide
Matthew Bigelow — who will be using the book during his upgrade of a medical services database architecture.
Erin O’Neill – who will be raffling the book at the upcoming http://www.sfmysql.org conference: register for the conference now and you’ll have a second chance to win a copy of the book!

Tagged , , , , ,

The InnoDB Quick Reference Guide is now available

I’m pleased to announce that my first book, the InnoDB Quick Reference Guide, is now available from Packt Publishing and you can download it by clicking here. It covers the most common topics of InnoDB usage in the enterprise, including: general overview of its use and benefits, detailed explanation of seventeen static variables and seven dynamic variables, load testing methodology, maintenance and monitoring, as well as troubleshooting and useful analytics for the engine. The current version of MySQL ships with InnoDB as the default table engine, so whether you program your MySQL enabled applications with PHP, Python, Perl or otherwise, you’ll likely benefit from this concise but comprehensive reference guide for InnoDB databases.

Here are the chapter overviews for reference:

  1. Getting Started with InnoDB: a quick overview of core terminology and initial setup of the testing environment.
  2. Basic Configuration Parameters: learn about the most common settings and prerequisites for performance tuning.
  3. Advanced Configuration Parameters: covers advanced settings that can make or break a high-perfomance installation of InnoDB.
  4. Load Testing InnoDB for Performance: learn all about general purpose InnoDB load testing as well as common methods for simulating production workloads.
  5. Maintenance and Monitoring: covers the important sections of InnoDB to monitor, tools to use, and processes that adhere to industry best practices.
  6. Troubleshooting InnoDB: learn all about identifying and solving common production issues that may arise.
  7. References and Links: informative data for further reading.
Tagged , , , , , , , ,

Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE statement to add two columns and then add some NOW() statements to the application’s SQL calls.

However, the problem is that the application in question is ion-cube encrypted so I cannot make SQL changes to add support for my ‘date_created’ and ‘date_modified’ columns. I could intercept the TCP steams via MySQL Proxy or a custom script but that’s quite silly to have to do for this. Other methods also exist but are rather hack-ish.

MySQL TRIGGERs easily solve this situation. Here are two TRIGGERs, one that runs on INSERT and the other on UPDATE. Now the database can have ‘date_created’ and ‘date_modified’ data without any application changes. Whenever a row is inserted into the table the ‘date_create’ column will be populated via the NOW() function and correspondingly when a row is modified the ‘date_modified’ column will be updated with the NOW() function.

DELIMITER |
CREATE TRIGGER trigger_radcheckDatetimeInsert BEFORE INSERT ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_create = NOW();
END; 
|
CREATE TRIGGER trigger_radcheckDatetimeModify BEFORE UPDATE ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_modify = NOW();
END; 
|
DELIMITER ;
Tagged , , ,

Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on update [^,]*//g' |            
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '               
  if (/^(INSERT.+?)\(/) { 
     $a=$1;               
     s/\\'\''/'\'\''/g;   
     s/\\n/\n/g;          
     s/\),\(/\);\n$a\(/g; 
  }                       
  ' > $1.sql              
cat $1.sql | sqlite3 $1.db > $1.err   
ERRORS=`cat $1.err | wc -l`           
if [ $ERRORS == 0 ]; then             
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql         
  rm $1.err         
    rm tmp          
else                
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi
Tagged , ,

MySQL: a convenient stored procedure for memory usage reporting

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo: https://bitbucket.org/themattreid/generic-sql-scripts/src/15c75632f1af/mysql-memory-report-storedproc.sql

#####################################################################                                                                                                                                                           
## NAME: memory_report_sp.sql                                                                                                                                                                                                   
## AUTHOR: Matt Reid                                                                                                                                                                                                            
## SITE: http://themattreid.com                                                                                                                                                                                                 
## DATE: 2012-09-02                                                                                                                                                                                                             
## LICENSE: GPL v3                                                                                                                                                                                                              
##                                                                                                                                                                                                                              
## INSTALL METHODS:                                                                                                                                                                                                             
##  a) install via linux shell                                                                                                                                                                                                  
##     $> mysql --user=root -p mysql < memory_report_sp.sql                                                                                                                                                                     
##  b) install via mysql command line                                                                                                                                                                                           
##     mysql> use mysql; import memory_report_sp.sql                                                                                                                                                                            
##                                                                                                                                                                                                                              
## USAGE:                                                                                                                                                                                                                       
##  execute the stored procedure to generate the report                                                                                                                                                                         
##     mysql> use mysql;                                                                                                                                                                                                        
##     mysql> call memory_report();                                                                                                                                                                                             
##                                                                                                                                                                                                                              
## EXAMPLE OUTPUT:                                                                                                                                                                                                              
##  [localhost mysql://root@localhost/mysql > call memory_report();                                                                                                                                                             
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | VARIABLE                    | VALUE    |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | TOTAL_BUFFERS_GLOBAL        | 420.00 M |                                                                                                                                                                                  
##  | TOTAL_BUFFERS_PER_THREAD    | 32.72 M  |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_LIMIT       | 151      |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_USED        | 2        |                                                                                                                                                                                  
##  | MAX_CONNECTION_USED_PERCENT | 1.32 %   |                                                                                                                                                                                  
##  | TOTAL_MEMORY_LIMIT          | 452.53 M |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE         | 47.44 M  |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE_PERCENT | 10.48 %  |                                                                                                                                                                                  
##  | HEAP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  | TEMP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  10 rows in set (0.02 sec)                                                                                                                                                                                                   
#####################################################################                                                                                                                                                           
                                                                                                                                                                                                                                
DELIMITER $$                                                                                                                                                                                                                    
DROP PROCEDURE IF EXISTS `memory_report` $$                                                                                                                                                                                     
CREATE PROCEDURE `memory_report` ()                                                                                                                                                                                             
BEGIN                                                                                                                                                                                                                           
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare some variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
DECLARE SUM_SGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE SUM_PGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE MAX_CONNECTIONS_LIMIT INT;                                                                                                                                                                                              
DECLARE MAX_CONNECTIONS_USED INT;                                                                                                                                                                                               
DECLARE CONNECTION_RATIO FLOAT;                                                                                                                                                                                                 
DECLARE TOTAL_HEAP BIGINT UNSIGNED;                                                                                                                                                                                             
DECLARE TOTAL_TEMPTABLE BIGINT UNSIGNED;                                                                                                                                                                                        
DECLARE k VARCHAR(255);                                                                                                                                                                                                         
DECLARE v BIGINT UNSIGNED;                                                                                                                                                                                                      
DECLARE TICK BOOL;                                                                                                                                                                                                              
                                                                                                                                                                                                                                
DECLARE MEM_LIMIT BIGINT UNSIGNED;                                                                                                                                                                                              
DECLARE MEM_USED BIGINT UNSIGNED;                                                                                                                                                                                               
DECLARE MEM_PERC FLOAT;                                                                                                                                                                                                         
DECLARE MAX_CONNECTION_USED_PERCENT FLOAT;                                                                                                                                                                                      
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare the queries                                                                                                                                                                                                          
#####################################################################                                                                                                                                                           
DECLARE GLOBALS CURSOR FOR SELECT                                                                                                                                                                                               
        VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                           
        FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES                                                                                                                                                                                
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('query_cache_size',                                                                                                                                                                                            
                'key_buffer_size',                                                                                                                                                                                              
                'innodb_buffer_pool_size',                                                                                                                                                                                      
                'innodb_additional_mem_pool_size',                                                                                                                                                                              
                'innodb_log_buffer_size',                                                                                                                                                                                       
                'read_buffer_size',                                                                                                                                                                                             
                'read_rnd_buffer_size',                                                                                                                                                                                         
                'sort_buffer_size',                                                                                                                                                                                             
                'thread_stack',                                                                                                                                                                                                 
                'join_buffer_size',                                                                                                                                                                                             
                'binlog_cache_size',                                                                                                                                                                                            
                'max_connections',                                                                                                                                                                                              
                'max_heap_table_size',                                                                                                                                                                                          
                'tmp_table_size')                                                                                                                                                                                               
        UNION                                                                                                                                                                                                                   
        SELECT VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                    
        FROM INFORMATION_SCHEMA.GLOBAL_STATUS                                                                                                                                                                                   
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('max_used_connections');                                                                                                                                                                                       
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
DECLARE CONTINUE HANDLER FOR NOT FOUND SET TICK = 1;                                                                                                                                                                            
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Set default values                                                                                                                                                                                                           
#####################################################################                                                                                                                                                           
SET SUM_SGA = 0;                                                                                                                                                                                                                
SET SUM_PGA = 0;                                                                                                                                                                                                                
SET MAX_CONNECTIONS_LIMIT = 0;                                                                                                                                                                                                  
SET MAX_CONNECTIONS_USED = 0;                                                                                                                                                                                                   
SET CONNECTION_RATIO = 0;                                                                                                                                                                                                       
SET TOTAL_HEAP = 0;                                                                                                                                                                                                             
SET TOTAL_TEMPTABLE = 0;                                                                                                                                                                                                        
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Query global variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
SET TICK = 0;                                                                                                                                                                                                                   
OPEN GLOBALS;                                                                                                                                                                                                                   
looper:LOOP                                                                                                                                                                                                                     
      FETCH GLOBALS INTO k,v;                                                                                                                                                                                                   
  IF TICK = 1 THEN                                                                                                                                                                                                              
    LEAVE looper;                                                                                                                                                                                                               
  END IF;                                                                                                                                                                                                                       
                                                                                                                                                                                                                                
    IF k in ('query_cache_size',
       'key_buffer_size',
       'innodb_buffer_pool_size',
       'innodb_additional_mem_pool_size',
       'innodb_log_buffer_size')                                                                                         
       THEN SET SUM_SGA = SUM_SGA + v;                                                                                                                                                                                          
    ELSEIF k in ('read_buffer_size',
       'read_rnd_buffer_size',
       'sort_buffer_size',
       'thread_stack',
       'join_buffer_size',
       'binlog_cache_size')                                                                                            
       THEN SET SUM_PGA = SUM_PGA + v;                                                                                                                                                                                          
    ELSEIF k in ('max_connections') THEN SET MAX_CONNECTIONS_LIMIT = v;                                                                                                                                                         
    ELSEIF k in ('max_heap_table_size') THEN SET TOTAL_HEAP = v;                                                                                                                                                                
    ELSEIF k in ('tmp_table_size','max_heap_table_size')                                                                                                                                                                        
     THEN SET TOTAL_TEMPTABLE = IF ((TOTAL_TEMPTABLE > v), TOTAL_TEMPTABLE, v);                                                                                                                                                 
    ELSEIF k in ('max_used_connections') THEN SET MAX_CONNECTIONS_USED = v;                                                                                                                                                     
                                                                                                                                                                                                                                
    END IF;                                                                                                                                                                                                                     
                                                                                                                                                                                                                                
END LOOP;                                                                                                                                                                                                                       
CLOSE GLOBALS;                                                                                                                                                                                                                  
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Output report                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
SET MEM_LIMIT = ROUND((SUM_SGA + (MAX_CONNECTIONS_LIMIT * SUM_PGA))/POW(1024,2),2);                                                                                                                                             
SET MEM_USED = ROUND((SUM_SGA + (MAX_CONNECTIONS_USED * SUM_PGA))/POW(1024,2),2);                                                                                                                                               
SET MEM_PERC = ROUND((MEM_USED * 100)/MEM_LIMIT,2);                                                                                                                                                                             
SET MAX_CONNECTION_USED_PERCENT = ROUND((MAX_CONNECTIONS_USED * 100)/MAX_CONNECTIONS_LIMIT,2);                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "GLOBAL_BUFFERS_TOTAL" AS VARIABLE, CONCAT(ROUND(SUM_SGA/POW(1024,2),2),' M') AS VALUE UNION                                                                                                                             
SELECT "THREAD_BUFFERS_TOTAL", CONCAT(ROUND((SUM_PGA * MAX_CONNECTIONS_LIMIT)/POW(1024,2),2),' M') UNION                                                                                                                        
SELECT "THREAD_BUFFERS_EACH", CONCAT(ROUND(SUM_PGA/POW(1024,2),2),' M') UNION                                                                                                                                                   
                                                                                                                                                                                                                                
SELECT "MAX_CONNECTIONS_LIMIT", MAX_CONNECTIONS_LIMIT UNION                                                                                                                                                                     
SELECT "MAX_CONNECTIONS_USED", MAX_CONNECTIONS_USED UNION                                                                                                                                                                       
SELECT "MAX_CONNECTION_USED_PERCENT", CONCAT(MAX_CONNECTION_USED_PERCENT, ' %') UNION                                                                                                                                           
                                                                                                                                                                                                                                
SELECT "MEMORY_UTILIZATION_LIMIT", CONCAT(MEM_LIMIT,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_ACTIVE", CONCAT(MEM_USED,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_RATIO", CONCAT(MEM_PERC,' %') UNION                                                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "HEAP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_HEAP / POW(1024,2),2),' M') UNION                                                                                                                                                 
SELECT "TEMP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_TEMPTABLE / POW(1024,2),2),' M') ;                                                                                                                                                
                                                                                                                                                                                                                                
END $$                                                                                                                                                                                                                          
DELIMITER ; 
Tagged , ,

SAN vs Local-disk :: innodb_flush_method performance benchmarks

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

  • Dell R610
  • 24 core Intel Xeon X5670 @ 2.93ghz
  • 72GB ECC RAM
  • Brocade 825 HBA
  • Local disk: RAID-10 15K SAS Ext3 (ugh)
  • SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(MLC-SSD), 36GB write cache (SLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
  • The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

  • SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
  • SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
  • SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
  • Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
  • Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
  • Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)
Tagged , , , ,