MySQL – MEMORY

MySQL

One of the things that I enjoy setting up are MySQL databases, its like my anti-drug. I enjoy the feeling of setting up tables, related via “PRIMARY_KEYS”. There is just a thrill to setting it all up, although I can admit the data entry part isn’t always fun if you have a large set of data. I think I started using MySQL databases when I was a kid, and as I grew, learned more and more about them. I will not pretend to be an expert by any means, but I feel as though I have grown over the years to really become familiar with  PHP+MySQL ( soon to include Python + MySQL I hope).

During the Fall 2010 Career Fair at CWRU, I heard about one of the visiting companies, Yelp.com, was giving a talk on MySQL database optimization and use in a large scale environment. I was fascinated by the very though of it. I have always used MySQL for my own personal projects, but have never gotten a chance to see how it would withstand to a large base of millions of users. All the queries, page views, tracking, logs. The database must be huge, and the larger the database, the slower it will become. How do they manage to handle such a load, not to mention the backup servers they must use to prevent the loss of such critical data.

As a kid, I stuck with MyISAM as my default storage engine for MySQL, it seemed the defacto standard, and I didn’t need to worry about transaction locks (unlike in Android, where problems always occurred without them, but thats’ a rant for another time). — So you can imagine my glee when I learned that non-crucial information, could be stored in a very fast table that uses the MEMORY storage engine. Now, this information is extremely volatile, and if the server crashes or goes down, that data will be lost. For some statistical data however, it could be all you need, and the performance benefit would be valuable. If you wanted, you can have a cron job or back scheduled to collect this data at defined intervals. As long as this data is require or crucial to your website, you may want to consider using this type of table storage for your tracking or statistics.

For a list of storage engines in MySQL 5.5, check out Chapter 13 in the MySQL Reference Manual

Privacy Policy