Corrado Pandiani is a MySQL and open source enthusiastic since 2000. He's a long-time Perl developer and designer of web sites and on-line applications. He's MySQL 5.0 Developer/DBA certified and currently is working as web project manager/DBA at Football Club Internazionale Milano, one of the best football (soccer) club of the world. He blogs about MySQL and his life at http://blog.pandiani.com/category/mysqlen/.
If you are a football (soccer) fan, then F.C. Internazionale may mean something to you. Or perhaps its more common name, Inter Milan sounds familiar? Next in our Use Case Competition articles, here's Corrado Pandiani's experience, about using MySQL to power inter.it, one of the most popular soccer sites in Europe. Not only that, but inter.it is using the latest MySQL 5.1 technology.
The Inter Milan squad in 2008/09 features stars such as Zlatan Ibrahimović, Luís Figo, Hernán Crespo, Marco Materazzi, Patrick Vieira and Adriano Leite Ribeiro.
At F.C. Internazionale Milano Spa we've used MySQL for eight years, starting from 3.23 and up to 5.0. Recently, we have started using satisfactorily the partitioning and event scheduler features of MySQL 5.1. The tasks that most benefit from the new features are:
In the past, we logged web accesses to a daily log file, which exceeded 2 GB in size. For us, this is a huge amount of data. Now, the data is stored directly in a partitioned MySQL database.
A year ago, when we thought about logging web accesses to the database, we were limited by issues such as:
We found that partitioning in 5.1 was a very useful feature for us. We use it to keep online various months of data and to calculate statistics in an optimized way.
We have developed (and we plan to further develop) a series of stored procedures to generate advanced statistics daily, hourly and monthly. These SPs are invoked using the event scheduler instead of using the operating system cron.
We developed a short Perl script to log page referrers of a hidden image from every web page, and to calculate counters in real time.
When the web server starts, the script creates prepared statements with all needed INSERT queries. At every page request, the script generates a set of user variables with all needed values and then EXECUTEs the prepared statements.
The webtraffic script is piped to Customlog configuration of Apache server.
Creating partitioned tables is very simple and the MySQL documentation on the partitioning feature is quite clear. The advantage of using partitioned tables is performance: All our scheduled statistics are computed very quickly. The daily statistics finish in less than 3 minutes, and the monthly in less than 10 minutes.
In the past, using a 5.0.xx version and not having partitioned tables, we had to wait a longer time to compute daily stats. In fact, the daily run was so expensive in terms of execution time that we simply gave up on the idea. Thus, we don't have comparison execution time on more complexstatistics.
Now, with partitioning, we run our daily stats with no issues and even expect to be able to further trim our response times. Notice also that our server is not at the moment a very powerful machine, but for our tasks we amazingly discovered it has the needed power.
To show the kind of traffic we deal with, we can see the table of average time of visits and most active countries for all sites (3 september 2008)
or the top pages stats for the most visited portal www (3 september 2008)
or the daily stats for all sites (data referred to 3 september 2008)
or the hourly stats for the most visited portal www (3 september 2008)
Attached to this article you can find a compressed file with some scripts:
Read and post comments on this article in the MySQL Forums. There are currently -1 comments.