Optimizing Performance of Legacy PHP, MySQL Application

When your webpage load time increases randomly & drastically, there is always a need to benchmark the performance for high scalable websites. The challenge i faced was, of server page generation time, or your can say time to first byte. My application was using servers like Memcache, Sphinx, Mysql and Apache. Now i went to dig down the issues and thanks to Syed Alam & Muhammad Wasif, i discovered a really useful server app, NewRelic. This application have wonderful features but definitely it has some cost too, but i think it worth if you can manage it.

So, for those who want to run their free manual solutions, immediate solution would be to check MYSQL query timings. If you are using some database wrapper function or class, it would be very easy to just track time before and after mysql_query() function (using timer library). It will tell the insights where you are actually losing.

But if are working on tuning legacy code base, in which there us no mysql_query wrapper used, it a big cost to wrap it or apply benchmarks all over the place. This was the challenge i faced recently while working on a project. I searched web for some options and found that there are 2 libraries available, that can override core PHP functions with your own implementations.

Runkit’s runkit_function_redefine()
APD’s overide_function()

My experience with both of them was not really very good, as although they override mysql_query() function with my custom function, but gave segmentation fault and blank page randomly. Definitely not suitable for production. But if you can diagnose issues on local environment, you can take this risk for dev machine.

Solution:

Finally, thanks to Zeeshan Shah, i found another Clone of Runkit, that actually fixed those segmentation fault issues, and much stable than the PHP.net PECL version.

It really gave insights of how many queries are on my page and not performing well on local environment. I won’t recommend to use it on live as it may also have some glitches but one can try on dev machine. This can be integrated with PHP Quick Profiler for more presentable results.

Following is the sample code to play with …

<?php

// Custom implementation of mysql_query
function custom_mysql_query($query, $link = null)
{
// put your timer benchmarking custom code

core_mysql_query($query, $link);
}

// Make a copy of actual mysql_query function
runkit_function_copy(‘mysql_query’,’core_mysql_query’);

// Now redefine base function with custom_mysql_query()

runkit_function_redefine(‘mysql_query’,’$query, $link_identifier = null’,’return custom_mysql_query($query, $link_identifier);’);

Fix: PHP “echo()” function performance

Few days back, i was optimizing performance of a website. After all benchmark probes placed … i detected that the echo() statement is taking almost 1.6s just to print the $string variable of size around 100K. It sound very pathetic when all your DB fetching and other things are under 0.1s and echo() is taking 1.6s. It reminded me about the website phpsadness.com (that log the big issues of php) shared by my colleague (kadnan) few days back

Actual problem …

$t1 = microtime(true);
echo $html;
$t2 = microtime(true);
echo ($t2-$t1);

I researched on web, and found that it is a known issue with PHP’s Nagle’s Algorithm that is used in echo function.

I alternatively used print() but still it decreased to 1.1s but still not that i want.

A solution was proposed, to split the string in small pieces and then echo … it improved till 1s, but still not that i want.

http://www.php.net/manual/en/function.echo.php#52881

function echobig($string, $bufferSize = 8192) {
$splitString = str_split($string, $bufferSize);
foreach($splitString as $chunk) {
echo $chunk;
}
}

Promising Solution

i got this comment from a blog …
http://wonko.com/post/seeing_poor_performance_using_phps_echo_statement_heres_why#comment-5607

Guys, I think I narrowed it down even further!

As previously said, PHP buffering will let PHP race to the end of your script, but after than it will still “hang” while trying to pass all that data to Apache.

Now I was able, not only to measure this (see previous comment) but to actually eliminate the waiting period inside of PHP. I did that by increasing Apache’s SendBuffer with the SendBufferSize directive.

This pushes the data out of PHP faster. I guess the next step would be to get it out of Apache faster but I’m not sure if there is actually another configurable layer between Apache and the raw network bandwidth.

– Francois Planque

This seems very promising, as peoples commented … and i’ll be going to try it very soon.

Reference:
http://wonko.com/post/seeing_poor_performance_using_phps_echo_statement_heres_why
http://phplens.com/lens/php-book/optimizing-debugging-php.php
http://www.php.net/manual/en/function.echo.php#52881

Free – JQGrid PHP Control

Download :: Screenshots :: Changelog :: Support :: Comments

php-ajax-datagrid-jqgrid

Download Latest Release

with usage example

For Live Support, Add in Gtalk

gridphp@gmail.com

For Community Support

Visit Support Forum

Generate JQGrid based PHP Ajax Datagrid with just configuration (No complex Javascript). Enabling Add, Edit, Del, Search, Sort, Page, Themes etc. Fed up by repetitive operations every time? Significantly reduce time and cost by using this jqgrid php component. Ideal for prototyping Admin interfaces and quick setup tables. PHP Datagrid Control is customizable using parameters & custom events. Supports major databases including Mysql, PGsql, SQL Server, Oracle … etc.

UPDATE (17th July 2016): Tons of New Features Updated …

Custom Forms: Sometime we need custom Add or Edit forms for data entry purpose. With this feature it’s now doable to connect your HTML Forms with PHP Grid and it will use PHP Grid API for add/edit operations.

Multiple Files Uploading: Now PHP Grid support multiple file upload option. You can multi-select the files as in screenshot and upload to server.

Firebird & SQLite Database Editor Support: These fast, slick and reliable databases will help in running portable apps with PHP Grid.

Search with Multiple Group Conditions: We’ve added most robust and efficient complex searching method in PHPGrid.

Grid with Vertical Header Text: When we have many columns in a grid with less amount of data in cells, there is a requirement to have vertical text in column headers to improve overall visibility.

Exporting selected Columns & Rows (at Runtime): Now you can export selected rows and columns at runtime. This means no code change, Export selection options are now on accessible with grid operations toolbar.

Multiselect Filter in PHPGrid: Filter columns having a dropdown with checkboxes to select multiple items. It also support textbox to quickly search your desired item within filter options. This is similar to excel like auto filtering.

Integrating Select2 with PHPGrid: Now you can use ‘Select2‘ control with PHP Grid’s Add or Update operations. Select2 is a jQuery based replacement for select boxes.

New Additions to Form Layout Design: You can have 2, 4, 6 column layout in your Add or Edit dialog forms of PHP Grid.

Multiple Subgrids at Same level: Added a new feature, that allow you to have multiple subgrids at same level.

UPDATE (26th Mar, 2015):

A lot of exciting new features are added in this product. The magic is it’s all transparent using PHP Class. You can need to switch on/off the feature with no backend complex jqgrid/php coding.

Search with Multiple Group Conditions

This enables the more advanced search dialog allowing to add any number of complex group conditions. The user can now add or delete an unlimited number of conditions to perform the search as shown in screenshot.

search-group

Visit www.phpgrid.org/updates and subscribe to RSS / G+ or Twitter feed.

UPDATE (19th Nov, 2014):
We have rolled out several features that we wish our visitors to be aware of.

1) Multiselect Filter:
Added a new filter option, that let you filter columns having a dropdown with checkboxes to select multiple items. It also support textbox to quickly search your desired item within filter options. This is similar to excel like auto filtering.
Live Demo: http://www.phpgrid.org/demo/demos/integrations/multiselect-filter.php

2) Select2 Integration:
Now you can use ‘Select2‘ control with PHP Grid’s Add or Update operations. Select2 is a jQuery based replacement for select boxes. It supports searching, with in your large select box data items. It gives behavior similar to ‘Combo-box’ control of Microsoft development tools.

3) New Additions to Form Layout Design:
We have made several additions in our customized dialog layout feature.
1) You can have 2, 4, 6 column layout in your Add or Edit dialog forms of PHP Grid.
2) You can also Add sections (sub-titles) within form to separate input data group.
3) A field can span to several columns where required. (e.g. Id)
4) Custom form buttons are also supported. (e.g. Load Default)

Visit official  website for more www.phpgrid.org/ -> Updates

UPDATE (2nd May, 2013): New Feature Updates
New features include Autocomplete option for fast data lookup, Column based formatting, Clone Records, Twitter Bootstrap Integration, Grouping Headers and File Upload Option. Visit official  website for more www.phpgrid.org.

UPDATE (4th Feb, 2013): Released version v1.4.8 (for premium customers)
New featured examples include HTML / WYSIWYG editor integration, FancyBox integration, Loading Grid from phpArray, Conditional formatting, Conditional Data display, Controlling multiple detail grids from master, Server side validation, Custom client side validation, MySQLi support added. Visit official  website for more www.phpgrid.org.

Italia: Genera pienamente funzionale griglia PHP in soli 7 linee di codice. Aggiungi abilitanti, Modifica, Del, Ricerca, Ordina, Page, temi ecc PHP Ajax DataGrid di controllo si basa sulla jqGrid.

This piece of code will result in fully functional JQuery Ajax-ified PHP Datagrid with:

  • Adding record
  • Editing record
  • Delete record
  • Auto-filter Search
  • Sorting the Data
  • Pagination (customizable)
  • Grouping Display By Column (with aggregate summary)
  • Date-picker Integration
  • Custom Display Formatter (dropdown/checkbox/image/password/link etc)
  • Multilingual Support (Italian, French, German, Arabic, Chinese etc)
  • RTL Support and UTF8 Support
  • Customizable Themes (using ThemeRoller)
  • No time limit in Free version
  • — Premium Features —
  • Integration with all Major Databases (mysql,pgsql,mssql … and odbc support)
  • Master-Detail Subgrid (multi-level hierarchy) *
  • Custom Events for own Add/Update/Delete implementations *
  • Inline Row Addition
  • Export to Excel *
  • Export to PDF (with Custom PDF formats) *
  • Source Code (neat clean with comments) *
  • Technical Support in Integration *
  • Free Upgrades (on request)*
  • Advance Search *
  • Excel like Navigation *
  • Unlimited Developers Usage License
  • Unlimited Production Website License
  • Unlimited Themes Support (Theme roller)

Here is the PHP Code …

include("jqgrid.php");

$g = new jqgrid();
$grid["caption"] = "My Sample Grid"; // set grid customizable params
$g->set_options($grid);
$g->table = "tags"; // db table for CRUD operations.

// You can also specify SQL query for displaying data (download code with examples)
// $g->select_command = "select f1,f2,f3 from tags";

$out = $g->render("my_grid_1"); // render grid

... and in HTML after including JS files ...

Need Customization or Full Source Code?

– Full source code is flexible and new custom features can be added easily

– Full source code can be given after payment of $50 of appreciation

– Technical Support for component integration (on need basis)

– Free Upgrades & Bug fixes (on request)

– If interested Contact Me

Free version has no time limitation, but source code is encrypted and some marked (*) features are not allowed.

Download Latest Release

with usage example

UPDATE (15th Jan, 2013): New featured examples include Custom toolbar button, Export to CSV, Date Time picker control, Open grid in edit mode by default, Added more themes (24 total), Add Data Grouping example + updated documentation. Visit official  website for more www.phpgrid.org.

UPDATE (11th Nov, 2012): New featured examples include Multiple grids, Master-detail non hierarchical grid, Custom validation, Bar graph with updated documentation. Visit official  website for more www.phpgrid.org.

UPDATE (31st July, 2012): New featured examples include Postgresql integration samples, Conditional Display of Buttons / Data in Rows. Product now got a new home www.phpgrid.org with a new version 1.4.6. Refer this site for all future updates.

Technical support in paid package for Intergation with CakePHP, CodeIgnitor, Zend Framework & WordPress. CakePHP & Jqgrid integration is now easier than never before using our PHP Grid library. No need to go in complex JQgrid Javascripts. Just utilize this component and release quickly.

Roadmap:

– Bulk Updates

– Custom JS validation

– Excel view like google-docs (auto refresh, row/cell lock)

UPDATE: Version 1.4.5 is available  (February 12, 2012) – for paid members

– support for all major databases (mysql,pgsql,mssql,sybase … and odbc support) (db-layer.php)

– inline row addition (excel_view.php)

– pdf export custom template sample (export-pdf.php)

– export to pdf/excel in same grid

– fix for non integer primary key

– fix for mysql keyword based column name

– Fix for non-integer PK column

– multiselect fix in lib jqgrid 4.3.1

UPDATE: 21st December, 2011

Version 1.4 is available

Features:

– Datepicker integration

– Group by field

– Runtime change “group by” field

– Password mask formatter / edittype

– Excel export support for numeric columns

UPDATE: 21st July, 2011

Version 1.3 is available

Features:

– PDF export feature (export-pdf.php)

– Multiple fonts in pdf export

– External link with grid data (external-link.php)

– Export filtered/all data option

– Export specific columns (export-pdf.php, $col[“export”] = false;)

Fixes:

– persist where filter in export option

– Jquery fix for IE

– utf8 encoding fix

– json fix for php 5.1

– slashes fix

UPDATE: 31st December, 2010

1. Version 1.2 is available

– Several bug fixes (with thanks to all reporters)

– Support for Subqueries

– Must review README.txt when migration from previous implementations

– Get latest version from above link

2. Image Formatter

Introduced image formatter to display image in grid, if URL is fetched from DB.

$col[“formatter”] = “image”; // format as image — if data is image url e.g. http://<domain>/test.jpg

$col[“formatoptions”] = array(“width”=>’20’,”height”=>’30’); // image width / height etc

3. Excel like Navigation

– Navigate through arrow keys

– Enter to Edit / Save

– Include Intrinsic Operations

—-

UPDATE: 2nd November, 2010

Several fixes and incorporated in latest version. Please get your latest copy.

* Support for formatters e.g.

$col[“formatter”] = “date”; // format as date

$col[“formatoptions”] = array(“srcformat”=>’Y-m-d’,”newformat”=>’d/m/Y’); // format as date

* Cell editing navigation support, like excel

* Table alias fields are fixed

* Enabled support for GROUP BY custom query

* Export excel fix for ? based url

—-

UPDATE: 5th October, 2010

Following features are now incorporated in free version …

1. Sub-Grid/ Master Detail support

And it is available with all main operations … like search/add/edit/delete/sort/paging etc. You can have multi-level subgrids.

JQGrid Subgrid & Master Detail Example
JQGrid Subgrid & Master Detail Example

2. Custom Events Support

Now you can write you own custom implementations on UPDATE / INSERT / DELETE.

E.g. code …

// params are array(<function-name>,<class-object> or <null-if-global-func>,<continue-default-operation>)

$e[“on_insert”] = array(“add_client”, null, false);

$e[“on_update”] = array(“update_client”, null, true);

$e[“on_delete”] = array(“delete_client”, null, true);

$grid->set_events($e);

—-

Technical FAQs / issues available here

  • Custom Display Formatter (dropdown/checkbox/image/password etc)
  • RTL Support and UTF8 Support

Genera pienamente funzionale griglia PHP in soli 7 linee di codice. Aggiungi abilitanti, Modifica, Del, Ricerca, Ordina, Page, temi ecc PHP Ajax DataGrid di controllo si basa sulla jqGrid.

UPDATE (6th June, 2012): Product now got a new home ‘www.phpgrid.org‘ with a new version 1.4.6. Refer this site for all future updates.

Mysql REPLACE() issue with Latin1 multi-byte character

Problem:

I’ve a database with server collation of utf8 / utf8_bin. DB have arabic utf8 text with accent chars (zabar / kasar etc). I want to get text without accent chars.

For e.g. SELECT replace(field1,0x[CODE],”) — where [CODE] is the accent char.

REPLACE() is working fine when i write static text in place of field1. But it does not replace when it run at database field.

Solution:

After reading an article, i come to know there are 2 (client & server) encodings used with mysql. My server encoding (table charset/collation) was utf8, but client’s encoding was latin1 and the time of INSERT.

So data stored in DB is somewhat mixed, due to which REPLACE() was not working as expected.

FIX:

1. i fetched the data using php,
2. applied bin2hex() function on data,
3. set client’s encoding to utf8 (set names utf8) and
4. updated same record using mysql’s UNHEX function.

Here is sample code …

$q=mysql_query("SELECT field1 FROM table1 where id = 4");
$rs = mysql_fetch_assoc($q);
$v = bin2hex($rs["field1"]);
mysql_query("SET NAMES UTF8");
mysql_query("UPDATE table1 SET field1=UNHEX('$v') WHERE id = 4");

This fixed my client’s encoding to utf8 that matches with server encoding, now REPLACE() is removing that accent hex code and working as expected .

Following article really helped …
http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

Mysql latin1 client with utf8 server encoding issue

Problem:

I’ve a database with server collation of utf8 / utf8_bin. DB have arabic utf8 text with accent chars (zabar / kasar etc). I want to get text without accent chars.

For e.g. SELECT replace(field1,0x[CODE],”) — where [CODE] is the accent char.

REPLACE() is working fine when i write static text in place of field1. But it does not replace when it run at database field.

Solution:

After reading an article, i come to know there are 2 (client & server) encodings used with mysql. My server encoding (table charset/collation) was utf8, but client’s encoding was latin1 and the time of INSERT.

So data stored in DB is somewhat mixed, due to which REPLACE() was not working as expected.

FIX:

  1. i fetched the data using php,
  2. applied bin2hex() function on data,
  3. set client’s encoding to utf8 (set names utf8) and
  4. updated same record.

This fixed my client’s encoding to utf8 that matches with server encoding, now REPLACE() is removing that accent hex code and working as expected .

Following article really helped …
http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

Custom Google Analytics Dashboard for your PHP Application using GA-API

Few days back, i was building an executive dashboard using  google analytics api. So i wrote a library, by which you can have required google analytics graph/charts inside your Corporate PHP application without going to google analytics page (avoiding 2 admin interfaces). You can embed any desired graph/charts (by providing desired URL) in your corporate website’s admin area.

Using few existing open-source tools, i tried to give same user experience as we have in google analytics dashboard panel. I have used Open Flash Charts for analytics like charts generation and data is fetched from google analytics (thanks to wordpress google analytics plugin).

Actual google analytics charts (image below) are almost similar to what is available in this lib.

Implementation is shown in working sample code which is available to download. You can enhance it as per your requirement.

In configuration file (config.php), you have to provide your Google Analytics User, Google Analytics Password and Profile ID. These information are required by google analytics api for authentication and analytics data fetching. You can get your profile ID from URL (as in image) when you log into your google analytics account.

Click here to download sample code. To download sample code, contact me by replying this post.

Update: Code is grounded due to stability / other issue. Apologies for all followers of this thread. Use this link as an alternate.

Experimenting SEO Ideas

Now as search engines cannot look as we humans do, there are some SEO strategic positions that google or any other search engine might look for. To get better serp (search engine result page) place your keywords in those strategic positions. For good keywords suggestions of your content, you can take help from free tools like google keyword tool or wordtracker.

Here are few seo strategic ideas / seo strategies that can be added in your webpage.

SEO of Content

1. h1,h2,h3:
H1 tag should contain the primary keyword of the content, and should be only one. You can multiple h2, h3 in your content.

2. meta title:
The title should be keyword rich, but write your meta title for humans first and then search engine.

3. meta decription:
Good Meta description will always benefit the CTR (Click through ratio) of your google listing, as it is shown  just below your page title.

4. <strong> & <em> tags
These tags are helpers for search engines, to pick important keywords of the page. So place your desired keywords in these tags.

5. first paragraph of page
Search engine gives special priority to the text of first paragraph of page. This should have crux of content and keyword rich.

6. table’s <th> and <caption>
If tables are used in content, these tags helps search engine to define what content type is present in table.

7. anchor link label
This is one most important weighted element of search engines, by what name, other pages are referring your content. This is usually achieved by ELB (External Link Building) or ILB (Internal Link Building) if you have tons of content in your website.

8. keyword rich url
Google gives priority to keyword present in URL. But another important learning is, if your URL contains many keywords, this weight-age will to particular keyword will get distributed.

9. ancronym and abbr
Use these tags where required to help search engines about abbreviations.

Keyword to Page Relevance

To increase quality of backlinks, the keyword must be relevant to page (for landing page as well as source link page). keyword density with in a page should not exceed 7% as is calculated using this formula

Keyword Density = Keyword frequency / total words in page

SEO of Images

For SEO of images (images.google.com), also consider adding following:

1. filename of (image/ logo)
The name of image file is the same rule as we define our keyword in URL.

2. alt tag
Search engines cannot see the images like humans do, they usually detect them through ALT tags

3. title tag
Title tags are important to store a description about particular image

SERP Checking Tool

For continuously optimization, check your SERP against some keyword, here is a handy SERP Checker Tool

http://ranks.nl (paid service) provides a proximity tool, to find out which keyword is more optimized in your site, and it checks it probably on above mentioned rules.

More reading …

More details available in an seo book “Building Findable Websites”.