Well, I haven't actually done a lot (or any) blogs before, but I decided that it might be a good idea to do one for my new project.
So I've had this idea for a website since the middle of 2004. I haven't done anything about it because I'm too damn lazy. But recently an opportunity arose recently that I will have a lot of free time....
..... due partly to the fact that my office will be closed down in 1 month.
But instead of being upset about it, why not just find something to do, I said.
But I digress..
The Project
So I have this idea for an online text-based game. The game will be about the cool world of spies and spy agencies.
You can read more about it here http://www.cometform.com/jonathan/sp.html
Discovering MySQL
The thing that I am very interested as of late is MySQL and stored procedures.
(this doesn't have anything to do with the game idea, but stay with me)
I have been interested in databasing for a long time now. However, when I started programing, the database was known to be the slowest part of the whole process. So I always (and I imagine people still do) used to cut the database out of the picture as much as possible by loading things to memory (into arrays for example).
This used to add a lot of lines of code to my programs. First I would have to map (it was a long time ago) my application to the database tables, then load it to arrays and lastly have my program communicate with the arrays.
All in all it worked fast, I would say. It was just very difficult to find problems when you need to look at so many lines of code.
My Current Job
Recently, I have been working for an online hotel reservation company that needed a new search and booking engine. I was hired to take an existing system that was in very bad shape that would possibly be abandoned, fix it, make it usable and then make it go live.
I started off with 200 hotels in the database and it did a search for all of them at 40 seconds, which was unusable. After some time, tweaking the database, I improved the speed greatly and the engine was able to search 400 hotels at 3-8 seconds.
All this by playing around with the indexes a bit, restructuring the tables slightly (not to ruin the application), take the SQL queries and use "explain" on them to see how fast they are.
Now, my company was using MySQL, which is very common for websites to use, however, they were using MySQL 4. MySQL 4 has no stored procedures (or views or functions or triggers).
They were reluctant to upgrade because they didn't want any more problems right after installing a new system that had enough already.
I saw a real potential for studying databases. It seemed to me like the heart of all businesses and if I were to really learn and practice it, I would be able to help other companies (and maybe get employed at the same time).
Lessons I learned
Now during my experience in my company, I learnt something very interesting which seemed to have eluded me.... Databases are VERY good at handling data.
No really, they are!
Why am I mentioning this? because in the past, I was under the impression that databases were the slowest link in the chain. However, I learnt in my last job, that its not the database, but how you USE the database.
So now, I was thinking, why spend time and energy doing arrays and extra coding, when I can just optimize my database, maybe add some stored procedures and have the database manage my data. Its really that simple.
But wait a second... if you move the data to the memory, isn't that much faster then keeping it on the hard disk for the database to grind through?
Well, yes and no. It is faster for the data to be in the memory then on disk, of course.
However, coding all those lines of code, to get arrays, sort them, sometimes call on more data from the database per array item to get more information (thats a different area)... that really doesn't speed things up, complicates things, is error-prone and once you finished it, you don't ever want to touch it again in case you break something.
But yes, keeping it in memory is faster..
In-Memory DBMS
Now, after doing a bit of research to get the database speed up, I came across a wonderful concept called In-Memory Database Management System. Thats basically a database that sits in the memory.
I mean... why not? you have so much memory now on server and in your own desktop. Why not use it?
If you are worried about "is it safe if it stays in the memory?" I suggest you search for oracle's timesten in-memory database (its free by the way) and see howTh they solve this problem by keeping the logs of transactions on the hard disk.
Also, magically, I noticed that MySQL has in-memory tables! "OMG!!11!!oneone"
Thats a table that sits in the memory. MySQL doesnt claim to do too much about supporting the data, in fact they say that if the server restarts, you lost all your information.
Being a bit different
But this gave me an idea. Why not use this memory table as the array or cache that you use in your programs and instead of writing extra code to manage it, just use regular SQL!
Seemed to me like a good idea and to prove it, its the reason why the hotel reservation system is as fast as it is today.
Conclusion
Ok.. so what was I saying up till now:
1) Databases are really good at handling data
2) Databases are not the slowest link in the chain
3) You can use in-memory databases to save all that extra work needed to have arrays or a caching system (well, I do anyway).
But wait.. what about my project? Ok, so I think I've made this blog post a bit too long as it is and I'll continue with my project and how it relates to MySQL in my next post.
But as you can see, I've laid the ground work that you can cut out a lot of code by letting the database be your cache and letting the database play a bigger part in entire system.
No comments:
Post a Comment