Jonathan Levin's Axioms

Cycle of Acquiring Knowledge

2009-09-29T16:47:00.004+01:00

I have been doing some reading into data mining recently. It's really interesting stuff.

To explain it, lets take an example:

Lets say you work in a business that has been working for some years. They have their own way of running themselves and their processes. One day someone comes along and suggests that the business should automate some of their procedures by developing a website that would interact with customers and process their orders.

Some nice PHP/MySQL guy or girl is hired and develops the website and the cycle of acquiring knowledge has unwittingly started.

The diagram below describes how management starts to process its own business knowledge and passes them down the chain so they can be implemented into the application.

Eventually, every transaction is saved in the database. Over time, the database has more and more data and then the management of the company asks to get reports from this data.

Why would they ask that? Because they are interested to get an overview about the operations from this new part of the business.

What they could also benefit from is finding patterns and trends from the data which can help the business increase its own business knowledge. Meaning, data mining.

The diagram below explains the process for that:

Here the data goes upstream and back to the business's management. Assuming they find trends or patterns that help them be a better business and more competitive, they should apply that knowledge into the operations of the business.

Now, I have a question....

Lets say, that the business decided to take the NEW business knowledge and apply it to their website. For example, every person who buys a red hat would also probably buy a green tie (like what 'amazon recommends' does ) or if you come to this landing page, there is an 87.2% chance you are only interested in these lines of products and nothing else.

So they would ask the developers to add that new rule to the website which would in turn make the website better to its users (hopefully).

This means that the cycle went from down to up (like in the first diagram) to up to down (like in the second diagram) and back again. Ergo, a cycle.

And my question is.... how fast on average, do companies repeat the cycle?

How often do they learn from their data lying in their databases and apply it to their businesses?

I'm going to guess around 6-12 months for a whole cycle, but its a wild guess based on no data whatsoever and in the data mining book I'm reading, you shouldn't really guess with no data.

MySQL2GoogleSpreadsheets

2009-09-22T13:52:00.011+01:00

I've managed to find a way to connect MySQL directly to Google Spreadsheets and although that its not yet perfect, it does show a lot of potential.

You will have a MySQL table directly fed into Google Spreadsheets. From there, you could do some charts, highlights some trends or simply share the data as is with our people in our out of your organization is a secure way.

The end result should look something like this:

What you will need:

A Linux Server with Apache and MySQL

A Google Apps Premier account

Google Secure Data Connector installed on your Linux server

SDC

Installing the SDC is a bit tricky but not too difficult and there is a lot of documentation as to how to do it. A lot of it is giving things the right permissions and configuring 2-3 XML files.

You can read more about how SDC works, but from my impressions, it runs on your Linux server and gets data from apache. It then sends that data in a secure way into Google Spreadsheets. So when you query your data, the URL you use is "localhost" which is as if you are on the server.

Apache

Again, its important to note that everything that SDC gets has to go through Apache, which gives you the option of static data (in the form of CSV or XML files) or dynamic data (from PHP for example). It also means that you should secure Apache to not give the whole world this data. You can do this with OAuth (and there are a lot of instructions as to how to secure Apache with SDC in the SDC documentation) or you could try restricting Apache to know that it will only server 127.0.0.1 since SDC runs on your server. Either way, you need to factor that in.

MySQL

The question you must be asking now is, since SDC connects to Apache, how to I get data out of MySQL directly? (hopefully in the future SDC might be able to query MySQL directly)

In order to achieve that, I have used MySQL CSV Storage Engine, which basically means that MySQL will store all the data from the table into a CSV file on the hard disk (I hope that you are already imagining the possibilities here).

All you would need to do is update that table with an INSERT or UPDATE statement and the CSV file gets updated.

Symlink

Now we need to connect that CSV file to Apache. For that, we can use a Symbolic Link in Linux and connect the CSV into Apache by placing the link into the directory Apache reads files from (where ever your index.html file is, usually /var/www/html).

Now another thing to remember is that you need to give Apache rights to see the MySQL directory and be careful about doing that. An option could be to create a new database which would create a new directory on the hard disk and to let Apache see that directory. Do try not to let someone that might possible hack Apache to also hack MySQL, but I will leave that kind of thinking to the security analysts.

SDC XML configuration file

You need to add this symlink as a new rule in the SDC configuration file. You can see some examples there inside the file. When adding a new rule, you need to remember that SDC accesses Apache from the same machine, meaning your URLs should start with http://localhost/

Google Spreadsheets

Go into your Google Spreadsheets and go the to first cell (A1) and type:

=importData('http://localhost/[Your File Here]')

Hopefully, you should see the data from the MySQL CSV table.

If not, you might need to debug it some more.

Problems with SDC

The installation process wasn't as smooth as we would have liked but we did try something new at the time with MySQL. But apart from that, there are 2 problems we have noticed with SDC:

Refresh rates
High number of rows

SDC caches extremely aggressively and when we wanted to make changes to our CSV file, we still saw the old data which confused us through out the process.

A trick to help solve this is, if you goto Google Spreadsheets and in the importData function you change "http://localhost/sample.csv" to "http://localhost/sample.csv?a"

This will force Google to get the new data and you can change the "a" to whatever you like.

A high number of rows (I tried 31k) will get Google Spreadsheets stuck. And when I mean stuck, I don't mean that their server crashes or anything, just that you would wait a long long time to get any results. So you should just show the summarized data you wanted to show.

Conclusion

I am under the impression that this is a relatively new technology for Google and its not 100% tweaked and finalized (it was launched around the end of April 2009). I would assume that it would be good to invest time in structuring it now (meaning, you would be an early adopter), while Google catches up.

UK Offshoring Data-Analysis

2009-08-25T11:31:00.009+01:00

Image by Getty Images via Daylife

TheITJobBoard contacted me and gave me some more data about an Offshoring Survey they conducted in the UK.

From the survey:

65.7% have outsourced part of their IT department to outside the UK in the last 6 months.

Out of the companies that have not outsourced, 3.3% plan to outsource in the next 6 months.

Here is a diagram that describes how much the companies would like to outsource their IT departments

From this chart we can see that people who have outsources more then 51% of their IT departments are quite happy to continue to outsource. While the late comers would also like to outsource a large chunk. And there are those that outsource but very slowly and carefully.

This diagram describes the types of positions that are being outsourced.

As you can see, the developer/testers and IT/Systems support are the jobs being "commoditized" the most. It is important to mention if a company is intending on outsourcing managerial and analyst positions, they fully intend on outsourcing the whole spectrum of positions.

The positions from the survey that are "safe" from being outsourced were: IT sales, IT training and IT consulting which accounted for 3% of the total positions being outsourced.

I wanted to show a trend with outsourcing certain positions. Here you can see that while developer/testers and support jobs are the largest part of positions that companies want to outsource, in 52% of cases, they would like to outsource both types of positions at the same time.

This to me shows that companies are interested to outsource whole parts of their IT departments and not just individual positions.

Conclusion

According to this survey, offshoring is due to increase in the UK.

I would not like to argue over the effects of this in the economy.

I would like to compare it to the manufacturing factories a few decades ago, which were all offshored to (mainly) Asia. Perhaps this might be a new trend with IT.

Life After Redundancy

2009-08-05T20:25:00.004+01:00

Image by Getty Images via Daylife

I was made redundant about 2 months ago. The reason why I was made redundant was because my previous company cut slightly under 30% of its staff. Apparently, online retail companies are not doing so well and my former company can add to that statistic.

Personally, I thought that letting go of so many people was very risky. A lot of expertise and system knowledge was lost instantly, particularly in IT. In addition, there had been a few redundancies in the previous 6 months that forced the remaining people to do other people’s work. The question remains, is it possible for a company to be able to respond to the market with a skeleton crew that mainly maintains the existing systems.

Perhaps this is a feeling that everyone gets when they leave a job – “They won’t be able to last without me!”, but the truth of the matter is, they probably will.

As for me, I ploughed the unsympathetic fields of job advertisements. I wasn’t particularly encouraged. My main gripe was that some 8-10 months ago, even if I only had half the experience the companies were looking for, I would still be considered. Now, you basically have to have 125% of what the job requires in terms of experience which necessarily means that you need to lower your asking price. There really was/is a lot of very skilled and experienced people looking for work and you get the feeling that they have been waiting in line a lot more then you have.

There is a happy ending to my story. I did manage to find a job and I now work for a search engine marketing agency. This would mean that I would get to work with a lot of data on a day-to-day basis and be able to learn more about statistical analysis and data mining.

What I took away from this experience, is to be careful of small and “exciting” companies, because they are the first ones to get rid of people when things turn bad.
The other thing is if you are spending a lot of time at home, then spend it with your wife.

DBA Salaries in the UK - TheITJobBoard

2009-07-24T15:27:00.006+01:00

TheITJobBoard published a report for 18 different types of IT jobs and what salaries they can expect in different part of the UK
I made my own analysis for DBAs using the pandora software inside google spreadsheets
(Please note, that DBAs positions in this survey include Oracle, MS SQL and MySQL DBAs)

DBA Salaries in the UK

View more documents from JonathanL.

Original:

The IT Job Board Salary Survey

View more documents from TheITJobBoard.

Oracle, Sun and Interesting Possibilities

2009-07-22T12:41:00.005+01:00

Image by Getty Images via Daylife

Sun is now/soon-to-be part of Oracle and I am extremely concerned about MySQL. There has been reassurances from the community... and I believe they could probably work.

I was wondering however, about the hardware side of Sun. Sun has always thought that by giving something for free, you encourage customers to buy something else from the same company.
They put that model to work with open-source software like MySQL and hoped that customer will buy their hardware servers.
Oracle hasn't had a hardware side (as far as I know of) and I was wondering what they would do with their new acquisition of Sun's hardware.

It occurred to me that Oracle could be in a position to develop an SQL-chip, like that on kickfire and sell a "Turn-Key" hardware solution to its customers. One that they will control and be able to support from the ground up, probably including the operating system.
It would make sense to me that Oracle would invest in hardware that will improve their database's performance and even more probable would be to sell it as a whole to customers.
In addition, if it would be something like kickfire's SQL-chip, then Oracle would increase its position in the BI world and compete with vendors like netezza and teradata.
Ultimately, it would provide a one-stop-shop solution to its customers and probably decrease drastically the combined cost of purchasing from several vendors.

My prediction.

Google Fusion Tables and Publicly Available Data

2009-07-16T02:01:00.008+01:00

Image by Getty Images via Daylife

I had a thought. What if Google's Fusion Tables could host in the future all the publicly available data like the UN data and then allow people around the world to view and analyze it.

What if the point was to have people make sense of the data for you in a collaborative way so that the publisher benefits with free insights into the data and everyone else benefits from these insights as well.

To fully appreciate what I mean, please watch this really good TED video about visualizing data.

Free Business Intelligence - Come Get Some

2009-07-16T01:33:00.006+01:00

Image by dannysullivan via Flickr

Apparently, Google and Panorama Software has been giving away free analytical and data-mining capabilities within Google Spreadsheets for a while now.

So to spread the word, here is the tutorial page:

http://www.panoramasoftware.com/google/pivot-table-overview.html

and here are 2 interviews discussing the product:

http://www.kyte.tv/ch/6118-scobleizer/302155-part-i-of-changing-business-intel

http://www.kyte.tv/ch/6118-scobleizer/302179-part-ii-of-changing-business-inte

After reading those, if you ask yourself "but how can I upload data to Google Spreadsheets?", you can use Google Secure Connector.

Share and Enjoy.

Business Intelligence for the People

2009-06-16T18:33:00.010+01:00

Business intelligence has been talked about for quite a while. Even today, while companies are looking to make budget cuts, some experts are saying that BI can be used to beat the recession.

When I hear about BI systems, the first thing that comes to my mind is a huge and expensive system that has very powerful servers, that sucks data from many sources and runs some intensive and even more expensive reporting suite. Since I had been involved in projects to set those systems up, I know that it can probably take around a year to complete.

So everyone is in fact thinking about saving money yet still being competitive and aggressive in the market. On that subject, there are very popular and stable open source BI suites and similar products (kickefire, scaledb) to achieve both those goals. Before you deploy some “black magic BI”, because that’s what the big companies are doing, there are some simple things you can do to get valuable information from your data.

Assuming your data is in one place, you can create reports using standard SQL that will benefit your company and make them more competitive. To help with that, I have compiled a list of relatively generic reports that most companies might/should be interested in. There is a good chance that after making these reports, the management will see that you actually have some business knowledge and might ask you for more specific reports. If that is the case, then consider the information you have delivered to be a success.

Sales

What is the total number of placed orders?
What is the total number of orders that were shipped or invoiced? - When a customer places an order it is not always the case that the products are in stock.
What is the total amount of money from placed orders? – Sum of order value. This can be with or without shipping costs, depends on the management.
What is the total amount of money from orders that were shipped or invoiced?
What is the total number of returned/cancelled orders?
What is the total amount of money that was lost from returned or cancelled orders (or credit notes)?

These reports should be for specific period of time. For example 1,2,6 and 12 months or 1,7,14,21 and 28 days.
You can compare this month to last month’s results or this month to the same month a year ago.

These reports can also be grouped by:

Product types or product ranges
Geographical areas (city, country or continent)
Customer matrix (teenagers, elderly, soccer moms, university students..).

Products (Best Sellers)

Which products sell the most quantity?
Which products generate the most revenue? – (product price * quantity)
Which products generate the most profit? – ((product price – product cost) * quantity) – To make your business more profitable, you should invest your energy in the 20% of the products that generate 80% of the profits.

Customers

How many new accounts/customers do we get (each day, week, month, half year or year)?
Who are our best customers – that buy the most from us? - The top 20% that make 80% of the profits.
What is our attrition rate – the numbers of accounts/customers we lose (each day, week, month, half year or year)? - If your attrition rate is high, the sales team needs to find a lot of new people just to keep things the same.
What is the customer satisfaction? – You will probably get this from surveys or customer feedback.
What are the top reasons for complaints?
How many existing customers referred new customers to your company? – You might need to setup something to collect this data. If you send your customers to do your marketing for you, you can save a lot of money.

Website
(Some of these reports can be handled by a separate application like google analytics)

Which product pages get the most views?
Which news/blog articles get the most views?
How many minutes does the user spend on the website?
What is the percentage of abandoned shopping carts?
What is the average amount of time a customer spends in the shopping cart/session?
Most important – What is the conversion rate of the visitors coming to the site and the people who purchase a product?
Which landing pages are the most viewed?
Which landing pages have the highest conversion rate?

Final Words
I genuinely hope that this list of reports will help you influence your business and your position in the company for the better.

I would very much like to welcome ideas for new reports or comments on current ones if they are unclear. Your feedback is appreciated.

Tired of Youtube? Try the hacked version

2009-06-14T14:01:00.008+01:00

Tired of not watching the latest music videos on youtube like the way you used to before? Frustrated at the live performance or unplugged version of the song you wanted to hear (I actually like it that way, but anyway…)? Well do I have a solution for you!

China has their own version of youtube called YouKu.

We all know for a long time now that in China, they copy everything and have very little or no respect for copyright laws. This sometimes upsets us (check out StackOverFlow vs CNprog), but here is a chance for us to get something back.

Go there and put in the search a name of a particularly famous/infamous blonde singer and see all the lost or missing music videos from youtube.

If you are lucky, you might even see full movies on it.

However, it might be “a bit” slower then youtube is. Sorry.

Caching Business Logic in the Database

2009-05-30T23:19:00.006+01:00

This is a presentation I gave at the Cloud Expo Europe in London on the 21st of May 2009.

I speak about:

Caching the results of processed data - data that had business logic applied to it - to the database to be re-used later.

Caching Business Logic in the Database

I Will be Speaking at the Cloud Computing Expo

2009-05-07T15:45:00.005+01:00

As the title says, I will be speaking at the Cloud Computing Expo.

And while I have spoken before to a bunch of people before, this is by far, a lot bigger.

The topics I was thinking of talking about was a piece from my previous presentation which would be about speeding up MySQL for a web site developers.

I have edited those parts of the presentation and uploaded then to youtube.

They can be found here and here.

What I'd like to talk about is a mixture of the posts I had about Storing Conditions and Using Business Rules in MySQL. The idea, as a friend of mine put it is "Caching of Logic" in the database in order to save reprocessing data.

I hope that I am not very vague and I guess that I will need to make it clearer. But for now, I will start preparing the presentation (and I am quite excited about it).

Slave Replication Errors

2009-04-17T02:00:00.005+01:00

Image by MethoxyRoxy via Flickr

I had a problem with slave replication, every now and then we would get an error which manually had to be resolved

We used Set Global sql_slave_skip_counter = 1; start slave;

You might also get some of those when you set up a new slave using a mysqldump. If from the time you started the mysqldump and by the time it finished, some lines were added to the bin logs or you started it at the wrong position, then you would get some duplication errors.

While it looks concerning, don’t panic. You can fix it by skipping the lines that are duplicates, although it’s not an ideal situation.

Now if you want to completely avoid errors, or specifically duplicates and you are sure that the odd ones that come along are not important, then why not try log-slave-errors=1062 . Even better, you can set it to “all” and be completely oblivious to any problem that comes along.

Every now and then you can check the log errors and if there are too many problems, get a new mysqldump to re-sync the slave.

Speeding Up MySQL for Website Developers

2009-03-15T14:41:00.008Z

I gave a presentation for PHP West Midlands (UK) about how to speed up MySQL for people who are primarily website developers.

Now the audio for the presentation could have been better. I speak very fast for some reason. I am foreign, so I have an accent. And to top it all off, I have some mistakes here and there.

If this still hasn't discouraged you, please watch the video

Follow Up - Question of Speed and Technological Progress

2009-03-07T00:20:00.016Z

Image via Wikipedia

Well, the servers we bought (Dell R200) were in fact very cheap.

We were using 2 of them and they could not stack up to 4-5 year old servers (Dell 1750 and 1850) which have SCSI hard disks.

I added a 4 year old Dell 1850 with SCSI disks next to the 2 R200's which were all connected to a separate server running pound load balancer. The total number of maximum concurrent connections tripled.

The Dell R200's cannot actually take SCSI/SAS drives (unless you go buy a proper controller). By default, they can only take Nearline-SAS which I heard is not something you would want for a web server.

I would have to say that I am putting the blame on the SATA drives. I would have thought that by now, SATA drives would be as fast as old SCSI drives, but I guess not.

Apart from that, I am still surprised that the new R200's with EIGHT Gbs of ram could not keep up (not even close) with much older servers.

What I'm looking into now is using squid for web accelerating. There is a good article explaining how to install it.

While I can plainly see that the servers we chose were not amazingly fast out of the box, I'd like to believe with some tweaking and using more of the memory, they could get close to the older servers.

The final point is, that it costs man-hours to do any tweaking to the new servers. If you calculate the cost of the man-hours you would be better off just buying a faster server.

How to Move Your Web Server With No Downtime

2009-03-03T23:04:00.009Z

How do you move your web servers to another location with no downtime?

The answer: the magic of MySQL Master-Master Replication

So, we are in the process of moving our web servers to another location. As usual, we would prefer to move them without anyone really noticing. What we have done is set up a skeleton of web servers in the new location as well as one database server.

Now the trick is, to move without anyone knowing it AND that everything else continues working. In this example, the original location has servers that query the database if there are any new orders and process them. So it is important to keep all the parts of the system running by continually updating the database in the original location. So in this example, if you point your DNS at the new web servers and don’t update the old database, you will basically lose money.

So, what do you do?

Well firstly, you should set up a secure and stable connection between the two locations so that the databases can communicate with one another. An SSH tunnel, will do the trick. You can set them up on the actual database server on a certain port, and it will seamlessly connect to the other database server as if it’s on your local network.

Secondly, you set up a Master/Master Active/Passive replication. This is a replication set up which is usually used for high-availability. You can also use Active/Active replication for load balancing if you take into consideration that auto-incrementing numbers would conflict on both servers. There is a solution for that as well.

On the day of the move, tell your web servers from the original location to query the database in the new location through the SSH tunnel. This will slow them down, but it should be only for a few moments.

Change the DNS to point to the new web servers and that they are pointing to the new database server.

Now you have your web servers in your new location and all the other servers and services running from the updated database in your old location.

All that’s left is to move the other servers and complete the entire move.

Job well done! You have completed the move with (hopefully) no downtime and no orders were lost. Your boss(es) will definitely be pleased with you.

A Question of Speed and Technological Progress

2009-03-01T01:41:00.010Z

Follow up post to this one

At my company, we have some really old web servers that host our website. They are from late 2004 and early 2005. Now, at the time, I am sure they were quite expensive.

Recently, we wanted to move to newer web servers and we bought the basic Dell R200 rack servers. Now, arguably they are quite cheap, but thinking about it they will definitely be faster then 4 year old servers, right?

Well, that’s what my predecessor thought when he bought those servers and I myself didn’t think about it till we wanted to start using them.

We noticed that the new servers are at least twice as slow as old servers and that just completely baffled us. No one saw it coming. I mean, the old servers are optimized and tweaked and the new servers just have an un-tweaked but clean install of Ubuntu server with apache.

So what’s going on exactly? How is it that when hardware is so cheap and supposedly so fast, that we now have to spend some time going into configurations, messing around with things, benchmarking and basically spending man hours on something that is supposed to be quite basic. I mean, I even used my cure-all method and threw some memory at the new servers to get them to 8Gb and it still didn’t work.

Anyway, so now we have to start spending time on speeding up the servers. Things that have been suggested were:

Removing un-needed modules or parts from the linux kernel and recompiling it. Although, I’m not too thrilled about that.
Tweaking Apache Config
And my suggestion which was put everything in memory with TMPFS. Again, I like memory, I’m from a MySQL background.

From my experience, PHP isn’t usually the bottleneck. So I need to look at the whole puzzle to see where the bottlenecks are.

For my part, I have looked at the MySQL server and we also monitor it with Nagios. I am not seeing any particular slowness from it and Nagios isn’t telling me that its being overused, but I guess I could still run some benchmarks on it.

Anyway, it’s a big mystery to us why it is as slow as it is compared to some 4 year old servers. Granted, the servers we have now are quite inexpensive, but we are still very surprised.

Conclusion

Moore`s law doesn't apply to web servers and cheap hardware will always be cheap.

I’ll leave some links that I found recently and we will try to use them to speed up the web servers.

Slashdot Effect

http://www.linuxformat.co.uk/pdfs/download.php?PDF=LXF97.slashdot.pdf

Apache Tweaks

http://www.askapache.com/htaccess/apache-web-server-speed-configuration-hacks.html

TMPFS – Linux Memory Partition

http://www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs

http://kevin.vanzonneveld.net/techblog/article/create_turbocharged_storage_using_tmpfs/

Follow up post to this one

What the ‘Microsoft’?!

2009-01-28T00:47:00.010Z

I’ve had about enough of Microsoft and its approach to help small businesses develop application. The .NET platform is FAR FAR from ‘working everywhere’ and being compatible to everything. Let me write down some points to what I mean.

Windows Applications that are developed in Visual Studio.NET by SMBs usually (almost always):

Do not work on other machines, even when its .NET. Don’t assume a .NET application will work on every machine. You need an installation of visual studio for each machine you want your application to work on.
Do not work with Crystal Reports. It might work “ok” on your computer, but if/when it doesn’t print on someone else’s machine, try to find some support.
Stop working when you update windows. Update at your own risk
Randomly disconnects from the network or has DNS problems. From the words of my system administrator “its being windows”.
Randomly doesn’t connect to the database. See above.
Locks up the database with all the open connections. That’s what happens when you distribute fat clients to everyone.
Crashes when some windows services mysteriously stop working.
Have issues when being deployed and make you click 10 ‘OK’ buttons just to open it. I do not know why, but someone previously decided to have a shared network drive with the .NET files on it, so that we can update one place and everyone goes there to run it (every single time). But try as they might, they still needed to put some config files on each machine.
Have a low learning curve which lets idiots make decisions on software architecture which afterwards are too complicated to change.

My personal opinion is that the Microsoft lock-in, is the problem. It starts off with

Oh yes, its so comfy and easy using Excel sheets
Oh.. lets write some functions using my macros
Now lets try some VBA/VB6
Hmm… we need a database… oh.. lets use Access.
(A year goes by….)
Hmm.. we need something more serious, we have more people using our system…. lets call someone and ask what we need…
Salesman: “yes, what you need is first of all 2 Windows servers, one with a domain controller/Active Directory and one with MS SQL database. Then you will need lots of CALs (licenses). If you are already there, you might as well use Exchange too. Then you need 2-3 copies of Visual Studio.NET team edition for your small development team. You know what?.. I think you will need a BizTalk server as well, just to be on the safe side. Oh.. and finally, installation and maintenance fees will come to ££££££££££££££££££!”.
Managing Director *gulp*.

Maybe nowadays this scenario may not be so common and more people would be happy cutting down costs. Perhaps by using MySQL instead of MS SQL. However, then you have interfered with the natural order of the way Microsoft wants things to be.

I don’t know, obviously this is a rant because I’m frustrated about some things. I guess I am glad I decided to do something about it.

Caching and Actually Speeding Things Up

2009-01-18T17:50:00.010Z

Image by Peter Forret via Flickr

I've ran into many people throughout my career that were under the impression that caching is a cure for cancer, world hunger and global warming. I myself often fall pray to the "just add more memory" approach for solving problems.

Some 5 years ago, while I was working with ASP classic and VB 6 (yes, you are allowed to chuckle now) on my online movie collection, I came up with the idea that nothing actually beats a flat and pre-processed HTML file for speed. I mean, this may seem obvious and no matter how many times I tried to add cache and do all sorts of tricks, nothing would beat a static file. The problem here is that when I suggest this sort of solution, people would almost always answer that "well, we have caching anyway" and that this sort of solution would actually be going backwards.

Now granted, you cannot use static files for most dynamic pages (static != dynamic) and maybe this would never be a real long-term solution, however lets just consider these benchmarks for PHP and Ruby Frameworks. No matter how many different types of caches you apply to your framework, you will never actually beat HTML files or baseline PHP files for that matter. But the point is, that going back to basics beats frameworks by an order of magnitude.

This reminded me of something I wrote a loooong time ago about event-driven publishing, when something is updated in the database, the webpage cache of a website gets regenerated.

This also reminded me about my thoughts about storing conditions in database tables, where you can save pre-processed results to the database to save you re-processing them.

Conclusion

Well, not so much a conclusion, but an option. Consider making the parts of your website that do not change very often, into static files if you are very concerned about speed. Have your system generate these static files every time they need to be updated.

Developing Internal Business Applications with PHP

2009-01-12T13:44:00.005Z

Image via Wikipedia

Recently, my company has decided to go full (or mostly) open source by moving away from Microsoft technologies. This is a particularly exciting venture to be part of, but in order to explain what I mean, I need to go back a bit and explain the history of the company.

History

Since its inception, the company has been a traditional LAMP stack. Over time, some internal business applications were needed to be developed. The initial platform to solve this requirement was Microsoft Office and specifically Access database and some Excel sheets. Later on, more and more VBA programming was added to the Access database. At some stage, a decision was made to move to VB.NET and port the VBA/VB6 code to it.
So far, this story is not that unique and I am sure many of you reading this can either relate to it or know of examples from other places.

There were issues moving to VB.NET from the start. It took over a year to finally move to it with many complications and a lot of resources invested in it during that time. After some time, the resources depleted and 1 developer was left to maintain the system.
With time, the application stagnated and the 1 developer could not keep up with the bugs and requirements for new features.

The Present

At this stage I came into the company and I decided that some serious work had to be done to improve the internal business application. I had made a plan to fix and improve the existing code base but saw that it would take a considerable amount of time and energy as well as expertise that we did not have.
At this point, I took a look at other parts of the company. The company has a very good history with open source products. The relatively new PHP team was accomplishing quite a lot of things. All the servers are on various flavours of Linux and maintained well.

The only parts that were holding the company back were closed source technologies. Specifically, incompatibilities between 3rd party applications, printers and forced networking standards that come with closed source. The cost for .NET developers was also an issue.
It seemed to me that the culture of the company is more aligned with open-source and ultimately, that would be the direction the company should follow. The only problem was that, because of initial convenient and easy-to-use application, the company was locked in to a now costly and constricted system.

The system that was chosen was PHP. From one point of view, PHP was the obvious choice, considering the expertise the company has. From another point of view, selecting a web-based technology to replace fat clients with rich GUI, isn’t the most obvious choice.
Considering all the different points (and after being grilled by the executives a bit), the decision was made mainly because of our strong history, achievements and expertise with PHP and Linux. The difficulties that PHP might have to replace the VB.NET system were researched and solutions were offered.

The Official Reasons to Move to PHP from VB.NET

To cut down costs and unify technical support expertise. It is difficult and costly to support multiple systems technologies.
To reduce complexity and speed up development time. The company has had a lot of success delivering products and features with PHP and believes that this success can be replicated for the internal business application.

Full Steam Ahead

When making this system it is very important to make sure that the system will be very stable, maintainable and flexible for future changes. It would need to prove that it can overcome the difficulties that the old system had troubles facing. After that, development speed is crucial as all new features will have to be put on hold until all the system (or at least some parts of it) is rewritten.

So the important factors were the quality and flexibility of the code as well as speed of development. I decided that in order to achieve these goals, we would need to use frameworks, tools, add-ons and coding standards. The idea is to leverage existing open-source products and best practices to save time and ensure quality. It is also important to make sure that by using the same tools and following the same coding standards, the code will be maintainable for many years to come.

The List of Technologies

I have compiled a detailed list of technologies, open-source products, frameworks, tools and coding standards we plan to use to get this project done properly. All of these are supposed to lay the foundation for a rigid and structured development process.

LAMP Stack - PHP 5, MySQL 5.1 – for improved replication and partitioning, Ubuntu Server and Apache Web Server
Zend framework – This is something the company has chosen a long time ago and it’s something we plan to continue with.
APC – PHP accelerating to speed up bootstrapping.
Memcached – To speed up database interactions. We came to the conclusion that memcached sped our whole system by a factor of 40.
Doctrine ORM – I didn’t want us to spend a second of a developers time on mapping database tables to classes, so I wanted an ORM. Doctrine seemed to have a better website then Propel, so we chose that. So it’s literally a case of judging a book by its cover.
YahooUI Javascript Framework – After my developers being biased to jQuery and I was biased for ExtJS, we decided to choose YahooUI as it was somewhat close to the rich GUI that our VB.NET uses now. We chose this framework to try to keep javascript development to as low as possible by using already made widgets and libraries from the framework.
PHPUnit – This is one of the important parts of the foundation we need to start building the new system. If we cannot have some guarantee that the system is stable then what is the point of the whole thing.
phpUnderControl with CruiseControl (Continuous integration server) – “Continuous integration describes a set of software engineering practices that speed up the delivery of software by decreasing integration times”. phpUnderControl uses PHPUnit, PHPDocumentor, PMD, PHP_CodeSniffer and CheckStyle.
Subversion SCM – We obviously, need source control management and we are already familiar with subversion. We had considered Git, but didn’t choose it in the end.
Zend Coding Style – The development team decided that in order to organize and standardize their code, they want to use the Zend Coding style. I also understood that it will later help with phpUnderControl with its style checking.
Scrum with ScrumWorks – We use Scrum and we find it very effective. The application we use for it is ScrumWorks Basic. Even though we don’t think it’s the best one, it works for most cases and is quite convenient.
Selenium with PHPUnit extension – for front-end testing. Selenium can test front end pages/forms and javascript functionality. Selenium can be called from PHPUnit which will fit with the automated testing suite.
Acceptance Testing for our User Stories – This is a standard we liked. We like to specify with the user stories the acceptance test or success criteria for this user story to be considered done.
Fat-Models, Skinny Controllers – A standard we are all in agreement is a very good practice.
PHPUnit TestDox for documentation - I wanted the tests to be the actual documentation. All the requirements and behaviors of the system should be the actual tests of the system. PHPUnit has a way to extract the test name and generate documentation for them. We will also use PHPDocumentor and Dokuwiki for anything else.
SOAP/REST– We will use SOAP/REST for any component that might need to be called from another location. With all the talk about SOA, I decided to just implement the minimum use of it, which is to use it when other locations need to use this functionality. This is instead of having the code in 2 places and both try to get data from the same database as the point of integration.
FireFox/Prism - We can dictate which default browser we plan to support. Prism is also quite a cool application to make our application look like… a real application.
Validations for HTML and CSS – Even though we don’t need to make our internal business application SEO friendly, it still might be a good idea to run some validation on it.
LDAP (linux) - We though it might be a good idea to have this for user logging and authentication, but the brightest idea would be that we would then know which printer is closest to that user.
Nagios for Web Page monitoring – We can monitor if the website is up and get advanced warning if something is wrong.
Log4php – a ready made logging library ported from java.

Conclusion

We are all quite pleased about the decision and look forward to start working on this project. I hope to learn as much as I can from this experience and I am sure the development team feels the same way.
I strongly believe that PHP with the addition of other open source products can deliver the same if not better results then VB.NET/ASP.NET would and I plan to put my money where my mouth is.

For writing this article, I did some research into Enterprise PHP, but did not find too many (current) articles about them. I hope to start seeing a bigger trend towards Enterprise PHP applications
I will keep bloging about the project when interesting things come up and I keep the right to update the list I made in case we decide that some parts of it are not as good as we thought

New Year's Resolution

2008-12-31T15:16:00.003Z

Its been a while since I blogged.. and my new year's resolution would be to blog more.

I could say that I have been very busy at work. I can't really say that I haven't been using MySQL, since I've been using it more these days than I have in my entire career.
I could say that I am noticing a trend to blog less in the MySQL community. I could be wrong about that, but I definitely don't see the same names blogging as the ones I used to see a year ago. But that could be that some people just moved on.
I could say that I have just been lazy. My blog statistics certainly reflect that.
Finally, I could say that I lost a bit of enthusiasm for blogging about MySQL. This could be that my priorities have shifted slightly, but I plan to get myself a bit more involved with database developing/administrating in the very near future.

Other things on my list for the new year is:

Re-learn the PHP that I forgot
Learn Subversion branching
Learn Git
Learn PHPUnit testing <- almost done
Learn how to use CruiseControl or PHPUnderControl
Look into ExtJS (for a Intranet Project)
Learn Ruby and/or Erlang in my free time......

Reporting SpeedUp Trick

2008-12-14T16:08:00.014Z

I've been asked to make some reports recently for some people in my company.

Almost all of them are quite heavy and hit the database pretty hard.

The thing is that these reports are usually generated once or twice a month, but they process a year's worth of data. Every time they are generated, they really upsets my poor production MySQL database.

I was thinking if there maybe could be a better way to just save the processed data in a table and update it every now and then.

So.. this is what I did:

I created a table that keeps the aggregated results from the report.
I added to the start of the SQL statement "INSERT INTO reports.thistable SELECT (the worlds most intensive SQL query) ;"
At this point I realised that I need a bit extra help determining which parts of the data should be updated and which is already in the table. So I added an "last_updated" column in both tables with a current_timestamp.
I changed the INSERT INTO statement to an INSERT.. ON DUPLICATE KEY so it can add to the previous amount in the aggregated table.
I also made sure that I added more primary keys to the reports table so the statement will know which fields are in fact duplicate keys.
Lastly, I added: WHERE filter the data table's timestamp column by -> (SELECT the last timestamp on the reports table). So basically, you are (in theory) only getting fresh data to update into your reports table.

This is fine for tables that only have data inserted into them. If existing rows get updated, you might need to use a trigger.

What I plan to do is to give an option to clear the whole report table if the user doesn't trust the data. However, in the future, I will limit that query from running during certain work hours to avoid upsetting the production database.

Prepared Statements Killed My Database

2008-12-10T20:15:00.004Z

In two separate instances, prepared statements used up all my database's connections and completely locked it. To the point that we had to restart the mysql server and in the other case, physically reset the server (not something you usually do with linux servers).

Apparently, one table had a read lock on it, which made the prepared statement wait a bit too long and it decided to flood the database connections till it would got an answer. Only the table was still locked and the database came to a screeching halt.

The last thing I saw on my screen was a bunch of the same prepared statement on my process list screen, completely taking up all the slots and then.... I lost my connection to see the process list.

I don't really understand how the perl script and php page that ran the prepared statement could be so aggressive or where was it defined to continue opening new connections till they got an answer.

This is definatly a first for me and afterwards I changed some of the tables to be more transactional, so that they wont lock.

Conclusion... well there is no conclusion. I'm not going to rule out using prepared statements, but I am going to look more into their behavior (once I find out where they are).

SSH Tunneling between databases with MySQL

2008-12-04T12:32:00.007Z

Dear Lazyweb

I saw recently an interesting installation at a company. This company had databases in two different locations and they established a connection using SSH tunneling.

The thing that looked very interesting to me is, they setup a dedicated machine that had a MySQL instance on it. That database was mostly empty, but it had a port open on that machine that is a "virtual" tunnel. This allowed you to connect to the databases in the second location but it seemed as if the database is local to the network.
I believe this opens up a SSH tunnel and from my first impressions, it appears to be the case. My question is, how is that setup?

MySQL Function to Change Hard-Coded Tax, Anyone?

2008-11-24T21:42:00.004Z

In the UK, tax prices have gone down from 17.5% to 15% .

Good news!

However, its a bit of a nightmare to change the prices on our websites. We're lucky. We don't have to reprint thousands of pamphlets that have prices for Christmas sales, since we are a primarily web-based company.

My boss said that this is like the Y2K bug, only you have 1 week to fix it.

Basically, we need to go over hundreds of pages of code for some 'very' hard-coded tax figures.

Our databases will also need to have a look at. I might need to have an all-encompassing function to look for products with tax already calculated into them and update them.

Oh well, it looks like it will be interesting.

Wish me luck.