Author: Michael Blood
HANA Database Backup using Cron
HANA Database Backup using Cron
To create backups using HANA we
- create a user
- create a user key
- create a script
- schedule the script to run via cron job
1) Create a HANA user to use specifically for Cron Job – Daily HANA Database Backup
Open an SQL Window and run the following to create a user, give them access to backup, and then remove their password so they can not connect via login.
create user backup_operator password Xxxxxxxx1; grant backup operator to backup_operator; alter user backup_operator disable password lifetime;
2) Create a HANA user key to be used for automated connection to the HANA database by the Backup User
Login to the server console using putty.
Under the hdbclient/ directory use the hdbuserstore command to create a key, -i makes it so you will then have to type in a password key from the command above
#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbuserstore -i SET cronbackupkey localhost:30015 backup_operator
Next, list all keys so that we know where the keys are that we will use to run the cron job automatically.
#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbuserstore list DATA FILE : /home/backupuser/.hdb/sid-hdb/SSFS_HDB.DAT KEY CRONBACKUPKEY ENV : localhost:30015 USER: backup_operator
Run a quick check of the new key by running a test command, this is where the password you entered above will be used
#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U cronbackupkey "select now() from dummy;"|more CURRENT_TIMESTAMP "2014-12-26 21:46:24.799000000" 1 row selected (overall time 600 usec; server time 98 usec)
If you run into a situation where the password is wrong, you may end up with a message usage as this:
* 416: user is locked; try again later: lock time is 1440 minutes; user is locked until 2014-12-27 21:35:34.3170000 (given in UTC) [1440,2014-12-27 21:35:34.3170000] SQLSTATE: HY000
If that happens, fix the password by running the hdbuserstore -i DELETE cronbackupkey and hdbuserstore -i SET command above with the correct password than run the following commands to allow the user access again.
alter user backup_operator RESET CONNECT ATTEMPTS;
Using these method the automated method finally came together. Keep in mind the the password for connecting to the database is stored in the key, so if you update the password in the database for the user, you will need to also update the password stored in the key.
3) Create a bash script to backup the HANA database to a time and date file
Create a bash script file you can run from a cron job, that does the work of creating a backup file. I create a wrapper script instead or running a command from the cron job, so I can decide in the script whether I would like to receive an email with the output of the command.
#touch /customcommands/hanabackup #chown hdbadm.sapsys /customcommands/hanabackup #chmod 754 /customcommands/hanabackup #vi /customcommands/hanabackup
tmpfile=/tmp/`date +s` textsearch="successful" sqlcommand="BACKUP DATA USING FILE ('$(date +F_%k%M)')" /vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U CRONBACKUPKEY $sqlcommand>$tmpfile #look up the backup that just completed" /vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U CRONBACKUPKEY "SELECT top 1 * FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME = 'complete data backup' ORDER BY UTC_START_TIME desc">>$tmpfile success=`grep $textsearch $tmpfile` if [ "$success" == "" ]; then echo "HANA BACKUP FAILED: `date`" echo "SQL COMMAND: $sqlcommand" echo "TEXT NOT FOUND:$textsearch" echo echo "File Out Put Below" echo --------------------------------- echo cat $tmpfile exit 10 fi exit 0
This script will output a message ONLY if the HANA backup fails, if it is successful, it just quietly completes
4) Finally setup a cron job which runs the HANA database backup once a day
create a crontab entry to run one time per day at 1:01 am
#crontab -e MAILTO=myemail@myemail.com 1 1 * * * /customcommands/hanabackup
In order to test that this works, you can force a test failure by setting a data_backup_buffer that is to high for your system see a recent post where we corrected this issue.
You can set a much higher limit than you system allows, and you can confirm that you have the CRON process send you an email each time that the process fails.
HANA error [447] backup could not be completed, [1000002] Allocation failed – Solution
HANA error [447] backup could not be completed, [1000002] Allocation failed – Solution
When attempting to backup a HANA database, I received an error which stopped the backup from progressing
[447] backup could not be completed, [1000002] Allocation failed ; $size$=536870912; $name$=ChannelUtils::copy; $type$=pool; $inuse_count$=1; $allocated_size$=536870912
When I looked at which files were created as part of the backup I found that the entire backup did not fail, actually it was only the ‘statisticsserver’ file that would have failed.
While the exact reasons for failure are still a mystery, I found several posts online encouraging me to reduce the size of the data_backup_buffer_size.
Since I had already had several successful backups using the default information, I was skeptical. Turns out it did work, by reducing the size of the buffer to only 256MB instead of 512MB, the backup started to work again
But the thing that had changed in my HANA installation was the license, so what was it about the reduction that made it work, I have 33GB of space I can use in the system.
So, while I have been able to resolve the issue and I can now create backups, I do still have an open question to HANA to find out what it is about the data_backup_buffer_size that prevented me from using the default 512 to run the system after it has worked previously?
- Do I now have full memory?
- Am I only licensed to use a certain amount of memory and I can’t exceed it?
- Do I have another setting in one of the other .ini files which limits how the data can be used?
Any comments on how i can control how this setting is used is appreciated!
Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS
Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS
I need to setup a PHP Website to connect to a HANA. The site is already installed with PHP and MySQL. Time permitting, I will also document the process of converting the existing MySQL Database to HANA. The environment:
- Hana OS: SUSE Linux Enterprise Server 11 SP3 x86_64 (64-bit)
- Web Server: Ubuntu 14.04 LTS
- PHP Version:5.5.9-1ubuntu4.5
First install the php odbc package
- sudo apt-get install php5-odbc
PHP ODBC Information
#php -i|grep odbc /etc/php5/cli/conf.d/20-odbc.ini, /etc/php5/cli/conf.d/20-pdo_odbc.ini, odbc ODBC_LIBS => -lodbc odbc.allow_persistent => On => On odbc.check_persistent => On => On odbc.default_cursortype => Static cursor => Static cursor odbc.default_db => no value => no value odbc.default_pw => no value => no value odbc.default_user => no value => no value odbc.defaultbinmode => return as is => return as is odbc.defaultlrl => return up to 4096 bytes => return up to 4096 bytes odbc.max_links => Unlimited => Unlimited odbc.max_persistent => Unlimited => Unlimited PDO drivers => mysql, odbc
Install HANA Database Client
Before starting, Since the Web server is on Amazon, I just took a quick snapshot so I could rollback to a ‘Pre HANA Client State’ on the server if I needed to.
- Download the HANA using this link SAP Hana Client download the client, you must be a member. Sign up as a public user with all of your information, you will be required to give them some information about your profession industry and company. There are several screen and it takes quite a few before you get to the download. BTW:If this link is broken, please email me and let me know. -[ if this link does not work, try it here }
- Extract the file into a temporary location and cd into the working directory
- cd /tmp
- tar -xvzf /home/michael/sap_hana_client.tgz
- cd sap_hana_linux64_client_rev80/
- Nexe ensure that the scripts that need to execute have execute privileges
- chmod +x hdbinst
- chmod +x hdbsetup
- chmod +x hdbuninst
- chmod +x instruntime/sdbrun
- Finally, run the installation program
- sudo ./hdbinst -a client
- press <enter> to accept the default installation path: /usr/sap/hdbclient
- You will see informaiotn like the following then you can take a look at the log file to see any other details of the installation.
Checking installation...
Preparing package 'Python Runtime'...
Preparing package 'Product Manifest'...
Preparing package 'SQLDBC'...
Preparing package 'REPOTOOLS'...
Preparing package 'Python DB API'...
Preparing package 'ODBC'...
Preparing package 'JDBC'...
Preparing package 'HALM Client'...
Preparing package 'Client Installer'...
Installing SAP HANA Database Client to /usr/sap/hdbclient...
Installing package 'Python Runtime'...
Installing package 'Product Manifest'...
Installing package 'SQLDBC'...
Installing package 'REPOTOOLS'...
Installing package 'Python DB API'...
Installing package 'ODBC'...
Installing package 'JDBC'...
Installing package 'HALM Client'...
Installing package 'Client Installer'...
Installation done Log file written to '/var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log'
- more /var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log
I saw some reports online that you would have to isntall ‘libaio-dev’, I did not have to, but in case you do, here is the command
- sudo apt-get install libaio-dev
Now you can connect to the hana client
- /usr/sap/hdbclient/hdbsql
Ignoring unknown extended header keyword
This happened to me, this is most likely because the tgz file was created on BSD. to address this install bsd tar and replace the ‘tar’ command above with the bsdtar command below.
- apt-get install bsdtar
- bsdtar -xvzf /home/michael/sap_hana_client.tgz
Testing that the client can connect to a HANA server
To confirm that the client works, run the client and connect to the HANA server
- # /usr/sap/hdbclient/hdbsql
Welcome to the SAP HANA Database interactive terminal
Type: h for help with commands
q to quit
- hdbsql=> c -n xxx.xxx.xxx.xxxx:port -u System -p mypass
I had to type a port, most likly because the server was not installed on a default port.
Creating an ODBC Connection to HANA Server
First Install the unixodbc packages
- sudo apt-get install unixodbc unixodbc-dev odbcinst
- ls /etc/odbc*
/etc/odbc.ini /etc/odbcinst.ini
Open the /etc/odbc.ini file and type the following
[hanadb]
Driver = /usr/sap/hdbclient/libodbcHDB.so
ServerNode =xxx.xxx.xxx.xxx:30015
Note that the blue matches the location you installed to, you must make sure these match or you will receive a cryptic [ISQL]ERROR: Could not SQLConnect message.
Connect using your username and password
- isql hanadb username passwor
+---------------------------------------+
| Connected! |
| sql-statement |
| help [tablename] |
| quit |
+---------------------------------------+
Implementing the HANA ODBC in PHP
As long as you have been able to connect via the ODBC methods above, PHP should be a breeze.
Just place the following in your code.
- $link=odbc_connect(“hanadb”,”myusername”,”mypassword”, SQL_CUR_USE_ODBC);
- $result = odbc_exec($link,”select * from sys.m_tables “);
- while($arr=odbc_fetch_array($result)) print_r($arr);
A couple of gotchas:
- Hana does not have the concept of ‘databases’, they use schemas so the user you connect with must be setup to use the Schema you want by default.
- Or you can prefix every table with your Schema name
- Or you can set the default context to the one you want before each connection
- I accomplished this by adding an extra line under the odbc_connect(); odbc_exec($dblink, “set schema MYSSCHEMA”);
That’s it! More posts to come on HANA.
Thanks to this post for help http://scn.sap.com/community/developer-center/hana/blog/2012/09/14/install-hana-client-on-ubuntu
Converting Assembla SVN repositor to GIT
Converting Assembla SVN repositor to GIT
Move a subversion repository, hosted at Assembla to GIT , For this, we expect that you already have a GIT server and repository setup with permission for you to write to it. there is an error to make sure it is 100% clean)
- first create 4 directories
- /data/code/svndump – to download your subversion repositories into
- /data/code/tmpsvn – to load your temporary subversion repository into after downloading
- /data/code/tmpgit1 – to create a temp git repository from your svn repo
- /data/code/tmpgit2 – to create a bare reformatted git repository from your first git
- first open the project / repo in assembla
- Click Import / Export > Download Dump
- right click on the link and copy link then open command line on a machine with svn and git
- cd /data/code wget -O svndump/myrepo.gz “https://linkcopoiedfromassempla” # download the file with quotes (to escape ampersands
- svnadmin create tmpsvn/myrepo #create an empt repositorycd
- gunzip -c svndump/myrepo.gz | svnadmin load tmpsvn/myrepo
- svn log -q file://data/code/tmpsvn/myrepo | awk -F ‘|’ ‘/^r/ {sub(“^ “, “”, $2); sub(” $”, “”, $2); print $2″ = “$2” <“$2″>”}’ | sort -u > users.txt
- git svn clone file:///data/code/tmpsvn/myrepo/ –no-metadata -A users.txt –stdlayout tmpgit1/myrepo
- cd tmpgit1/myrepo
- touch .gitignore #if I dont have any ignore files
- git svn show-ignore > .gitignore #if I do have ignore files i svn, this will return an error if there are no ignore files
- git add .gitignore
- git commit -m ‘Converting Properties from SVN to GIT’ .gitignore
- cd /data/code
- mkdir tmpgit2/myrepo
- cd tmpgit2/myrepo
- git init –bare
- git symbolic-ref HEAD refs/heads/trunk
- Go back to the tmpgit1 repo and push it to the new bare repo and rename the ‘trunk’ to ‘master’
- cd ../../tmpgit1/myrepo
- git remote add bare /data/code/tmpgit2/myrepo
- git config remote.bare.push ‘refs/remotes/*:refs/heads/*’
- git push bare
- cd /data/code/tmpgit2/myrepo
- git branch -m trunk master
- Clean up branches and tags (thanks to http://john.albin.net/git/convert-subversion-to-git for most of this)
- git for-each-ref –format=’%(refname)’ refs/heads/tags |
cut -d / -f 4 |
while read ref
do
git tag “$ref” “refs/heads/tags/$ref”;
git branch -D “tags/$ref”;
done
Now you have a correctly formatted repo at /data/code/tmpgit2/myrepo you can push it to your final git repository
- git remote add origin <your final git repository url >
- git push origin master
This may take a bit of time depending on how much code you have. But once it is complete you can browse your repository in stash.
Matraex has moved
Matraex has moved
Matraex has outgrown our space at 1101 W Grove St in Boise. The space was great and everyone LOVES being downtown, but we were to big for the tight space.
We will be hiring 2 to 3 people in 2015, starting with a new Developer on January 5th, and so we decided to treat ourselves to a little holiday treat and find a larger space.
So we found an office with more than twice the space (at the historic Alaska Building about a block closer into downtown Boise and moved in on Monday the 15th of Decmber.
With a giant bank of windows looking out at Bogus Basin, we have been very happy with the amount of space and light.
Over the coming weeks we will be adding our unique blend of geeky design to the office space.
The new address is, all those that would like to use the mail to send us anything, please send it to the address below:
1020 W Main St
Suite 250
Boise, Idaho 83702
8Th Annual Matraex River Trip (Survivors)
8Th Annual Matraex River Trip (Survivors)
We completed the 8th1 Annual Matraex Trip and everyone returned safe and sound. We all got wet to varying degrees, everyone got tossed around, one tossed half out (but wholly rescued). Other than one knee-bruised rib and a single rib-bruised knee, we ended the day with smiles all around. Here are some highlights of the trip this year:
1 – The 8th annual Matraex river trip is only one year after the second 6th2 annual Matraex river trip.
2 – Basically this was the 8th because the 6th3 happened twice, and there never was a 7th.
3 – It was actually the 5th that happened twice, and there never was a 6th, the 7th trip actually happened.
Converting an assembla subversion repository to a Jira / Stash hosted git repository
Converting an assembla subversion repository to a Jira / Stash hosted git repository
We were tasked by our client to move several subversion repositories, hosted at Assembla, to Stash GIT on dedicated Windows 2008 servers with SQL Server 2008.
First install windows git (http://git-scm.com/download/win)
So, first I had to download an install Jira (https://www.atlassian.com/software/jira/download), and Stash (https://www.atlassian.com/software/stash/download) from atlassian. This was very straight forward, just create an account at Atlassian download on the Webserver and install using the evaluation key.
I installed the apps using their default options with the default ports and then when prompted I just changed ‘localhost’ to the IP of the server I was working on.
Once they were installed I accessed them at
http://serverip:8080/ – Jira
http://serverip:7990/ – Stash
Each one of the programs takes you through a setup wizard. Where it prompts you to connect to a database server. This was straight forward you just have to make sure you setup separate databases for each of them. I used the same database server and username. The only gotcha comes with setting up the Stash database. here you will want to just use SQL to set up the DB.
CREATE DATABASE stash
USE stash
ALTER DATABASE stash SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE stash SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE pm_stash COLLATE SQL_Latin1_General_CP1_CS_AS
SET NOCOUNT OFF
This setup is a requirement of stash so the db is case sensitive. After both databases are setup you can setup your user with create drop insert and select privileges (i just gave it db_owner).
Setup Jira first and then when setting up Stash, you can just answer some basic questions about where your Jira installation is and you can combine the user accounts so you only manage one set of users.
Now that the default is setup, add a project and repository to stash, this is basic and the repository is empty and on the resulting screen it shows you how to push an existing repository into stash.
You will see in the Stash repository screen commands that you will use to push the desired repository to Stash take note of where to find it, we are going to first convert our repository from SVN to git
Log into a ‘working’ linux installation, I have a Ubuntu server.
Delete the temporary folder and download your svn repository into a temporary directory (I had seen some odd errors when I tried to clone again to a partially cloned git repo, so I decided I will always delete the git repo first , and start from scratch if there is an error to make sure it is 100% clean)
- rm -rf /tmp/temp.git
- git svn clone <svnurl> –no-metadata -A users.txt –stdlayout /tmp/temp.git
This clone command may take a while depending on how large the repository is as well as your bandwidth to pull down all of the code.
While it is difficult to be able to tell WHERE in the process you are in order to find out how long the process may take, I did find a couple of indicators which may help if it is a very lengthy process.
find out some information on your existing repository 1) the current revision and 2) what the total size of your repository is on the server (http://stackoverflow.com/questions/1740543/determine-total-size-of-svn-directory-trunk)
- svn info <svn repository url>
- svn list -vR <svn repository url>|awk ‘{if ($3 !=””) sum+=$3; i++} END {print “ntotal size= ” sum/1024000″ MB” “nnumber of files= ” i/1000 ” K”}’
You can use the information above in a couple of ways
- watch the output from your command, depending on the size of the commits to your repository, you will frequently see what revision number is currently being worked on. (r17 is revision 17) you can compare this against the most recent revision on the server to determine how far you are in the import
- Even though you git repository will be smaller than the SVN repository(between 0 and 30%), you may be able to compare the size to indicate how far along in the process it is. so cd /tmp/temp.git and ‘du –max-depth=0’
Ignore the errors about using –prefix, these just caused me headaches and so by NOT using their recommendations to set prefix=origin/ this will work.
If you receive an error which says I need to have ‘usera’ I would add usera to users.txt and run the rm and git svn clone command again
- echo “usera = User A <usera@com.com>” >> users.txt
This method works if you only have a couple of users, but for some of my repositories I had LOTS of users, so I used the following command and then edited the user file by hand to make sure it worked.
- svn log -q <svnurl> | awk -F ‘|’ ‘/^r/ {sub(“^ “, “”, $2); sub(” $”, “”, $2); print $2″ = “$2” <“$2″>”}’ | sort -u > users.txt
If you receive the error “Unexpected HTTP status 405 ‘Method Not Allowed’ on ‘/svn/wssus.admindash’” this means that you have some protected directories setup in your Assembla. Open the project within Assembla > Settings > Protected branch and remove the directories and run the rm and git svn clone command again
If dont have any ignore files in svn i fake one so I have at least one commit
- cd/tmp/temp.git
- touch .gitignore #if I dont have any ignore files
- git svn show-ignore > .gitignore #if I do have ignore files i svn, this will return an error if there are no ignore files
- git add .gitignore
- git commit -m ‘Converting Properties from SVN to GIT’ .gitignore
create another bare repo and create a symbolic link for the ‘trunk’ coming from SVN
- mkdir /tmp/bare.git
- cd /tmp/tmpbare.git
- git init –bare
- git symbolic-ref HEAD refs/heads/trunk
Go back to the temp git repo and push it to the new bare repo and rename the ‘trunk’ to ‘master’
- cd /tmp/temp.git
- git remote add bare /tmp/bare.git
- git config remote.bare.push ‘refs/remotes/*:refs/heads/*’
- git push bare
- cd /tmp/bare.git
- git branch -m trunk master
Clean up branches and tags (thanks to http://john.albin.net/git/convert-subversion-to-git for most of this)
- git for-each-ref –format=’%(refname)’ refs/heads/tags |
cut -d / -f 4 |
while read ref
do
git tag “$ref” “refs/heads/tags/$ref”;
git branch -D “tags/$ref”;
done
Now you have a correctly formatted repo at /git/bare.git. you can push it to your stash git repository
- cd /tmp/bare.git
- git config remote-url <your git repository url from stash>
- git push origin master
This may take a bit of time depending on how much code you have. But once it is complete you can browse you repository in stash.
Next step, setting up Git work flows to implement a required SDLC.
Converting a Subversion Repository(hosted on Assembla) to a GIT repository (hosted on dedicated Linux Host)
Converting a Subversion Repository(hosted on Assembla) to a GIT repository (hosted on dedicated Linux Host)
We were tasked by our client to move several subversion repositories, hosted at Assembla, to GIT on dedicated internal Linux server. First step was actually installing the Ubuntu Server on their ESXi4 server.
- I downloaded the ubutu 14.04 LTS iso
- Uploaded it to one of the datastores on the ESXi server
- Created a new VM with 4CPU, 8GB RAM and 100 MB disk space.
- I loaded the ubuntu 14.04 iso into the VM CD and started the VM.
- I installed ubuntu with all of the defaults
Next I installed GIT on the server.
- apt-get install git-core
- apt-get install git-svn
Next I clone the svn directory into git.
- git svn clone <svnurl> –no-metadata -A authors-transform.txt –stdlayout /svn/tempreponame/
Ignore the errors about using –prefix, these just caused me headaches and so by NOT using their recommendations to set prefix=origin/ this will work.
Enter your username and password and each time I get an error which says I need to have ‘usera’ I would add usera to users.txt and run the get svn clone command again
- echo “usera = User A <usera@com.com>” >> users.txt
If you receive the error “Unexpected HTTP status 405 ‘Method Not Allowed’ on ‘/svn/wssus.admindash’” this means that you have some protected directories setup in your Assembla. Open the project within Assembla > Settings > Protected branch and remove the directories and run the git svn clone command again
I dont have any ignore files in svn so i fake one
- cd /svn/tempreponame/
- touch .gitignore
- git add .gitignore
- git commit -m ‘Converting Properties from SVN to GIT’ .gitignore
Move the git repository to a new/bare repo and create a symbolic link for the ‘trunk’ coming from SVN
- cd /git/newreponame
- git init –bar
- git symbolic-ref HEAD regs/heads/trunk
Go back to the temporary git repo and push it to the new bar repo and rename the ‘trunk’ to ‘master’
- cd /svn/tempreponame
- git remote add bare /git/newreponame
- git config remote.bar.push ‘refs/remotes/*:refs/heads/*’
- git push bare
- cd /git/newreponame
- git branch -m trunk master
Clean up branches and tags (thanks to http://john.albin.net/git/convert-subversion-to-git for most of this)
- cd /git/newreponame
git for-each-ref –format=’%(refname)’ refs/heads/tags |
cut -d / -f 4 |
while read ref
do
git tag “$ref” “refs/heads/tags/$ref”;
git branch -D “tags/$ref”;
done
Now you have a new repo at /git/newreponame. check it out to confirm it worked by checking it out and confirming that you have the same log history as your original SVN repo
- cd /tmp/
- git clone /git/newreponame
- find . #this should list all of your files
- cd newreponame
- git log
Disk write speed testing different XenServer configurations – single disk vs mdadm vs hardware raid
Disk write speed testing different XenServer configurations – single disk vs mdadm vs hardware raid
In our virtual environment on of the VM Host servers has a hardware raid controller on it . so natuarally we used the hardware raid.
The server is a on a Dell 6100 which uses a low featured LSI SAS RAID controller.
One of the ‘low’ features was that it only allows two RAID volumes at a time. Also it does not do RAID 10
So I decided to create a RAID 1 with two SSD drives for the host, and we would also put the root operating systems for each of the Guest VMs there. It would be fast and redundant. Then we have upto 4 1TB disks for the larger data sets. We have multiple identically configured VM Hosts in our Pool.
For the data drives, with only 1 more RAID volume I could create without a RAID 10, I was limited to either a RAID V, a mirror with 2 spares, a JBOD. In order to get the most space out of the 4 1TB drives, I created the RAIDV. After configuring two identical VM hosts like this, putting a DRBD Primary / Primary connection between the two of them and then OCFS2 filesystem on top of it. I found I got as low as 3MB write speed. I wasnt originally thinking about what speeds I would get, I just kind of expected that the speeds would be somewhere around disk write speed and so I suppose I was expecting to get acceptable speeds beetween 30 and 80 MB/s. When I didn’t, I realized I was going to have to do some simple benchmarking on my 4 1TB drives to see what configuration will work best for me to get the best speed and size configuration out of them.
A couple of environment items
- I will mount the final drive on /data
- I mount temporary drives in /mnt when testing
- We use XenServer for our virtual environment, I will refer to the host as the VM Host or dom0 and to a guest VM as VM Guest or domU.
- The final speed that we are looking to get is on domU, since that is where our application will be, however I will be doing tests in both dom0 and domU environments.
- It is possible that the domU may be the only VM Guest, so we will also test raw disk access from domU for the data (and skip the abstraction level provided by the dom0)
So, as I test the different environments I need to be able to createw and destroy the local storage on the dom0 VM Host. Here are some commands that help me to do it.
I already went through xencenter and removed all connections and virtual disk on the storage I want to remove, I had to click on the device “Local Storage 2” under the host and click the storage tab and make sure each was deleted. {VM Host SR Delete Process}
xe sr-list host=server1 #find and keep the uuid of the sr in my case "c2457be3-be34-f2c1-deac-7d63dcc8a55a"
xe pbd-list sr-uuid=c2457be3-be34-f2c1-deac-7d63dcc8a55a # find and keep the uuid of the pbd connectig sr to dom0 "b8af1711-12d6-5c92-5ab2-c201d25612a9"
xe pbd-unplug uuid=b8af1711-12d6-5c92-5ab2-c201d25612a9 #unplug the device from the sr
xe pbd-destroy uuid=b8af1711-12d6-5c92-5ab2-c201d25612a9 #destroy the devices
xe sr-forget uuid=c2457be3-be34-f2c1-deac-7d63dcc8a55a #destroy the sr
Now that the sr is destroyed, I can work on the raw disks on the dom0 and do some benchmarking on the speeds of differnt soft configurations from their.
Once I have made a change, to the structure of the disks, I can recreate the sr with a new name on top of whatever solution I come up with by :
xe sr-create content-type=user device-config:device=/dev/XXX host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk ‘{print $NF}’` name-label=”Local storage XXX on `cat /etc/hostname`” shared=false type=lvm
Replace the red XXX with what works for you
Most of the tests were me just running dd commands and writing the slowest time, and then what seemed to be about the average time in MB/s. It seemed like, the first time a write was done it was a bit slower but each subsequent time it was faster and I am not sure if that means when a disk is idle, it takes a bit longer to speed up and write? if that is the case then there are two scenarios, if the disk is often idle, the it will use the slower number, but if the disk is busy, it will use the higher average number, so I tracked them both. The idle disk issue was not scientific and many of my tests did not wait long enough for the disk to go idle inbetween tests.
The commands I ran for testing were dd commands
dd if=/dev/zero of=data/speetest.`date +%s` bs=1k count=1000 conv=fdatasync #for 1 mb dd if=/dev/zero of=data/speetest.`date +%s` bs=1k count=10000 conv=fdatasync #for 10 mb dd if=/dev/zero of=data/speetest.`date +%s` bs=1k count=100000 conv=fdatasync #for 100 mb dd if=/dev/zero of=data/speetest.`date +%s` bs=1k count=1000000 conv=fdatasync #for 1000 mb
I wont get into the details of every single command I ran as I was creating the different disk configurations and environments but I will document a couple of them
Soft RAID 10 on dom0
dom0>mdadm --create /dev/md0 --level=1 --raid-devices=2 /dev/sda1 /dev/sdb2 --assume-clean dom0>mdadm --create /dev/md1 --level=1 --raid-devices=2 /dev/sdc1 /dev/sdd2 --assume-clean dom0>mdadm --create /dev/md10 --level=0 --raid-devices=2 /dev/md0 /dev/md1 --assume-clean dom0>mkfs.ext3 /dev/md10 dom0>xe sr-create content-type=user device-config:device=/dev/md10 host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk ‘{print $NF}’` name-label=”Local storage md10 on `cat /etc/hostname`” shared=false type=lvm
Dual Dom0 Mirror – Striped on DomU for an “Extended RAID 10”
dom0> {VM Host SR Delete Process} #to clean out 'Local storage md10' dom0>mdadm --manage /dev/md2 --stop dom0>mkfs.ext3 /dev/md0 && mkfs.ext3 /dev/md1 dom0>xe sr-create content-type=user device-config:device=/dev/md0 host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk ‘{print $NF}’` name-label=”Local storage md0 on `cat /etc/hostname`” shared=false type=lvm dom0>xe sr-create content-type=user device-config:device=/dev/md1 host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk ‘{print $NF}’` name-label=”Local storage md1 on `cat /etc/hostname`” shared=false type=lvm domU> #at this point use Xen Center to add and attach disks from each of the local md0 and md1 disks to the domU (they were attached on my systems as xvdb and xvdc domU> mdadm --create /dev/md10 --level=0 --raid-devices=2 /dev/xvdb /dev/xvdc domU> mkfs.ext3 /dev/md10 && mount /data /dev/md10
Four disks SR from dom0, soft raid 10 on domU
domU>umount /data domU> mdadm --manage /dev/md10 --stop domU> {delete md2 and md1 disks from the storage tab under your VM Host in Xen Center} dom0> {VM Host SR Delete Process} #to clean out 'Local storage md10' dom0>mdadm --manage /dev/md2 --stop dom0>mdadm --manage /dev/md1 --stop dom0>mdadm --manage /dev/md0 --stop dom0>fdisk /dev/sda #delete partition and write (d w) dom0>fdisk /dev/sdb #delete partition and write (d w) dom0>fdisk /dev/sdc #delete partition and write (d w) dom0>fdisk /dev/sdd #delete partition and write (d w) dom0>xe sr-create content-type=user device-config:device=/dev/sda host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk '{print $NF}'` name-label="Local storage sda on `cat /etc/hostname`" shared=false type=lvm dom0>xe sr-create content-type=user device-config:device=/dev/sdb host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk '{print $NF}'` name-label="Local storage sdb on `cat /etc/hostname`" shared=false type=lvm dom0>xe sr-create content-type=user device-config:device=/dev/sdc host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk '{print $NF}'` name-label="Local storage sdc on `cat /etc/hostname`" shared=false type=lvm dom0>xe sr-create content-type=user device-config:device=/dev/sdd host-uuid=`grep -B1 -f /etc/hostname <(xe host-list)|head -n1|awk '{print $NF}'` name-label="Local storage sdd on `cat /etc/hostname`" shared=false type=lvm domU>mdadm --create /dev/md10 -l10 --raid-devices=4 /dev/xvdb /dev/xvdc /dev/xvde /dev/xvdf domU>mdadm --detail --scan >> /etc/mdadm/mdadm.conf domU>echo 100000 > /proc/sys/dev/raid/speed_limit_min #I made the resync go fast, which reduced it from 26 hours to about 3 hours domU>mdadm --grow /dev/md0 --size=max
Working with GB Large mysql dump files -splitting insert statements
Working with GB Large mysql dump files -splitting insert statements
Recently I had to restore a huge database from a huge MySQL dump file.
Since the dump file was had all of the create statements mixed with insert statements, I found the recreation of the database to take a very long time with the possibility that it might error out and rollback all of the transactions.
So I came up with the following script which processes the single MySQL dump file and splits it out so we can run the different parts separately.
This creates files that can be run individually called
- mysql.tblname.beforeinsert
- mysql.tblname.insert
- mysql.tblname.afterinsert
cat mysql.dump.sql| awk 'BEGIN{ TABLE="table_not_set"} { if($1=="CREATE" && $2=="TABLE") { TABLE=$3 gsub("`","",TABLE) inserted=false } if($1!="INSERT") { if(!inserted) { print $0 > "mysql."TABLE".beforeinsert"; } else { print $0 > "mysql."TABLE".afterinsert"; } } else { print $0 > "mysql."TABLE".insert"; inserted=true } } '