• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

Design Web Application with real-time status

Dear experts,

I wish to create a design which can display a real-time status. It needs to support 20+ users. Currently my design is to create a html page with integration of  javascript ajax into a div tag. The div will refresh every 3 secs. Inside this div, it will call a php file that will retrieve data from MySQL database that will display all processing orders (that is like 50+) at a time. Any closed orders will not be displayed. If there is only a single user that use this page, the server can handle the load. If there are more than 20+ users, I can see that it can create a great burden to the server.

I wish to know what will be the best solution in term of low cost, better performance, and easy to maintain?
Here are my options:
1. install mysql on each client's PC. Everytime when there is an update of the data, the server will copy the data over to clients' PC. If local users wish to see the display, MySQL query will simply run off the data on the local DB from client side.

2. Upgrade the physical server as in adding one more extra CPU and memory. Most important is the Disk I/O. I may need to get SSD for server or I can get better harddrives with faster RPM. MySQL is running on Web server and web server is running on one of the VM (Virtual Machine). By upgrading the physical machine, it may increase the performance of the VM running web server.

3. Get a SAN storage and install just MySQL. I am not sure if this is the best solution because i haven't try SAN before. I've only use NAS for backup files. I will need some instruction of how to set this up.

4. Install MySQL on an entirely different physical server.

5. Are there other options besides #1 - #4?

If anyone has a good suggestion, please let me know. Thanks
0
Kinderly Wade
Asked:
Kinderly Wade
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Ganesh Kumar ASr Infrastructure SpecialistCommented:
Recommend this : 2, , 3 for & 4 are the best
It is best to have MySQL on separate hardware.

1st option is worst one and there are no option other than this, because the processing request are 50+ and 3 sec refresh will consume much power (processor, memory and disk) heavily.
0
 
Ray PaseurCommented:
There is almost no overhead load at all from 20 users hitting a server every three seconds.  Have you tried to simulate the activity?  We're talking like 7 hits per second - that's just peanuts.  Your disk drives are probably spinning at 7,500 RPM, so your I/O operations are measured in single digit and low double digit milliseconds.

Most likely you will want to first tune your MySQL installation and understand your query timings.  Our colleague here at E-E, @gr8gonzo, has a good article that can help you get started.
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

To that, I would just add that database normalization may not be your friend at scale, where the more usual approach is to denormalize, use MemCacheD, and throw hardware at the performance problems.
0
 
Kinderly WadeprogrammerAuthor Commented:
Hi Ray,

The table that I used to query select statement grows fast (about 20K of rows). The query time for the select statement is fast. Somehow when I checked my web server, the CPU is consistently running around 70% to 100%. It will get to a point that I have to restart the web server.

Besides the php script that runs every 3 sec, there are other bash scripts running to obtain data from another source.

Here is my basic layout.

I need to import data from data with *.DBF extension. Once I imported those data over, the users can process the data in mysql. Finally the data can be displayed.

Thanks for the links from above. I've checked my tables(4 of them) and they all have indices and I've also checked the performance of the queries (with mysql workbench) and those are fine.

The basic format of query is like this:

SELECT *
FROM tbl_A
WHERE current_date = now();

The web server is hosted on a VM. I am not sure if other VMs are taking up the shared resources or simply other processes are taking it up. There are like 8+ VMs hosted on the server machine.

Maybe I need to swap out the web server to a different place or perhaps a different physical server?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Ray PaseurCommented:
It's always smart to isolate the application from the other things that might interfere with its performance.

Regarding this:

SELECT *
FROM tbl_A
WHERE current_date = now();

You probably do not want to select all columns; you can select only the columns you actually need (select by name) and this will make the application faster.  If you do not need all 20,000 rows, you can use ORDER and LIMIT clauses to reduce the size of the results set to something that will run faster.
0
 
Kinderly WadeprogrammerAuthor Commented:
Hi Ray,

Thanks for the tip. I will try to optimize for the columns that I need. With the new Select there will be 10 columns with like 2K of rows at the moment. Mysql has no issue with executing the query with my test run.

I may have to get an consultant to see if there is a need to upgrade the system in order to handle the VMs.
0
 
Slick812Commented:
hello  Kinderly Wade, , you have several many different parts to this question, as some about the MySQL engine (computer processor operations), and about the VMs, and about "bash scripts running to obtain data", and about the LOAD on your server, if you query with AJAX every 3 seconds. With MySQL SELECT, to have proper column Indexes, and minimized and optimized SELECT query's can make tremendous differences in the CPU work (time) needed for a SELECT query. But since you have several other data gathering processes (bash scripts), I would change the AJAX time cycle to maybe 10 to 15 seconds, instead of 3 seconds. For the most part, a person can NOT possibly read and respond to and display with more than 12 rows of data every 15 seconds, much less every 3 seconds. Although this may be some sort of machine (browser javascript) response to to data display changes in the ROW values. Next, , I have found that in sequential AJAX large data retrieves as you describe, it is a LARGE disadvantage to do new PHP processes (mysql) on the server to send back to the browser rows and columns that Have NOT changed, to send back data already in the browser is a waist of time. I have used a "date" marker, you can save in the browser javascript OR the PHP session for the "segments" of data or the Table or rows, and if the "date" marker has not changed, then do not retrieve the OLD data, and just send an AJAX response to browser with just the needed new data sets, , and indicators that tell it not to change the browser data sets that are NOT NEW. This can be something that takes time to develop, however for timed sequential AJAX calls to get big data sets, you almost always have to find ways to reduce SERVER work and processes, or you end up repeatedly doing useless data retrieves to send back data already in the browser and slowing your server operations.
0
 
Kinderly WadeprogrammerAuthor Commented:
Thanks Slick812.

I found out that just the bash scripts along 10+ consuming up to 30%+ CPU already. MySQL ranges from 30% to 65%. This simply adds up to full CPU consumption.

Does it mean that I need to separate out the MYSQL into a separate server?

Since I am running on VMs, I can see that resource allocation for each of the VMS. My CPU max is 9.6K Ghz and my web server VM is taking up 8K Ghz already.

Let me know what you think. Thanks.
0
 
Slick812Commented:
You ask some questions like - "Does it mean that I need to separate out the MYSQL into a separate server? ", Which I can not fully understand the other things (the Context) of what you have and the load level (users and web traffic numbers) that you have these very concerning CPU levels. If these levels (65% + 30%) are for your expected user hit per minute levels, you need an over all HARDWARE upgrade, plus some load balancing, I would think. You should already have moved your MySQL engine to a separate server VM or other sectioning, where it has its own separate (not shared) CPU cores, hard drives and memory allocations, but not necessarily another "server" machine. I know some about server setups, however , people with much experience in Server Set-Ups, will know more about how why and when to do your hardware allocations, and many "Tricks" to increase throughput and performance.
0
 
Ray PaseurCommented:
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now