Migration from MySQL to MSSQL our solutions – Continued

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.
Continue reading “Migration from MySQL to MSSQL our solutions – Continued”

Migration from MySQL to MSSQL our solutions

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.

Continue reading “Migration from MySQL to MSSQL our solutions”

Lighttpd – Separate log files for each vhost

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"

Continue reading “Lighttpd – Separate log files for each vhost”

Speeding up WordPress home page

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.
Continue reading “Speeding up WordPress home page”

Importance of event logging in server side scripting

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.
Continue reading “Importance of event logging in server side scripting”

WordPress Super Cache on lighttpd

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

MySQL Query Profiler; Better late than never

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 (https://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.

rschumacherRobin 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.

Scott’s Blog: Stupid Bug Reports

When you make a bug report or feature request to any sort of project please check you have all of the relevant information and if you can get someone else to check it through. Try searching Google first or ask in a mailing list.

For a performance issue check against an older version of PHP to see if you can work out when it was introduced, if you’re not currently testing the CVS version then check that as well. It may have been fixed already. Reports that language X is faster than PHP will also most likely be ignored, these really aren’t constructive and unless you can identify the issue within PHP it’s pointless to create a report.

Scott in his blog continues to write “Finally, don’t get aggressive or be an asshole when your bug reports get closed. PHP is an open source project and most contributors are volunteers. If you don’t like something then you are more than welcome to submit a patch.”

PHP; Towards a 5.3 release

Straight from the php internals discussion:

Items on the list as the key features of this release

1) namespaces
Here we need to make sure that the current state is now in a coherent state. I think Derick still has some issues with the recent change by Greg, but even his criticism did not sound all to loud. So I think we are in a good state here?

2) late static binding
Etienne had some questions recently, which were met by criticism by Stas. However all others agreed with the change. So I guess we are solid here too?

3) re2c
Rui recently came to the list with notes on the ZE MB feature.

4) windows support
Ever since Edin disappeared it has become clear that we have a bus factor issue with windows support. The windows team is working to rectify this situation. We need to make sure that the infrastructure to deliver windows binaries for PHP 5.3 as well as PECL extension is in place before we can release 5.3.

5) BC issues
Well this is a bit of an “anti-feature” in the sense its not a task anyone is dedicated to. The point is that we need to make sure that we understand any BC issues we currently have, so that we can either correct them or document them.

These are the 5 areas we the RMs would hope that people focus on.

Thats actually 3 focus areas too many for my taste, but goes to show that we might want to release more often (given that the list for 5.4 is already cramming up).

On top of this we also have a few other changes that are of quite some importance, but that to me will not stop a release if they do not make it (for the extensions we feel that they will be available via PECL for those who really need them now in the worst case). But these are big features non the less that could warrant a new minor release on their own alone if it would be for even bigger stuff:

1) intl extension
Last discussion ended without a decision on the class naming. I specifically remember Derick taking issue with intl ext “invading” the date ext namespace. Stas however arguing that the intl ext is supposed to bring some forwards compatibility to PHP 6 and therefore naturally will need to span the namespaces of other extensions, that are planned to be expanded for PHP 6.

2) phar extension
I guess we are pretty solid here?

Here we just need to make sure that we actually mark only the things as deprecated that we actually want to deprecate. This ties in a bit with the BC issues point above.

4) __callStatic

5) Garbage Collection

So is anything missing? Please everybody take time to review the todo list, make sure that all items are on the list, make sure that the information is as up to date as possible. Finally anyone who’s name is on this list (or who will add himself) should get in contact with Johannes and myself within 1 week (thats July 9th) to explain the state of the todo item and when he can finish the item and what the general impact the feature has on the release. Also please bring up any issues, especially for the above 6 points, to the list and try to focus on solving the issues in a timely manner. We RMs will try to moderate as much as possible, but understand that at some point we will have to have to go with one approach (or in the worst case we might have to push a feature to 5.4).

After July 9th, we will then publish a tentative release plan within 3 days afterwards. The tentative schedule will probably try to move us quickly towards a feature freeze together with a first alpha. Depending on our discoveries we will schedule beta and RC releases (obviously subject to continued review).

MySQL Backups and a lot more

A couple of years back, I had asked several people through groups, and posts the possibility of backing up a MySQL database programatically, from client side applications which could connect to remote servers running MySQL. There was a lot of suggestions,

* php script on server to take backup on server
* application code to select the data and do a round trip
* mysqldump from client machine with the server parameters

And finally from all those valuable suggestions from all the helpful people, I derived my own hybrid method which I would summarize in a few words. This is a method, which I have adopted for web applications in php as well as custom single machine applications in Visual Basic. How this can be termed as hybrid can be known by going into the structure, though it seems to be a bit complicated, it would serve the purpose and be worth the headache.

Continue reading “MySQL Backups and a lot more”