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.
Read more…
General Articles, mysql
mysql
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.
Read more…
General Articles, mysql
MSSQL, mysql
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.
Read more…
General Articles, Security, Suite Reviews
cacti, rrdtool, server monitoring
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.
Read more…
General Articles, mysql
MSSQL, mysql
In the previous post, I had specified that a code analysis would be presented later on, which is happening now. Though I am not permitted to abstract the whole database abstraction, which would otherwise divulge the core business logic of the system too, I do expect that the following would be enough to guide a MySQL PHP developer to port his application to MSSQL 2005. Mostly the compatablities are maintained. But for the database design, we had to let away some of the wonderful features from MySQL.
We started by a code auditing and reworked the system such that we did not use any group_concat through out the system. Also all timestamp and datetime fields were changed to varchar(19) since we were already feeding those fields with the php function date’s return value or ‘now()’. But there was quite a handful of areas where we were using STR_TO_DATE and with different formats. So this had to be handled in its own way. And it is here we started our regular expression war path.
Read more…
Code Snippets, General Articles, PHP, mysql
MSSQL, mysql
Last post I did had all of my feelings, and despair, and now I am sure those were out of sheer negligence and mostly because I was unaware of the MS SQL product. Though I still do not agree with certain things like the row_count workaround for the limit feature, and escaping of quotes. But for our project we could more or less maintain a streamline by using the same code base for MySQL, SQLite3 and MSSQL 2005. Will try to explain this across a couple of posts.
Read more…
General Articles, mysql
MSSQL, mysql
I could not resist digging into my administrative and shell experience when I saw that somebody wrote a php script to dynamically split lighttpd logs. Hmm I use php for more complicated stuff.
I achieved this by putting the following into lighttpd.conf
accesslog.filename = "|/usr/bin/splitlog.sh"
Read more…
General Articles
lighttpd, shell scripting
After about an year, since we launched Asianet News Portal the home page with all the complications had started to show a dead slow performance, and by using our Query Profiler, I could identify the bottleneck as the number of queries, and wordpress itself was telling the status as 48 queries in 6.344 seconds. Now this was a whopping value, and when the latency of 2 to 3 seconds is added to this, the initial html load would be in 9 seconds. Making the total site complete load in about 53 seconds. One can imagine the agony I was feeling, after all the work I had done to get backlinks, and if users who are visiting the site is just running away without waiting for the site to load, then I better not run the site atall.
Read more…
General Articles
php tips, speedup php pages, wordpress
Now a days script driven web applications are getting more and more complicated with background operations and triggered events. Debugging or event tracking is tough once the application is moved into production. Fresh and aspiring programmers are always too cautious to wade into deeper waters, and always go with line by line testing. Almost always in the course of debugging or code optimizations I see a lot of them using file_put_contents or echo to check variables at a particular point of execution.
I always gave the pressure to use a good logging system from the start itself, and to add level based message logging with debug_backtrace wherever needed. The most recent class abstraction for php programmers which is being used in our custom framework is attached to the downloads here. The file logging is being done after serializing, compressing and base64_encoding to keep logs in single lines, and to make sure they dont take up too much of the space.
Read more…
General Articles, PHP, Tips
php command line, php snippets, php wrapper, script logging
While trying to speed up Select Articles, I got stuck, torn in between WP Super Cache and lighttpd. Already we at Saturn are using lighttpd on several dedicated servers, and know the potential and benefit of having this over Apache though the flexiblity of having a perdirectory configuration at the programmers choice was a big question. A quick search on the Google landed me to a technical blog, notepad and personal web page by Asterios Katsifodimos.
Trying to get WP Super Cache & Wordpress working on my fast lighttpd server, I came into problems, mainly because of lighttpd’s lack of (Apache’s version of) the mod_rewrite module. The static files that were created from the cache were not statically served from wordpress. The problem is that in order to use them, the PHP fcgi was called for each request. So, why would we have to call PHP every time that a file can be completely statically provided by the web server?
Read the rest of his version Installing WP Super Cache with lighttpd
General Articles
lighttpd, super cache, wordpress
Recent Comments