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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
What was wrong with the answers?
http://support.experts-exchange.com/customer/portal/articles/481419
http://support.experts-exchange.com/customer/portal/articles/481419
ASKER
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?