Site heavy on disk writes?

[bigrover]

I am looking at the graphs on resource usage at our site and the disk writes are far larger than reads. I expected to see the reads be larger than the writes, as most users are reading and occasionally posting. Why would the disk writes be so much larger than the reads? How do I track this down?

[Wayne Luke]

The data as presented doesn't really tell us anything. vBulletin is a database driven application and MySQL will create logs, temp files and other information behinds the scenes when data is requested from it.

What problems are you experiencing that causes your concern?

[bigrover]

The site is very sluggish and slow on our new server. On the upside it isn't throwing errors and dropping content such as css, images, etc. The server load appears to be high, Server Load Averages 5.26 5.26 5.16 | 716 Users Online (417 members and 299 guests). Using top shows:

top - 16:31:46 up 7 days, 19:47,  1 user,  load average: 5.18, 4.49, 4.59
Tasks: 180 total,  10 running, 170 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.8 us,  0.7 sy,  0.0 ni, 58.1 id,  0.1 wa,  0.0 hi,  0.1 si, 38.1 st
KiB Mem :  3880660 total,   193108 free,  1498788 used,  2188764 buff/cache
KiB Swap:  4063228 total,  3847284 free,   215944 used.  1876572 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
11801 oh2admin  20   0  732644  38300  20724 R   8.9  1.0   0:13.90 php-fpm
11555 oh2admin  20   0  804232  39832  23036 R   8.0  1.0   0:31.60 php-fpm
11155 oh2admin  20   0  730764  37468  21716 R   5.4  1.0   0:53.70 php-fpm
13901 mysql     20   0 1272716 345568  12544 S   5.1  8.9 209:54.60 mysqld
11549 oh2admin  20   0  730640  39236  22364 R   4.8  1.0   0:34.96 php-fpm
10917 oh2admin  20   0  730788  37040  21496 R   2.6  1.0   0:56.33 php-fpm
31112 nginx     20   0  185932  55472  12516 S   1.9  1.4  22:51.84 nginx
    9 root      20   0       0      0      0 R   1.6  0.0  82:34.54 rcu_sched
31111 nginx     20   0  186920  60108  12568 S   1.6  1.5  32:06.37 nginx
  408 root      20   0       0      0      0 R   1.0  0.0  29:47.59 xfsaild/dm+
 8847 nobody    20   0 2459824  18496   7660 S   1.0  0.5   0:16.62 httpd
12144 root      20   0  160060   2192   1496 R   1.0  0.1   0:00.08 top
 8911 nobody    20   0 2459824  16524   7824 S   0.6  0.4   0:15.79 httpd
 9132 nobody    20   0 2459824  16584   7860 S   0.6  0.4   0:15.20 httpd
 9373 nobody    20   0 2459824  15836   7868 S   0.6  0.4   0:13.79 httpd
31109 nginx     20   0  181272  48900  12512 S   0.6  1.3  16:33.49 nginx
   12 root      rt   0       0      0      0 S   0.3  0.0   0:08.10 watchdog/1

iostat shows:

[root@host vbadmin]# iostat
Linux 3.10.0-862.el7.x86_64 (host.xyzdomain.net)         05/17/2018      _x86_64_(4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.67    0.23    1.05    0.09   18.65   76.31

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
vda               9.63        34.23       129.29   23117561   87304892
dm-0              9.74        33.13       126.92   22370235   85703721
dm-1              0.16         0.14         0.51      97432     341552
dm-2              0.14         0.94         1.84     635142    1244387

The control panel on the host shows the cpu usage as consistently at 45%. Last night the server loads were running between 10 and 12, I reduced them by cutting back on apache and php-fpm servers. I've done some minor mysql tuning, and disabled a lot of options in vB per instructions I found via Google but not sure what else to do. I've tried a number of system configurations and what I have running now is performing the best and registering the lowest load averages, but we still see responses exceeding 5 seconds on occasion, and averaging 2 seconds for each request.

[Wayne Luke]

You'll need to have someone optimize NGINX and PHP on your server. We can give basics but server optimization is outside the realm of technical support.

What version of PHP are you using? What are you using for an Opcode Cache?

What is the version of your database server? What table engine are you using?

[bigrover]

The nginx is running as a reverse-proxy that is offloading static content. It drops the average load by 25% when it is enabled. I am currently running version 7.0 of PHP. I have tried version 7.1 but it throws up the vbulletin database error page. I haven't tried troubleshooting it yet and I don't have a development site that can run 7.1 either. I was running 5.6 which works too, but 7.0 seems faster and the load is less though I haven't quantified it specifically. Zend Opcache and APCu are running per the phpinfo page. The sql database is mariadb 10.1. I'm not sure what you mean by what table engine I am using. Most of the tables in our db are MyISAM. I cut down on the size of the InnoDB buffers because we were thrashing memory and don't have much data in InnoDB tables.

I am certainly open to running a different opcode cache if that is recommended. I have also read numerous places I should load memcached even though I only have the one server. The database is relatively small (less than 300MB) and should be able to live in memory

[Wayne Luke]

You should use MariaDB 10.2 and convert all tables without a fulltext index to INNODB. It has some parts of MySQL 5.7 backported and is less resource intensive. MySQL 5.7 will probably give the best performance as your version isn't compatible with MySQL 8. Restore your buffer values. INNODB uses row level locks compared to MyISAM which locks the entire table every time it is read from or written to. The session and cpsession tables should remain as MEMORY table types as well. I don't know how the Aria (previously known as Maria) Table engine performs. I doubt that we have ever tested it. Aria is a kind of a hybrid between MyISAM and INNODB.

I also recommend installing MemcacheD on the server and using it in the /includes/config.php file to store your datastore.

Though it sounds like your system is low on memory or your increased the MySQL memory buffers which are per connection, not per server. Plus they are allocated at boot up. So if you allocate 100MB buffers and allow a maximum of 150 connections, then it will allocate 100 MB X 150 connections (just under 15 GB of RAM) when it starts up. Check those and make sure they are properly allocated.

Offloading the database to a second server is another way to improve performance. Then you can write to the offloaded or Master server and replicate everything back to the slave instance on your slave instance for reads. That is what this site did for vBulletin 4.X. Currently, we use three servers. Two for web servers and a third for the database.

[bigrover]

Thank you for the feedback. I am well past my area of comfort but that is where learning happens. The few changes I have made already have dropped the reported cpu load even further and performance has improved, at least enough that it is noticeable.

• I will upgrade to mariadb 10.2.
• How do I tell if a table has a full text index?
• I can install memcached. Are there any basic suggestions on initial config values?
• Regarding the memory buffers, is the problem that they are too large or that we are allocating for too many connections? Where would I find my historical number of connections to guide in that decision?

We are also considering moving to a dedicated server rather than a VPS. I don't know that we can do two dedicated servers at this point, but I assume a good dedicated server with fast disks and plenty of RAM would be better than two VPS's?

[bigrover]

I have upgraded to mariadb 10.2 and have noticed a performance increase and lower resource usage on the server. I also figured out a tedious method of determining if a table has a full text index. So far I haven't found any. Which tables would I expect to have a full text index? I did run into a minor issue with the strict sql rules with an addon mod I was using but I will deal with that later, and it isn't affecting the site.

I tried installing memcached but I can't get it to work. I used the installer i found at the Engintron nginx wiki (https://github.com/engintron/engintr...yApache-4-only)). When I try to use the vB_Datastore_Memcached all I get is a blank white screen when I visit my test site. I also tried some test scripts to validate the install but they fail too. Can you point me to instructions on how to install memcached on cpanel, and verify it's working?

I have installed APCu using the instruction at the same Engintron wiki and am using it for the vB_Datastore_APCu in config.php. It is caching 1.3 Mb of data per the apc.php monitoring script. It is not caching anything else like opcode that I can tell. What should I be using to cache opcode, is it important, how would I know it is being cached?

I was previously loaded the vB_Datastore_Filecache method but removed it when I started getting reports of user logins to other user accounts, I even experienced it myself once. A search of that issue showed it was related to caching and that was the only caching I had enabled. The problem seems to have gone away since I dropped that method of caching the datastore.

Can you point me to a guide for configuring the memory buffers and other important configurable settings for performance?

[Wayne Luke]

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE index_type LIKE 'FULLTEXT%' and TABLE_SCHEMA='vb4_live'

Replace vb4_live with your database name. It should return a list similar to:
"vb4_live" "blog_text"
"vb4_live" "groupmessage"
"vb4_live" "post"
"vb4_live" "post"
"vb4_live" "searchcore_text"
"vb4_live" "searchgroup_text"
"vb4_live" "socialgroup"
"vb4_live" "thread"

For memcached, I recommend downloading and installing it from https://memcached.org/. You would have to discuss with your engineer how to integrate it with NGINX but that shouldn't be necessary. There are no tools in vBulletin 4 to control opcode caching. PHP 5.5+ has its own opcode caching built in. http://php.net/manual/en/book.opcache.php

vBulletin 5 has better caching controls.

[bigrover]

The only two tables that come back from the search you defined are searchcore_text and searchgroup_text, though searchcore_text is listed twice. I only found one FULLTEXT search in that table though. How do I add the FULLTEXT searches to the other tables? Is there a default vB database I can load and copy the index configuration?

[Wayne Luke]

You can install a new copy and it will give you the default configuration.