SQL – Always use aggregate functions if you can

One would wonder what the title means. Well it was a thunder bolt for me when I was trying to optimize some headless scripts. Well we at Saturn do heavily use headless scripts written in php. Some use mysql some use xml and some other use memcache, in fact pretty much all would use memcache. But that is not the situation now.

In an attempt to multi thread a cron job part of optimizations done an year back, instead of sequential processing, we had switched it to single row processing. Which required to have a method getNextRow which was passed a parameter, the primary key of the table. The cron starts with a value 0 (zero) and after each is processed, the value is supplied as the last processed one. The getNextRow had

select ID from [table] WHERE ID > [LAST_ID] ORDER BY ID ASC LIMIT 1;

Continue reading “SQL – Always use aggregate functions if you can”

url shortening script using MariaFramework

Just as a proof of concept, we had tried to port or even rework the whole of asianetindia.com using MariaFramework. The task of migrating the wordpress admin side, being herculian, this was delayed in pushing to production and it is still in poc stage. Now to nail down the fact that MariaFramework is production ready, we need to show off some generic applications. Here comes one, though the commenting in the main application is poor, I hope one can implement this and get running with out much headache.

Download the full script Download

Submit all your comments and views as posts to MariaFramework

MariaFramework 0.22 released

The MariaFramework or phpmf has been released with a couple of new enhancements. The MariaFramework portal is updated with some new plugins for phpmf. Will dive into what the details of the plugins are in another post. For the time the enhancements of the all new MariaFramework.
Continue reading “MariaFramework 0.22 released”

How I got MySQL 5.5 running on Ubuntu 10.04 Server

After referring to all sort of documents online, for getting the src deb and compiling, which always created one or other method of headaches for me, we approached the Oracle recommended method. Downloaded the binary from official oracle downloads. In the actual process I just deviated here and there to suit my preferences. Untarred the binary to /opt/mysql-5.5.15-linux2.6-x86_64, soft linked to this from /usr/local/mysql.

After creating the /etc/my.cnf to my desired settings, I proceeded with the mysql_install_db, which promptly failed complaining about a libaio. Again a bit of googling, found the answer ‘apt-get install libaio1’. Okay the install db went straight. Now the startup script. Copied the script [mysql-base-dir]/support-files/mysql.server to /etc/init.d/mysql. Issued update-rc.d mysql defaults. All was well, the server started smoothly. But Ubuntu did not have path spec for finding the binaries, so did the easy way ln -s /usr/local/mysql/bin/* /usr/bin/. Since we were using the Ubuntu stock mysql-server and client for a long time, most of our shell scripts also expect the same path.
Continue reading “How I got MySQL 5.5 running on Ubuntu 10.04 Server”

The famous spinning cursor in bash

Displaying some sort of activity, the hangover from working continuosly on slow desktops are already deep into developers. Well we cannot complain, the users who are our clients or the clients of our clients always want everything very fast. And if we dont tell them about a process being running in the background, they are sure to kick the button again, and again and again. Hence the need to show activity progress, or even a small activity to remind that the system is busy processing some junk, the progress or throbbers cannot be avoided. A similar progress with message for a long running non interactive bash script was needed for me, since we were depending a lot on build kits written in bash.

The ESC sequence is to hide and display the bash cursor. Since the whole script is self running, and do not need any feedback, the cursor is better not displayed. The function at line 7, showProgress will output a message, and show the spinning cursor with full cycle in a second. stopProgess will make sure the progress display is stopped by removing the file touched by it. Only limitiation is that this can be used only in scripts that will run single instance at a time, even on a multi user system.

I prefer writing really temporary files to /dev/shm/ since that reduces harddisk wear and tear, and is real fast being on ram. While digging for something else at my home, accidentally I completed this bit of snippet. Well thought to share the same. Poor at explaining things so please excuse.

Using Bash to take incremental backups on MySQL database

The backup plan was to have a full backup of data and structure every Sunday and then difference of the database from last sunday to every other day, rotate the backups every fourth week.

At Saturn in the development labs, we restructured the development mysql by logically splitting out or combining projects and spread it across four instances of mysql running on a dedicated server. Later we felt that the weekly backups of mysql data folder was not sufficient for some of our projects. And for these we decided on having a rotational backup. The backup plan was to have a full backup of data and structure every Sunday and then difference of the database from last sunday to every other day, rotate the backups every fourth week. The structure backup was decided to be taken with mysqldump and the options –routines –triggers –no-data –compact. Whereas the data backup was to be taken as tab-seperated-values, using select into outfile. We had specific reasons to decide these methods as well as to take data and structure seperately.

Continue reading “Using Bash to take incremental backups on MySQL database”

Session TimeZones and DateTime fields

I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone

Today I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone. Well we arrived at a conclusion that we could not do that on DateTime fields in MySQL. Alas we were having varchar(19) in the equivalent field in MsSQL, which was the outcome of an import from MySQL using a connection string and a procedure. We were at a dismay, and admitted defeat. Later while having lunch, I got enlightend about using the built in functions to do the methodical conversions.
Continue reading “Session TimeZones and DateTime fields”

Cacti and rrdtool – a boon to the SysAdmins

Cacti templates and scripts built with the idea of getting the sampling data off colocated servers where there is atleast a webserver + php running, and on acute cases where both are unavailable, I would prefer deploying a single instance of nano web.

Regrett that I got familiarized with the cacti system only a week back. And once a clean installation was ready and running, the bottle necks started. Running snmpd for monitoring cpu, disk and network was an overkill. Though the snmp helped when we could add our WiFi Access Points, Routers and Switches. Even the traffic from our ISP Load balancer (pfsense) could be added for monitoring. All this was excellent. We got bottle necked at one point though.

Most of our colocated servers were behind heavy firewalls, and or running in virtual box machines inside real hardwares. True that we could use a single snmpd with multiple community id to collect data. But I was just reluctant. Just out of curiosity, the last one week day and night, at home, at work over IRC I was after getting the insides of both cacti and rrdtool. Thanks to the developers of both, and to all those who have contributed towards it. I did find better cacti templates on google code. This was one good step. The MySQL templates are simply superb. Also there is a script in php which does do ssh to remote servers and collect data. Good enough, but needed the cacti host root user to have a public key published on all of our servers.
Continue reading “Cacti and rrdtool – a boon to the SysAdmins”

MySQL vs MsSQL – comparisons never end

Search for the title, and you will land in several sites, which will tell you the views of the author. In fact there may be hell lot of people who would tell you that one out performs the other. But in reality what does that mean ?. If one knows how to optimize the database server, the sql, and the code behind. The performance can be tuned to a factor that both are at par. Now that is a far fetched view. Just think of the real bare minimum hardware to handle 300 tables, with less than 50 having a million rows, and the rest having master data. The MsSQL server will need a min of dual core with 8G memory, whereas the mysql would run on a 2G Virtual Machine and even out perform the MsSQL server. Think of the SELECT …. LIMIT X,Y and the SELECT GROUP_CONCAT, the advantages when considering server side pagination as well as selections directly outputting JSON for ajax frontends.
Continue reading “MySQL vs MsSQL – comparisons never end”