PHP Trivandrum

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

One of the great things about MySQL is the superior innovation model that’s used to deliver database server software. Rather than relying solely on internal engineers who create and maintain a piece of software (as in a traditional software company), MySQL partners with the millions of active users across the world who take advantage of the open source model and daily extend the MySQL server to do new and pioneering things. These innovations can then be submitted to MySQL AB, tested, validated, and rolled into the database server so everyone can benefit from the creativity of the very active MySQL community.

An example of this model in action is the recent release of a new SQL diagnostic facility that assists in the debugging and analysis of running SQL – the SQL Profiler. The new profiler became available in the 5.0.37 version of the MySQL Community Server and was created and submitted by Jeremy Cole of Proven Scaling (http://www.provenscaling.com/).

Let’s take a look at this new diagnostic utility Jeremy developed and see how it can help you create high-performing SQL code as well as assist in troubleshooting existing queries that aren’t providing the response times you’d like.

The Best Way to Diagnose Performance Problems

When it comes to overall performance, it’s important to remember that the #1 contributor is always a good database design. The second highest contributor to performance is well-tuned SQL code. Some try and switch the two in priority, but this is a mistake because a bad design has the potential to simply drown even the best-tuned SQL code (e.g. you can’t get index access in your SQL queries if the physical index design is poorly done). But make no mistake – bad SQL code that’s introduced into an otherwise well-running database can make a real train wreck of things.

So how do you go about analyzing database performance? There are three forms of performance analysis that are used to troubleshoot and tune database systems:

1. Bottleneck analysis – focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
2. Workload analysis – examines the server and who is logged on to determine the resource usage and activity of each.
3. Ratio-based analysis – utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.

Of the three, bottleneck analysis is the most efficient approach in terms of fast problem resolution. By determining where the server, a user connection, or set of SQL code is spending its time, you can then work to eliminate the discovered blockages, increase throughput, and reduce overall response times. Unfortunately, this hasn’t always been easy in MySQL because the server hasn’t traditionally exposed the type of wait-based and resource statistics to quickly uncover bottlenecks.

But a better day has come along in MySQL 5.0.37 with the inclusion of the SQL Profiling utility. This interactive diagnostic aid allows a developer or DBA to gain insight into where a set of SQL code spends its time and the resources it is using. The best way to see the power of this new utility is to walk through some examples of how it’s used and see the clear benefits it supplies, so let’s do just that.

Robin Schumacher is MySQL’s Director of Product Management and has over 13 years of database experience in DB2, MySQL, Oracle, SQL Server and other database engines. Before joining MySQL, Robin was Vice President of Product Management at Embarcadero Technologies.

Read the full article at Mysql Developer Articles

Open PHP MyProfiler is just a trial to run query profiling on a php-mysql application, without changing the architecture too much. Any one who needs to do the analysis could make use of the same by downloading the profiler, and implementing with their code. The profiler would create logs depending on hostname and date. upload the profile logs to our Profile Sampler and you should be able to see the full profile of your application.

The Kerala’s favourite portal Keralaonline was built on top of the popular blogging and content managment system Wordpress. With about 30K hits per day, and 8000 entries across 100+ categories and video clips, the site started to slow down by about the end of March. This gave a spark, and the Open PHP MyProfiler, was built originally to run sql profiling on keralaonline.com.

General Articles, mysql