I recently updated my Drupal site in a multi-webhead environment and am trying to configure MySQL using the InnoDB engine. I notice that SELECT queries are faster than setting, but UPDATE queries are slower in production.
- Staging: on a virtual machine with a LAMP stack on it.
- Production: dual web pages with load balancer. Dedicated MySQL server and a second backup database server.
My system administrator informs me that the delay is due to 1) remote database connection and 2) binary logging for data replication between two database servers.
I am new to InnoDB and multi-server environment. I would like to know if the output from the MySQL profile confirms the parameters of my server, or if there are even more opportunities for further optimization of the production of the MySQL server.
This is what I ran from intermediate and production databases. For comparison, I modified the output with column numbers. Please note that the query is faster for production in each column of the table, except for one with the status "end". The end phase, where does the binary log run?
mysql> SET profiling = 1;
mysql> UPDATE node SET created = created + 1 WHERE nid = 100;
mysql> SHOW profile;
+----------------------+----------+------------+
| Status | Staging | Production |
+----------------------+----------+------------+
| starting | 0.000100 | 0.000037 |
| checking permissions | 0.000014 | 0.000006 |
| Opening tables | 0.000042 | 0.000017 |
| System lock | 0.000007 | 0.000004 |
| Table lock | 0.000009 | 0.000003 |
| init | 0.000076 | 0.000030 |
| Updating | 0.000062 | 0.000022 |
| end | 0.000031 | 0.002159 |
| query end | 0.000006 | 0.000003 |
| freeing items | 0.000010 | 0.000003 |
| closing tables | 0.000009 | 0.000002 |
| logging slow query | 0.000005 | 0.000001 |
| cleaning up | 0.000004 | 0.000001 |
+----------------------+----------+------------+
| Total | 0.000385 | 0.002288 |
+----------------------+----------+------------+
source
share