Posted by Agiss on November 11, 2016 at 6:29pm
Hi guys
I am running a dynamic classified ads website on Drupal 7 and I wonder what would be the best database configuration for optimum performance.
InnoDB, or MyISAM?
What would be the best settings in my.cnf?
The hardware is a Cpanel/MySql VPS with 6 x 2.9GHz Xeon CPU Cores and 11GB Dedicated RAM.
Many thanks
Comments
InnoDB
You'll want MySQL 5.7 or MariaDB 10.1. InnoDB all the way. If you need some help with the settings https://www.drupal.org/project/apdqc has some really good recommendations on the status report page; also if you're using the DB as a cache, it's a great alternative compared to what's in core.
Thanks Mikeytown, I am
Thanks Mikeytown,
I am currently using MySQL 5.6.3 with InnoDB tables. I am not confident enough to use MariaDB as yet.
APDQC couldn't be installed, it needs some options that are currently disabled on Apache.
I'll come back to this as soon as I have the status report.
I installed APQDC module and
I installed APQDC module and these were the recomendations:
For settings.php-
$databases['default']['default']['unix_socket'] = '/var/lib/mysql/mysql.sock';
$databases['default']['default']['host'] = NULL;
$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';
$conf['cache_default_class'] = 'APDQCache';
$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc';
$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';
For my.cnf-
innodb_checksum_algorithm = crc32
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_spin_wait_delay = 24
innodb_sync_spin_loops = 200
innodb_write_io_threads = 16
join_buffer_size = 8M
metadata_locks_hash_instances = 256
binlog_format = ROW
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 25
wait_timeout = 600
read_rnd_buffer_size = 8M
table_open_cache_instances = 16
thread_cache_size = 10
innodb_autoinc_lock_mode = 2
innodb_log_file_size = 256M
table_open_cache = 2048
I also enabled OPCACHE and updated my.cnf as below
default-storage-engine=InnoDB
innodb_file_per_table=1
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000
innodb_checksum_algorithm = crc32
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_spin_wait_delay = 24
innodb_sync_spin_loops = 200
innodb_write_io_threads = 16
join_buffer_size = 8M
metadata_locks_hash_instances = 256
binlog_format = ROW
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 25
wait_timeout = 600
read_rnd_buffer_size = 8M
table_open_cache_instances = 16
thread_cache_size = 10
innodb_autoinc_lock_mode = 2
innodb_log_file_size = 256M
table_open_cache = 2048
Based on the gtmetrix.com the website loading time remain the same. :-(
Any advice?
Loading time
If you want to speed up the backend using PHP 7 is by far the biggest win if you're using PHP 5.x. Database speed usually shows up as an issue when your site is getting hammered; the above changes you made should help you out in this case, not with single page request speed though. What made you think the DB is the bottleneck in your case? The page cache is what you're testing which shouldn't be slow.
Using https://gtmetrix.com/reports/www.drupal.org/FySw0N2S as an example, if you go to the waterfall tab the first request is for the backend (php & mysql). This shows 28ms. Everything else (862ms) is the frontend.
Gtmetrix mainly deals with frontend performance testing. In order to improve this you'll want to use AdvAgg. http://drupal.stackexchange.com/questions/107311/eliminate-render-blocki... is an answer directly from the readme explaining how to configure it for speed. https://www.drupal.org/node/2493801 explains how drupal.org is configured.
Thanks a lot. I will try this
Thanks a lot.
I will try this and get back.
Cheers
@mikeytown2 is right, if you
@mikeytown2 is right, if you are serving cached pages to anon users, AdvAgg is going to make a bigger difference in load and render times then most back-end optimisations
IF your site is live already make sure that under /admin/config/development/performance all of the Bandwidth optimisation options are checked off. These settings are just a very basic version of what AdvAgg does, but it will get you small boost right away while you get AdvAgg setup.
As a note, other options that do similar things as AdvAgg include:
AdvAgg is the way to go if you need an all Drupal option, or your site serves alot of logged in users. You can also layer on a CDN like CloudFlare (with RocketLoader off) on top of AdvAgg.