Slow drupal website on 11.x. Pages load too slow

[MaKento]

Hello,
Newbie to VPS hosting, stats in my sig below. Have poor performance with a drupal and civicrm(drupal based) website and looking to troubleshoot.
Main issue is pages take too long to load, sometimes over 5 seconds and more!
Below is my.cnf and MYSQLtuner results. Any help pointers will be appreciated.

So my.cnf

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
 
# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"
 
 
innodb-file-per-table
innodb_buffer_pool_size=128M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_thread_concurrency=4
max_connections=100
#max_user_connections=40
tmp_table_size=64M
max_heap_table_size = 64M
key_buffer_size=128M
table_cache=400
query_cache_size = 96M
query_cache_limit = 4M
max_allowed_packet = 16M
thread_cache_size = 4
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"
 
[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"

!includedir /etc/mysqld.d

MYSQLTUNER results...

 >>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 135M (Tables: 499)
[--] Data in InnoDB tables: 857M (Tables: 1264)
[!!] Total fragmented tables: 304

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21d 19h 27m 34s (12M q [6.801 qps], 521K conn, TX: 70B, RX: 5B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 421.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 696.0M (54% of installed RAM)
[OK] Slow queries: 0% (174/12M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/28.4M
[OK] Key buffer hit rate: 99.8% (28M cached / 56K reads)
[OK] Query cache efficiency: 79.9% (6M cached / 8M selects)
[!!] Query cache prunes per day: 2159
[OK] Sorts requiring temporary tables: 0% (319 temp sorts / 163K sorts)
[!!] Joins performed without indexes: 17612
[!!] Temporary tables created on disk: 32% (92K on disk / 280K total)
[OK] Thread cache hit rate: 99% (5K created / 521K connections)
[!!] Table cache hit rate: 0% (400 open / 92K opened)
[OK] Open file limit used: 1% (81/4K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[!!] InnoDB  buffer pool / data size: 128.0M/857.5M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 857M)

 

[Tozz]

1.25 GB total RAM is not alot. This does not allow alot of caching for MySQL. The lack of memory will cause MySQL to loada lot of data from disk. Possibly your VM has low/little IO performance, causing performance degradation.
This is not a Plesk related issue. Your VM is just very tiny, and is perhaps simply too little for this Drupal website.

It could also be that your PHP's memory limit is too high, causing your VM to start swapping memory to swapspace. That will decrease performance very fast. It could even crash/hang your VM. Lowering your PHP's memory_limit is probably not an option because Drupal will then no longer function.

On our shared hosting platform we allow PHP processes to consume 500MB of memory per instance. So if there are 10 scriptsrunning at the same time, they are allowed to consume 5GB of memory. You only have 1/5th of that, and in that 1 GB of RAM you also need to load your kernel, all daemons, etc, etc. You probably have only about 200 MB of memory left for your website, and that is just not enough.

I would recommend you host this site on a shared host, because then you will probably be able to use more memory. Otherwise you could increase the amount of RAM by upgrading your VPS

 

[MaKento]

Thanks, upgraded to 4GB memory. Some improvements but pages still hanging.