1

Ok, we run a growing auto parts shopping cart. Our server runs slow and is always crashing so we asked our web host to assist us. They sent us these report but I need some advice in how to process it.

Our plan right now is to take the MySQL database and host it on AWS, but I need to know how to gauge what size instance we need and what service would work best.

I also need to know any tweaks to apache that would improve performance.

Here is the analysis the web host send us

The way these issues come into play are:

1) The very large RAM footprint of your web application processes over 512M plus improper tuning of Apache that allows these web application processes to consume more RAM than is available on your server.

2) The MySQL service on your server like Apache is tuned to allocate much more RAM than is available on your server. This makes the MySQL service unstable thus will require restarts to fix problems that would not occur if it were properly tuned not to use more RAM than is installed.

APACHE

Your server's Apache MaxClients setting is the default == 256

Your server has 32232 MB of memory

The largest Apache web application process is using 572.73 MB of memory

The smallest Apache web application process is using 35.02 MB of memory

The average Apache web application process is using 94.93 MB of memory

Going by the average Apache process, Apache can potentially use 24302.09 MB RAM (75.40 % of available RAM)

Going by the largest Apache process, Apache can potentially use 146618.89 MB RAM (454.89 % of available RAM)

Your server's Apache MaxClients setting should be no greater than 50 if you were only allocating 100% of your server's RAM to Apache web application processes.

Max potential memory usage: 146618.88 MB(454.89 % of available RAM)

Percentage of RAM allocated to Apache 454.89 %

NOTE: this analysis does not take into account any other processes like the MySQL database service running on your server that also requires significant RAM resources to run efficiently, currently a minimum of 1.2GB of RAM.

MySQL

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 11m 51s (9M q [195.147 qps], 41K conn, TX: 22B, RX: 1B)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 1.6G global + 5.0G per thread (151 max threads)
[!!] Maximum possible memory usage: 756.7G (2403% of installed RAM)

[OK] Slow queries: 0% (2K/9M)
[OK] Highest usage of available connections: 23% (35/151)
[OK] Key buffer size / total MyISAM indexes: 1.0G/2.2G
[OK] Key buffer hit rate: 100.0% (9B cached / 1M reads)
[OK] Query cache efficiency: 90.4% (8M cached / 9M selects)
[!!] Query cache prunes per day: 725309
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 147K sorts)
[!!] Joins performed without indexes: 1544
[!!] Temporary tables created on disk: 47% (96K on disk / 203K total)
[OK] Thread cache hit rate: 99% (59 created / 41K connections)
[!!] Table cache hit rate: 5% (512 open / 10K opened)
[OK] Open file limit used: 1% (910/65K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] Connections aborted: 6%

Suggested MySQL mitigtion:

Run OPTIMIZE TABLE to defragment tables for better performance

Reduce your overall MySQL memory footprint for system stability

Adjust your join queries to always utilize indexes

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

Your applications are not closing MySQL connections properly have your developers fix the code so MySQL connections are explicitly closed when the query results are returned.

  • Is your web application custom, or off the shelf? If the latter, what is it? Everything I'm seeing here points squarely at low-quality code. – Michael Hampton Apr 3 '15 at 3:08
  • Xcart, but we have many custom systems various developers built like Point of Sales, Vendor and Stock Management systems that plugin to xcart and the database – Anthony Accetturo III Apr 3 '15 at 3:23
  • I also added you on linked in. – Anthony Accetturo III Apr 3 '15 at 3:29
2

If you're hosting MySQL and Apache on the same server, you should see a performance gain just by moving them to separate machines. MySQL likes to use RAM for caching data, but you won't see a ton of improvement if you're doing a ton of writes (which it doesn't look like you are).

Something else you might consider is getting a load balancer and spinning up two web servers behind it. That would let you get smaller instances and spread the risk out some. AWS has multiple zone within a given availability zone (like 1a, 1b, etc.) and you can have your DB sit in 1a and a mutli-az db copy sitting in 1b, with a web server sitting in zone as well. If 1a goes down, you fail over automatically to 1b. The load balancer would ensure you also split the traffic between servers which means less chance of overloading.

For all of this, I would suggest you try the following

  • Run mysqltuner and see if you can tune your DB first. You'll need this information to help tune your RDS instance later
  • Put your db into m3.medium or a m3.large. Remember, you can always change the instance type later if you find you're overloaded
  • Use two m3.medium EC2 instances for your web servers. Load balanced, I bet they perform better than your monolithic server does now. Consider using a server configuration system like Opsworks to make setup easier. If you find your servers are overloaded, you can always use that scripting to spin up new servers on demand and share the load.
0

Which apache processus modeling are you using? Or sounds like it's preform. Preform is now dated and there other mpm for Apache with fat lower memory footprint. Not all application support them but most should at least work with Apache amp me worker. This should allow to maintain, and maybe increase, the number of concurrent clients while using less ram.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.