Solved

Design Web Application with real-time status

Posted on 2014-09-21
9
547 Views
Last Modified: 2014-09-23
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
Comment
Question by:Kinderly Wade
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Accepted Solution

by:
Ganesh Kumar A earned 150 total points
ID: 40335916
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 40336464
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
 

Author Comment

by:Kinderly Wade
ID: 40337087
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40337114
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Kinderly Wade
ID: 40337133
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
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 100 total points
ID: 40337417
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
 

Author Comment

by:Kinderly Wade
ID: 40337542
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
 
LVL 33

Expert Comment

by:Slick812
ID: 40338098
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40339031
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now