I have a small virtual Server: 2 Cores, 2 GB RAM, Windows Server 2012 R2, Plesk, MySQL 5.6 (
my.ini see below). Pagefile is disabled. Taskmanager says 1,3 GB RAM used. Only a single Wordpress installation is running there. The machine is idle, still a simple (text-only page) request take about 8 seconds.
The bottleneck is the database (InnoDB engine). When I uncomment the last line of
my.ini, thus making DB read-only, requests are answere in 0.3-0.8 seconds.
On processing a request, the Windows Ressource Manager shows, that
mysqld.exe is writing a lot to file
ibdata1. I figure that this is due to locking and assuring ACID properties.
So the question: What can I do to make the DB (and thus the web-server) more responsive without changing the hardware?
I already tried all kinds of different settings for MySQL. Vary
query_cache_limit between 0 and 10MB,
innodb_buffer_pool_size between 4MB and 20MB. Added
skip-name-resolve. I even tried to loose ACID properties, by setting
transaction-isolation=READ-UNCOMMITTED. This however, had no effect at all. With the other settings, I could reduce response times of Wordpress pages to around 3-4 seconds.
However, the main problem of heavy write access to
ibdata1 can only be amended by
transaction-read-only. This however, is not a suitable solution, as I need the DB to be writeable to add new articles.
A good idea would probably to use a faster HDD, or to move
ibdata1 to a SSD. However, this is not possible as it is a virtual server. I could upgrade the server (more cores, more RAM, more disk space, but I don't think faster) but I do not see how this should help. Another idea would be to make Wordpress use the
SET TRANSACTION statement at runtime when not needing write access, but this only helps to visitors, not authors/admins. Any other ideas? Other MySQL settings I oversaw? Upgrade to MySQL 5.7 or use MariaDB?
[client] port = 3306 [mysqld] port = 3306 basedir=C:\Program Files\MySQL\MySQL Server 5.6\ datadir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data character-set-server=latin1 default-storage-engine=InnoDB #transaction-read-only