1

I am working on a Magento website, and having a few difficulties with server load. Our site receives around 2,500 pageviews/day on average. I have been working on optimizing the website, and I am sure there is more to do, however I need a quick solution as I am loosing sales.

Currently, the site is located on a HostGator dedicated server (Centos, Xeon 3.3 , 1Gbps Uplink, 16GB Ram, 1TB Raid 1, 25TB Bandwidth). It is running Apache, Varnish, Memcached, and Cloudflare over the top of everything. On low periods, the load averages stay low (0.29 0.22 0.19), at other times they will jump to 6,7,8,9s on the averages.

I had seen some suggestions about using a dedicated SQL server. I have searched and found different providers, however not sure when I need to look for when dealing with SQL servers. I am assuming a SSD would be a minimum, however do I need more RAM or better CPU, or both? The Magento database is 15GB+-. I am also worried about the communication between my web server and the SQL server affecting load speeds for customers, could this be a problem?

  • could you please run this command php -f shell/log.php -- status – MagenX Nov 4 '15 at 14:47
  • @MagenX imgur.com/G81Nzr2 – Brett Adams Nov 4 '15 at 14:50
  • logs are ok, but anyway you have not enough memory to run this database, you need at least double to cover db size + buffers and for apache/php/varnish/memcached – MagenX Nov 4 '15 at 14:58
  • you have to start monitor your database activity with mytop to make sure database is the problem, check queries and query time. also I/O could be a problem too, with bad drives – MagenX Nov 4 '15 at 15:00
  • If it comes and goes, you need to look at what is causing the "flare up". It sounds like you may have a module that goes awry under certain conditions. You shouldn't need a terribly strong hosting environment for 2,500 page views per day. – TJ Gamble at Jamersan Nov 4 '15 at 16:22
1

Below is a not so brief summary from my on staff server technician on how he would go about finding an issue like what you are reporting. I hope this helps you track it down.


Check historical load average using the sar command:

for day in $(find /var/log/sa -type f -name 'sa*');do sar -q $day done

This will give you the load average taken every hour of the day for the past month (assuming that you have sar installed). If you do not have sar installed, install it with your package manager. Look for trends. If your load sky rockets at a specific time of the day every day for a short period then look for cron jobs, including those that are part of web application code, that run at these times.

If you don't have sar then you may want to go straight to the 'top' command while the server is running slowly. Watch the cpu usage, load, and iowait to see where the bottleneck is. A high load with a low iowait suggests that there is a process is simply require a lot of cpu, while high load coupled with high iowait suggests that something disk reading/writing is causing the bottleneck. If the load is high, but there is little to no iowait then look at the process list to see what is using the most CPU. If the process name does not make clear what file it is then you can get the pid of the process (far left side of top) an run the command 'lsof -p ' to get a list of files opened by the process, or look around at /proc/ to see things like the file 'cmdline', which will tell you what command started the process.

If iowait is high then you should start by looking at disk usage. If you are on a single disk, raid 0, or raid 1 system then having a disk more than about 80% full will make a major impact. If running raid 5 or 10, especially raid 10 with 6 or more disks in the array, you can get up to around 95-97% before it is a major problem in most cases. A full disk causes the load to rise due to instructions getting queued becuase the currently executing instrunctions are waiting on some disk IO to finish while the disks are taking a long time running seeks to find empty space.

If your disk usage is fine then you should run a program called iotop to see what process is using the most disk io. Remember that bandwidth isn't necessarily the problem. It causes a lot more wait often times to do a lot of random reads and writes than it does to write more bytes in sequence at once. Normally the offending process will be doing more iops and the Mbps written isn't that important.

Common iowait culprits: mysql queries that write temp tables to disk or do huge full table scans without indexes. You can see these temp tables being written to disk as they happen by running "ls -lah /tmp|grep '#'|grep mysql". This will even tell you the size of the temp table that is being written. To use this method you have to run the command over and over again very quickly. Mysql can write and remove even large temp tables (200M+) in less than a second, so after running the command in your terminal once you can use the up arrow followed by enter to replay that last command or do something like "watch -n.2 'ls -lah /tmp|grep '#'|grep mysql".

If you do find that temp tables are the culprit, or want to further investigate queries that could be the issue, then mysqladmin is your best friend. The command 'mysqladmin proc' will show the mysql process list including the queries themselves. Normally you will have to use the -h, -u, and -p arguments for host, user, and password with it. This is another command that should be run over and over as the queries come and go very quickly.

Another issue I have often found is iowait generated by having too many inodes (files) in one directory. This is common when there is a crontab that generates output to stdout. In this case the output gets emailed to the crontab user's email address. On a stock Ubuntu or CentOS installation this would be something like a single file in /var/mail/, however if email aliases are set up, as with a cPanel server then the email is sent to that users actual dovecot/courier mail box, with Maildir using a separate file for each message. I have seen servers where there was a crontab running several jobs once per minute that generated an email on each run. This cron user's email account had millions of files in one directory. It was crippling a 24 core VPS hosting server with an 8 disk raid 10 array. Make sure that any cron jobs end with stdout and stderr being redirected to either /dev/null, or a log.

Logs that rotate on a schedule without clearing dated logs are a common issue as well. Check anywhere that you store logs and make sure that there aren't any building up into the 10's of thousands. You may be able to use repquota -a to view how many inodes are owned by each user to aid in this. This depends on whether or not you have quotas turned on or not in the file system.

If none of these things shine a light on the problem then we should look at network wait.

99% of the time that network wait is an issue at the same time every day it is a backup running and being shipped off somewhere.

Either from that server or by the hosting company

0

oh ok, you have cPanel server, then we know what is the problem. your /etc/my.cnf file is probably empty or has some basic 3-4 lines from default cPanel setup?? also apache server usually has some plugins compiled and loaded that you dont really need and they slowdown and make connection stale, etc. as i said you need enough memory to run big databases. to check whats going on with your current mysql parameters you need:

cd
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

to install mytop you need to do next:

/scripts/perlinstaller --force Getopt::Long
/scripts/perlinstaller --force DBI
/scripts/perlinstaller --force DBD::mysql
/scripts/perlinstaller --force Term::ReadKey

cd /usr/local/src
wget https://launchpad.net/ubuntu/+archive/primary/+files/mytop_1.9.1.orig.tar.gz
tar xzf mytop_1.9.1.orig.tar.gz
cd mytop*
perl Makefile.PL && make && make install

here is some basic mysql parameters you can play with:

https://raw.githubusercontent.com/magenx/magento-mysql/master/my.cnf/my.cnf

also open top and filter it by memory (Shift + f then n then Enter). and when load goes high make sure that this value does not go above 10. if it more then your disk is not so good. enter image description here

also check your tables size, maybe you can truncate some useless info like some extensions have log tables over few gig

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.