How do I keep a password login enabled for SFTP transactions (made by Drupal, if this is important) while keeping it disabled for all other SSH key based authentications? Currently all the existing users of the CentOS server use keys to log in and /etc/ssh/sshd_config has PasswordAuthentication no)?
we have Drupal 7 on Ubuntu LTS virtual machine (VMWare) with MySQL 5.1 server on the same physical server, but on a different virtual machine.
The Drupal website is currently under development, therefore there is practically no traffic, however, despite ping and shell access being lightning fast, the Drupal site is running extremely slowly (>5 sec to load any page). After research here and elsewhere I understand that the problem is with the fact that SQL is accessed via network interface, which Drupal apparently does really badly.
I have checked DNS resolution and there should be none either from Drupal side (db host is given by ip, there is a dns entry in /etc/hosts file) nor from MySQL side (skip-name-resolve is on, drupal client is granted access by ip address).
The Drupal devel querylog says:
Executed 336 queries in 5993.89 ms. Queries exceeding 5 ms are highlighted. Page execution time was 7015.35 ms. Memory used at: devel_boot()=5.53 MB, devel_shutdown()=93.35 MB, PHP peak=94 MB.
The wierd thing is that for a single page load about 2/3 of queries are fast, others are exceedingly slow:
1.765 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
575.825 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
13.585 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN (:cids_0)
default
466.9914 DrupalDatabaseCache::getMultipleP A E
SELECT cid, data, created, expire, serialized FROM cache WHERE cid IN (:cids_0)
default
1.841 drupal_lookup_pathP A E
SELECT source FROM url_alias WHERE alias = :alias AND language IN (:language, :language_none) ORDER BY language ASC, pid DESC
default
The situation is remedied for anon access by enabling Drupal cache. The whole of the admin interface, however, is still as slow.
It is the current company policy to have DB server separately. Could there be any other options to speed up Drupal?
The default database configuration says driver 'mysql', maybe I should change it to 'mysqli' or 'pdo_mysql'? If I change it to mysqli, however, the database.inc complains that the relevant driver doesn't exist -- anything to install there specifically for drupal, as mysqli & pdo are both enabled in PHP itself.