Mysql Improving Performance


I need to investigate speeding up a mysql Database for the office. So this is how will setup and test.

  • Install and Test Mysql
  • Create Tables
  • Create Data sets to load
  • Develop similar load
  • Record the timings


Try and Improve things using

  • Indexes
  • Transactions
  • Something else ????....

Sounds like a plan .... Let's go


As I am doing this on Ubuntu 14


    apt-get install mysqld

Then put my Db commands into 1_Create.sql

drop database pt;

Create database pt;
use pt;

create table customers ( ID INT, NAME varchar(20), Address Varchar(80), Phone Va
rchar(30), Custtype int );

create table customers_update ( ID INT, NAME varchar(20), Address Varchar(80), P
hone Varchar(30), Custtype int );

create unique index customers_U_id on customers (ID);
create index customers_name  on customers (NAME);
create index customers_custtype on customers (Custtype);

Now create the Db - like this

mysql <1_Create_db.sql

As I am on private machine I am not using username and password !!! Do not do this on an internet connected machine

Db Created.

Load Table

I am lazy - so as I need several 000's of records - I am going to write a small data generator.


import sys
for n in range (1,items):
   print ("%d,NAME%d,Adress Street %d,44123%d,%d"%(n,n,n,n,n%4))

Test it like this

python 5

And I see

1,NAME1,Adress Street 1,441231,1
2,NAME2,Adress Street 2,441232,2
3,NAME3,Adress Street 3,441233,3
4,NAME4,Adress Street 4,441234,0

Which is what I want - to put it in a file just do

python 5 > customers.csv

Note the file name is important

Load the Customers Table

Using mysqlimport

mysqlimport pt customers.csv -L --fields-terminated-by=','

A quick select * from customers; gives

| ID   | NAME  | Address         | Phone  | Custtype |
|    1 | NAME1 | Adress Street 1 | 441231 |        1 |
|    2 | NAME2 | Adress Street 2 | 441232 |        2 |
|    3 | NAME3 | Adress Street 3 | 441233 |        3 |
|    4 | NAME4 | Adress Street 4 | 441234 |        0 |

To Load data into customers_update

cp customers.csv customers_update.csv
mysqlimport pt customers_update.csv -L --fields-terminated-by=','


I am going to use a Transaction to do the update.

I have placed these commands in a .sql file called 2_update.sql

And I will run it using

mysql pt < 2_update.sql
start transaction;
select now(),'delete starting' from dual;
delete from customers where custtype=2;
select now(),'Insert Starting ' from dual;
insert into customers (select * from customers_update where custtype =2);
select now(),'Commit Starting ' from dual;
select now(),'Commit Done' from dual;

When I run this I get

now()   delete starting
2015-06-15 11:24:19 delete starting
now()   Insert Starting 
2015-06-15 11:24:49 Insert Starting 
now()   Commit Starting 
2015-06-15 11:25:41 Commit Starting 
now()   Commit Done
2015-06-15 11:25:41 Commit Done
operation time
Delete 30s
Insert 52s
Commit 0s

As the data should be evenly split - this is 250,000 records. Which I think is quite acceptable.

What happens to the user query time when this is happening ?

I will not create a random Query (to stope the Db Caching) - and run this as the previous update is running.

First the query code....

import mysql.connector
import random
import sys
from timeit import Timer
import datetime

#Query use a Primary Key
def Query(num):
    global cnx
    cursor = cnx.cursor()
    query = "SELECT ID,NAME,Custtype from customers WHERE ID = {0}"
    for (cid, name, ctype ) in cursor:
        print("{}, {} {} ".format(
            cid, name, ctype ))

# Main Loop

#mysql Connect 1 time only
cnx = mysql.connector.connect(user='root',database='pt')

for q in range(1,loop):
    #Timeit using lambda 
    #t = Timer(lambda: Query(num))
    #print ("\t\t\t\t{0}".format(t.timeit(number=1)))
print("Query for %d took %d seconds"%(loop,(end-start).seconds))

You run this like....

time python 5000

You will see ....

469518, NAME469518 2 
975783, NAME975783 3 
Query for 5001 took 2 seconds

On my hardware/setup the average time for 5000 queries is 2 seconds.

Query for 5001 took 2.883825 seconds
Query for 5001 took 2.870572 seconds
Query for 5001 took 2.958822 seconds
Query for 5001 took 2.929722 seconds
Query for 5001 took 2.755262 seconds
Query for 5001 took 2.822934 seconds
Query for 5001 took 2.905826 seconds
Query for 5001 took 2.895257 seconds
Query for 5001 took 2.846605 seconds
Query for 5001 took 2.894246 seconds

Time with Update running

We need t execute two tasks at the same time - so we will use some Linux command line foo

    rm *.log
    nohup mysql pt < 2_update.sql 2>1 >> update.log&
    sleep 4s
    for a in $(seq 10);do python 5000 | grep Query; done

I placing the sleep - to make sure that the Transaction is in place.

The worst sample I found was

Query for 5001 took 2.435922 seconds
Query for 5001 took 4.502661 seconds
Query for 5001 took 3.130135 seconds
Query for 5001 took 3.333874 seconds
Query for 5001 took 2.069113 seconds
Query for 5001 took 2.094691 seconds
Query for 5001 took 2.140467 seconds
Query for 5001 took 2.011877 seconds
Query for 5001 took 2.145676 seconds
Query for 5001 took 2.175512 seconds

Which shows a slight jitter from 2.4-4.5 seconds - but is still reasonable.

Which I found surprising....

I will increase the datasize to 10M records and try and break it again.

10 Million Records

Update No Query RUnning

command time Increase
Delete 900s 30 times
Insert 430s 8 times
Commit 0 0 Increase

10M Instead of 1M - so you would expect 10-15 times slower. Delete seems Slow - but Insert seems in line.


After some digging around in the system and some reading - I wondered if the solution we would use with an Oracle Db would also work - partition the table.

With a loaded Table I did the following sql command.

ALTER TABLE customers 

I then ran the update script again. These were the figures I got.

command time Increase
Delete 360s 11 times
Insert 270s 5 times
Commit 0 0 Increase

SSH Quick guide to Keyless setup


A great way to connect to machines



ssh-keygen -t rsa

Thats it !!!

Copy the Keys


There are 2 ways - the simple (assuming everything is standard ie ssh ports)

ssh-copy-id user@

When you log into the remote machine - a trust is created - allowing you to log in again without specifying a password

Non Standard

Should the ssh be used with a special port, cipher etc - then the Standard way will not work.

So instead you need to manually (1 Line !!) do this

    cat ~/.ssh/ | ssh -p 34890  "mkdir -p ~/.ssh && cat >>  ~/.ssh/authorized_keys"

SSH A command

This now becomes

   ssh user@remote "df -kh"

No waiting no fuss. Great for Scripting.

Installing Spark on a Mac

Apache Spark

I hope these instructions will help you get a Spark environment up and running. I have this working on Mac OSX 10.10.3 Yosemite - It should be applicable to move Linux distros however.

First the bad news.... we need to install a few other things before we can do Spark...

  • Install Tasks
    1. Java
    2. My Java Environment
    3. Ant
    4. Mavern
    5. JDK
    6. Spark
My Java Environment

I do not want to install much into the System partitions, instead I will create my own JAVA area ... This way I can develop/play without effecting the integrity of the system.

In the following examples - my Java environment will all be based on ~/JAVA_ENV

So Lets create this and get installing

mkdir ~/JAVA_ENV/bin

Apache Ant

Ant is the make of the Java world. I downloaded it from apache apache-ant-1.9.5-bin.tar.gz and then

cd Downloads
tar -zxvf  apache-ant-1.9.5-bin.tar.gz 
cp ~/Downloads/apache-ant-1.9.5/bin/* ~/JAVA_ENV/bin/ 
cp ~/Downloads/apache-ant-1.9.5/lib/* ~/JAVA_ENV/bin/

We will Build a Java enviroment file as we go.

Java Env

In the bash shell please type these commands

echo "ANT_HOME=~/JAVA_ENV/bin" >> ~/Java.env
echo "PATH=\$ANT_HOME:\$PATH " >> ~/Java.env

Java JDK

I went to the Oracle site - and installed the latest JDK (jdk1.8.0_45) - this was a typical Mac install, and typically I had no idea where to find the JDK root.

However using the utility /usr/libexec/java_home I can 100% guarantee it is installed and where it is.

Java Env

echo "JAVA_HOME=\$(/usr/libexec/java_home)" >> ~/Java.env
echo "export JAVA_HOME" >> ~/Java.env

Apache Maven

I again download Maven from - this time taking the binary apache-maven-3.3.3-bin.tar.gz

To install this I carried out these steps

cp ~/Downloads/apache-maven-3.3.3-bin.tar.gz .
tar -zxvf apache-maven-3.3.3-bin.tar.gz
ln -s apache-maven-3.3.3 maven

If you create a logical link - you can install new versions of the product - and just use them by changing the symbolic link. Should they not work (or have a version miss-match) then you can quickly and seemlessly switch back to the previous version.

Java Env

echo "MAVEN_HOME=~/JAVA_ENV/maven" >> ~/Java.env
echo "export M2_HOME" >> ~/Java.env
echo "export MAVEN_HOME" >> ~/Java.env
echo "PATH=\$MAVEN_HOME\/bin:\$PATH " >> ~/Java.env

Apache Spark

We now (yes you guessed) download Spark from, and carry out these steps. I chose the Source code - as I do not have a Hadoop Enviroment on my Mac.

cd ~/Downloads
tar -zxvf Spark1.4.tar.gz
mv Spark1.4 ~/JAVA_ENV/
ln -s Spark1.5 Spark
cd Spark

I need to get my Java enviroment sorted - so

source ~/Java.env

Now build using mvn mvn -DskipTests clean package

If this produces an error like

command not found

Then you have an error with your Java setup, mvn (maven) can not be found.

The instalation at this point needs to access the Internet - as there are more packages and modules to be added. Just sit back and relax, go find a good book - and let all the packages download.

Quick Test

After the mavern build process has completed - you can quickly check if everything worked by

cd ~/JAVA_ENV/spark
./bin/spark-shell --master local[2]

You should see the scala> prompt

To get out just type


Coming Next - Using Spark with Python

Paris Euro Disney

Getting to Disney

From our hotel in the 18th district of Paris, we took the Metro until we connected with the French local railway system the RER. Here we bought a return ticket to EuroDisney which for the two of us cost us €30. Despite it being rush-hour we were soon able to get a seat, and after about 20 minutes we emerged from various tunnels and cuttings to be able to see the outskirts of the Paris countryside.


The journey in total took just under an hour, and as we walked out of the train station there was probably a two-minute walk until we arrived at Disney.



To try and save a little money we had pre-booked our tickets, because we were visiting Disney off-peak we were able to purchase what is called a mini ticket (as in mini mouse) - we printed the tickets (eventually) at the hotel the night before, and simply presented them at the turnstile where they were scanned and we were allowed in with no fuss and no bother.


The Disney Experience

There can be no doubting that Disney has a well-defined product, Market and clientele-it certainly makes the most of all the opportunities to extract as many euros as possible from the visitors.


Park did not feel that big, and we were always able to move around with not too much of a crowd (please bear in mind however that this was low season).

Throughout the day though always ample staff to load of the rides properly and to deal with customer questions-in fact the staff at Disney were quite faultless - and the language skills were extremely impressive.


Bad things

It may seem harsh to criticise, we were not there with young children who were probably excited about seeing Mr X or Miss Y - but where you to go there in the hope that your daughter could meet the Princess only to find out that the princess was booked solid of the day - I suspect your day could've been soured.

We were not bothered about seeing the princess, but it would've been nice to have had a go on some of the slightly more fun park type of rides that Disney has - However three out of the five fun park rides were either closed or are experiencing technical difficulties. To have them were actually being rebuilt and they did not look as though there will be ready for the public for at least several weeks if not months.


Fun Rides

We started off with Pinocchio- a very gentle I'm rather dull experience, however I suspect those of you with small children would've enjoyed it.

Next came Indiana Jones at the Temple of doom, we didn't really know what we had let ourselves in for. The queue was a mere five minutes, and as we were being strapped into a small chariot - it occurred to me we were going to have a thrilling ride. It certainly did not disappoint - a full 360° loop coupled with sharp turns and swift drops make this quite a white knuckle ride. In fact this was the scariest ride in the whole park.

Another notable ride was buzz light-year - who happens to be one of my favourite Disney characters, so this review is probably a little biased. We queued outside for approximately five minutes, And then we went into a set of corridors with some very dull buzz light-year themes on the wall. I was starting to think that this was the whole attraction -and I have to say I was very disappointed. After about 10 minutes of shuffling down this corridor we were quickly dropped into to person chairs, had a safety bar placed upon us, and instructed to pick up the laser gun. We then spoke thoroughly fun few minutes going through and shooting the monsters helping buzz light-year, This was made more fun because we were able to rotate our chair at will.

Big Thunder Breakdown

Big thunder Mt, was going to be our last ride before we went to watch the parade. The queue time had decreased from 60 minutes to 20 minutes, and typically we were loaded at about 30 minutes! The ride we had seen from the river boat and it looked quite fun, indeed it started out very well.


About 30 to 40 seconds into the right however just as we were climbing a steep slope everything stopped. A few seconds later were told in French that there was a train ahead of us going a little slowly - sort of a British rail "the the Leaves on the track" type excuse. The second became minutes, and minutes started to add up. Then two Disney employees arrived to tell us that the ride had been closed, and they then carefully unloaded us and we had to walk back to where we started from. The internal structure of big thunder Mt was fun to walk-through, however it did at yet another ride failure onto an already extensive list.

Paris Bikes and Shopping

Paris day 2

Continuing our efforts to see the city, we headed out of the hotel around 830 in the morning - not that we got very far as we stopped for breakfast about 100 m down the road. After a pleasant breakfast, May slightly more interesting by the waitress arguing with her boss, we boarded the metro and headed off to one of the most memorable landmarks in Paris the Arc De Trimuphe.

Arc De Triumphe

Alas when you a tourist, you know that you are approaching a major site by the fact there are hundreds of tourists there as well. This was the case yesterday with the Eiffel tower and the Trocadero, as soon as we emerged from the Metro Half of Beijing seems to be standing in front of us. After my slight complaints of yesterday about how noisy there were in Notre Dame Cathedral today they seemed much better behaved-possibly the noise level was being dulled by the number of trucks.

The ARC is very impressive, however the roundabout on the traffic detracts from the beauty of this area. Alas whilst we were there a motorbike and a tourist busHad a slight accident.


Champs D'Eysees

Walking down this is wonderful Avenue, with its multitude of expensive shops on either side was very pleasant. The collection of high-end fashion, restaurants, embassies and gift shops is endless.

Juliet unbeknown to me had a shopping plan !!! And when she found her first shop I was left to take pictures of passing motorbikes.

/galleries/2015/France/Day2/bikep1.thumbnail.jpg /galleries/2015/France/Day2/bikep3.thumbnail.jpg /galleries/2015/France/Day2/bikep5.thumbnail.jpg /galleries/2015/France/Day2/bikep6.jpg

No sooner have Juliet finished with the first shop, she spotted her second shop And in a flash I again standing alone on the pavement.

Shopping concluded, we continue to stroll down the Avenue as the number of police continued to increase. It appears that there is a state visit from Spain arriving in Paris today.


Towards Le Louvre

There just never seems to be an end off impressive buildings, when are the very posh shops stopped them some wonderful government buildings appeared. When the government buildings stopped - than parks or bridges appeared.


Passerelle Leopold-Sedar

I have just been reading on the BBC website about the problem this bridge is having due to people overloading it with locks.

Not not sure what I'm talking about ??


The river seemed very busy with commercial as well as tourist boats, we rested here for awhile watching the boats pass up-and-down, Listening to the tourists chair as they passed under the bridge (why ?) Before continuing our journey.


Le Louvre

The Louvre rather surprised us, we have been walking in a very pleasant garden typically lined with trees, fountains and statues as we approached another arch.


As soon as we cleared the arch, we were at our destination. I noticed two thingsWhen we arrived - it was quiet and the Beijing crowd were not here.


Paris Day 1


It is never easy to choose what to do when visiting a place and you have limited time - some people want to see the Museums - others want to go shopping; We seem to not like either of these options - but instead enjoy walking around trying to get a feel of the place - and to see some of the sights that the City has to offer.

I also like (very much) taking photo's - and whilst some of them have to be rather touristy - there are still plenty of interesting photo oppertunities that pop up all the time. My only complain is that I usually have the wrong lense with me at that time.

Notre Dame

As we had done Churches/Basillicas in Italy and Switzerland on this trip - is seems only fair that we give France an oppertunity to impress.... So our first port of call was the famous Cathederal of Notre Dame.

We arrived at around 09:00 - and there was a slight queue to get in - outside there were lots of tour groups being given a talk about the construction and location of the Cathederal.

Entering the Catherderal I was suprised at the number of people already inside - and the overall noise level. To be honest the Cathederal had more of a feel of a super-store which was having a sale - rather than a place of worship, and whilst it may be unfair to single out any one specific group of people - the Chinese visitors (and there were many) seemed to be the loudest, most rude and most ignorant - well done China 3 Gold Boo's from me.

The Catherderal however was quite beautiful inside, but compared with Rome - VERY SMALL indeed !!


Find the Tower

Although we could have taken a Metro to our next stop - we decided to walk, as I was hoping that a not so touristy place would prove to be worthwhile.


The place if the Palais de Luxembourg - about a 10 minute walk from Notre Dame.

Lunch Time

We needed a little re-fueling as we had not had breakfast at the hotel - mainly as I had not ordered it.

Outside the restaurant I thought was a typically french image...


Eiffle Tower

It had been growing bigger and bigger for about 30 minutes - but the magnificance of this tower still is quite breathtaking.


After this it was time to return to the hotel for a rest - this had taken us 6 hours of walking and our feet were starting to feel a little more than tired.

Off to France

Bye Bye Switzerland

After a fantastic 5 days in Switzerland we loaded up the bags and said our rather sad farewells to my family, and boarded the clean, on-time Swiss train service. After changing to another clean Deutch Bahn Intercity train we arrived in Basel - where we went through the "French" gate - and waited for a Alsace local train from Basel to Mulhouse (pronounced in French moule-ouse - but much funnier said in English as Mull-House) - The train was 25 minutes late. No problem.. we just waited around - on a platform covered in cigarette butts, our train looked like a group of English football fans had just had a party in it !!! Oh dear - France was looking rough - despite we were still standing in the French railway sataion in Basel.

Mull House

Arriving in the station where you expect to collect a fast TGV train you are hoping for a high tech place - like Euro-Star in London (note not Brussels) but it has a very laid back feel to it.

As there seemed to be at least 3 MullHouse stations we checked to make sure that Mullhouse-Ville was the correct one (and it was) - so set off for some lunch.

The meal was pleasant - and my old fondness with France started to return.

TGV Time

THe TGV arrived exactly on time - and left exactly on time. I am writing this from the 1st Class carriage at the rear of this speeding bullet. There is a screen on the bulkhead telling you where we are in the journey - and what speed we are doing.


I will not bore you with the fastest speed - but as you insist .... I have seen 320 Kph ...

This is what the view out of the windows looks like


The TGV carriage is a double decker - we are downstairs - which is a little bit of a shame - but there are only 50 seats. It is like a small coach. The seats are a cross between Business Class on a plane and a economy seat. I have a power socket for the laptop - window blinds, directable Air, Light, reclinable seat - it is very pleasant indeed.

I would not say this is a quiet train - the wind noise above 250 kph is quite loud - But who cares - this train rocks....

Swiss Mountains Part 2

Mountains Part 2

We took a train from Bern to Toun, and then a Bus across to the other side of the lake - then a cable tow railway up to 1100m, and then a cabel car up to 1950 meters.

Lovely view - great scenary.


Swiss Mountains part 1


We had a day out in the mountains - There is little to say apart from a massive thank you to Catherine & Jeremy who took the time out of their busy schedules to show us where we should go.

It was absolutly fantastic - the Switzerland I have seen in pics/photos for ages - but I never really believed this existed.



Basel for 24 hours

Rome to Basel

Flying from Rome to Basel was painless - EasyJet were much better than I thought they were going to be - Budget airline but nice place, staff and well organised.

When we arrived in Europort - a weird airport that serves 3 countries - Basel, Feibourg and Mulhouse - we picked up our baggage and went out of the Swis Exit. No hassle - no fuss.

Jumping on the #50 bus - we were in the town centre within 15 minutes.

Basel to Hotel

The internet is good for lots of things, and I like for the ease and reliability of its Hotel services - but it’s directions to this hotel - were V V V bad. We could not find bus #36 - but the Tourist Information Bureau was excellent - and within minutes we were boarding a #11 tram from platform no 5.

/galleries/2015/Swiss/Basel/h1.thumbnail.jpg /galleries/2015/Swiss/Basel/h2.thumbnail.jpg

Hotel Upgrade

We entered what looked like a rather dull and old fashioned hotel - to find a clean, modern interior - with a concierge who was very happy indeed to see us. He commented when he handed us our room keys - that we had been upgraded and he would be interested on our comments.

I think I had asked to be put in a high floor room - apparently the view is good there - so I was a little sad to find we were on the ground floor. The room however was more like a suite…. And I am saying that having stayed in Suites in the Middle East modern 5* hotels now and then.

Evening Walk

That evening as it weirdly does not get dark until 21:15 - we had a walk around the old town - enjoying the feel of an old looking city - at the same time liking the bracing weather. It was cold - for us very cold.

The site of the mighty Rhine was very impressive indeed. Further improved with a large barge passing under the bridge where we were standing.


After getting tired and cold from walking we found a typical Swiss/German style restaurant and had two plates of filling and tasty food.


Basel in 5 hours

Firstly it is not possible to do anywhere throughly in 5 hours - But after a wonderful 8 hours sleep - we checked out of the hotel - and went to find some food - Swiss style. Juliet has never had a soft pretzel and something that looked like a beef-berger as breakfast before - come to think of it neither had I - It was very filling.

We set off to the Cathedral but managed to get lost !!! and instead went to a different church - which was quite pretty but was not what I had planned.

Looking at the map - we re-grouped and then with some deft down-this alley, turn right here etc came to the main Cathedral. Inside it was pleasant but nothing like the sites we had seen at Rome - however I had done some research and I had been led to believe that the views from the towers were worth the effort. There some some warning about small spaces…

After investing in 10 CHF the lady on the desk buzzed the door open for us - and we started up a metal staircase for 2 flights - very easy - very simple. Then the metal switched to wooden stairs and 2 flights further up - we had a slight traffic jam with 2 people coming down. We were able to pass each other standing on a platform bent double.

The stairs carried on - a little steeper and a little narrower. Until standing beside me was Quazimodo - well the Filipino version :)


We went out onto the balcony - and WOW what a view. The sign indicated to carry on around…. opening another door - we went up. This time VERY narrow steps, with a rope as a safety line. Up and up and up we climbed - when we next emerged we were level with the roof.

Here there were some really nice gargoyles looking down on the City. The views were much more impressive.

Yet another door - and yet more stairs - even narrower, even steeper - and we had reached the very top. We could go no more. However the views were stunning.

Once we descended we had one final walk around the town before retracing out steps to the train station.

As we were heading to Berne next we bought return tickets - very quick and simple.

The only shocks in Switzerland so far have been a late train !!!, And prices cheaper than where we live !!!

Avante - next stop Berne.