Heavily loaded Php Application - Expert Optimization Techniques

Posted on 2010-11-14
Medium Priority
Last Modified: 2012-05-10
Hi experts have a little trouble with optimization ;

I have developed a gold stock exchange web based application that shows live currencies, by using ; php,mysql and js and it used to work fine for a while . Now its hosted from win 2003 server with 512mb rams / 2.2 ghz cpu and there's a page stays open on server that connects to a web service , takes currency datas from there and creates data.xml once in 12 seconds which all application works over it.Also this page connects to mysql takes profit margins from there and adds it to my customer's currency value before creating data.xml.Lets call this part as update.php.

While update.php is open once on server and renews it self once in 12 seconds ,a file called as main.php , parses data.xml and saves all current currencies buy / sell information in sessions to compare with the next values in order to display up or down arrows near by updated currency values and plays a sound if my customer's currency updated.

In a file called as program.php i setted up an interval in javascript that uses jquery load to load main.php every 12 seconds in to the main div , program.php is the file which is accessed by clients and stays open all time in client's computers.

Screen Shot of application
Result seems like above everything works perfect , but nowadays the simultaneous usage of application rised and there are at least 600 simultaneous connections which makes web server fail and stuck.

What i ask for is ;
is there a better way to build this application , i mean logically ? Would caching work here since the data is renewed every 12 seconds ?

Here are my solutions ;

Hiring my vps from vps.net which is linux + apache based much more better than win + iis build , also cloud hosted.
Creating another page that stays open at server to convert program.php 's output to html and save it as output.html and serve it from program2.html in every 12 seconds , so there will be only one session created and all php processes works once.

Any other solutions ?
Question by:Ali Kayahan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7

Expert Comment

ID: 34134412
Hi ali_kayahan,

 Glad to help you :-)

  Even before cache your files, you have to optimize your MySQL server for better results that you are looking for. Kindly go through with the link given below


If it helps then its cool else let me know !!!

Jerome Dennis D
LVL 111

Expert Comment

by:Ray Paseur
ID: 34136002
Grab a copy of Firefox and install Firebug + YSlow.  Those will help you with the WWW part of the application.

Since we cannot see your data base, web service or programming, we probably cannot help you with anything more than theoretical guesses.  So here are some of the things that can help improve the speed of web applications.

Be sure you have indexes on every column used my SQL statements JOIN, WHERE, ORDER BY and GROUP BY.  If you are adding a lot of rows to the tables, consider dropping the indexes and recreating them instead of having them in place during the additions.

Use EXPLAIN on every complex query to see what MySQL is doing.

Use LIMIT whenever you can.  Avoid SELECT * and choose the columns you need instead.

Add script timers around any calls to external services -- see if the services are responding quickly enough.

It sounds like "data.xml" might be a design element that could be removed - you might just put this information directly into the data base?

Whether the currency is rising or falling really only requires two rows from the data base - current and most recent.  Your SELECT statement would ORDER BY most recent and LIMIT 2.

Determine whether there are enough data pipes into the server.  The word "connections" might mean HTTP connections or MySQL connections - either could be causing you trouble.

Should you move to Linux?  I would.  But I cannot tell you that it will necessarily help your performance.  Before I did that, I would hire a DBA to examine the application in detail.  For a few hundred dollars, you can get a world-class computer science expert to share a day of experience with you.  Sounds like it might be worth it.

HTH, ~Ray
LVL 14

Author Comment

by:Ali Kayahan
ID: 34137102
@haijerome: Thanks a lot for that great mysql optimization article , i already clicked yes for it :) .But in my case ; i only use one row and one column even though all optimization tips already applied on it .

@Ray_Paseur : Ray , as i wrote mysql has really few jobs to do , in this case a query gets triggered by update.php  in every 12 seconds which works on server and closed for client access  , and compiles data.xml with the datas that has taken from web service.

As far as i know , yslow can only look for image,css,dom ect.. speed which is completely client side related anyway i checked it again and looks excellent.

I think there is no need DBA while the database in question is mysql anyway would it be smarter to let mysql handle all mess instead of xml and sessions ? In this case we will get rid of all session data which really constitutes a serious part of load whilst creating a serious headache for mysql.

But we should imagine that 600 clients asks for new data in every 12 seconds while upgrade.php writing new datas in every 12 seconds , this means query rain  , in this case should we use memcached ?

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 34137369
You might consider memcachedD - but again, without looking at your application code and data base structure, all we can offer is the most general guidance.

The code snippet will show you how to time part of a script.  You might try applying that sort of measurement to various parts of your code.  600 client requests per 12 seconds is a steady-state load is 50 hits per second.  That will never actually occur, instead you will get a surging pattern - sometimes more and sometimes less.

Not sure why you think there is no need for a DBA -- in my experience, when there are performance problems with a computer program, the answer is almost always found in the I/O subsystem.  In a web application this usually means the data base.  That's why I recommend having a good data base administrator look at the structure and the queries.
<?php // RAY_script_timer.php

// MAN PAGE http://us.php.net/manual/en/function.microtime.php

$alpha_time = microtime(TRUE);

$page = 'http://google.com';
$html = file_get_contents($page);

$omega_time = microtime(TRUE);

$lapse_time = $omega_time - $alpha_time;
$lapse_msec = $lapse_time * 1000.0;
$lapse_echo = number_format($lapse_msec, 1);

echo "SCRIPT READ $page IN $lapse_echo MILLISECONDS";

Open in new window

LVL 14

Author Comment

by:Ali Kayahan
ID: 34138357
Ray ; i just thought handling 50 queries per / second for mysql would consume more time than parsing xml . I can handle all mysql operations thats why we dont need DBA :) , so i will try to rebuild application by using mysql to store datas recieved from web service and use memcached to serve it clients.
LVL 111

Accepted Solution

Ray Paseur earned 2000 total points
ID: 34138463
Makes sense.  I don't think XML is necessarily giving you any added value here.  If the DB server is configured correctly a lot of information will be in cache most of the time anyway.  50 queries per second means 20 milliseconds for a query response - probably that is no problem at all if your DB is indexed correctly.  But it's still all a big fat guess because you have not posted anything meaningful, like the CREATE TABLE statements or the queries

When I think of XML, I think of it as a transport mechanism, not as a data store.  And depending on the element names, it can be an inefficient transport mechanism when compared to JSON or CSV.  I use it all the time, but not for efficiency - just for ease-of-use.
LVL 14

Author Comment

by:Ali Kayahan
ID: 34139244
A table build like ;

create table currency(
    cId int not null auto_increment ,
    primary key(cId),
    prevGld int,
    currentGld int,
    prevDollar int,
    currentDollar int

from program.php runs on server side once in 12 seconds ;

update currency set prevGld = currentGld , prevDollar = currentDollar ,currentGld = '$currentGld' , currentDollar = '$currentDollar' where cId = '1' ;
LVL 111

Expert Comment

by:Ray Paseur
ID: 34139291
Are you suggesting that there is only one row in the data base, and that this is the only change every 12 seconds?  Are there any other tables?
LVL 14

Author Comment

by:Ali Kayahan
ID: 34139337
Yes only one row in database no other tables , the row gets updated by program.php in every 12 seconds , as far as we have 5 currency values that we retrieve from web service , saving their previous values for comparision for up or down arrow would be enough , wouldnt it ?
LVL 111

Expert Comment

by:Ray Paseur
ID: 34139583
Sure, unless you want to keep some kind of historical record.  You could keep another table of similar structure and just do INSERT ... DELAYED.
LVL 14

Author Comment

by:Ali Kayahan
ID: 34139968
So from client side that refreshes once in 12 seconds ;

   $memcache = New Memcache();
   $memcache->connect('localhost', 11211) or die("everything will be ok , really") ;
  $currencies = $memcache->get( 'values:' . $updatedValues ); // i set this from update.php that works on server

LVL 111

Expert Comment

by:Ray Paseur
ID: 34140785
I don't know how to answer you since I this appears to be only a tiny fragment of the code.  It looks right, I guess.  

What is the question at this point?
LVL 14

Author Comment

by:Ali Kayahan
ID: 34141019
i was just trying to make you busy by asking foolish questions to gain time for answering other questions (: ok i was kidding , i just wanted to be sure about my new build , in update.php (the file works on server side once in 12 seconds and forbidden for client access) ;

1) i get datas from webservice ,
2) insert them in to mysql ,
3) populate memcache variable 'values' with query result that holds prev and current currency values.

from client side that 600 clients connect and renews it self once in 12 seconds

1) i run the code above to get memcache variable
2) fetch datas from that variable and parse them
3) than i pray for memcache not to commit suicide

is this build true or logically is there any pitfall ?
LVL 111

Expert Comment

by:Ray Paseur
ID: 34141629
Nothing could possibly go wrong.
LVL 14

Author Comment

by:Ali Kayahan
ID: 34141693
Thanks a lot for your help and patience :) , i ll re-build the project and let you know the result

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What You Need to Know when Searching for a Webhost Provider
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question