Due to some performance issues I've been optimizing several SQL queries and adding indexes to certain tables/columns to speed up things.
Been running some time tests using microtime() in PHP (looping the queries a couple hundred times and calling RESET QUERY CACHE in each loop). I'm somewhat baffled by the results from one of the functions that does 3 things:
- Inserts a row in a
sessionstable (InnoDB). - Updates a row in a
userstable (InnoDB). - Sends session ID to remote server which inserts the session ID in a session table of it's own (MongoDB).
Step 1. generally takes 30 - 40 ms, step 2. 20 - 30 ms and step 3. 7 - 20 ms.
I've tried looking up some expected query times for MySQL, but haven't found anything useful, so I don't know what to expect. Having said that, those query times seem somewhat high and I would definite not expect the web request to finish faster than the MySQL queries to the local database.
Any idea if those query times are reasonable compared to the web request?
SQL/system information
Both servers (the remote and the one with the MySQL database) are virtual servers running on the same physical server with shared storage (multiple SSD raid destup). The remote server has a single CPU and 2 GB RAM assigned, the MySQL server has 8 CPUs and 32 GB RAM assigned. Both servers are on the same LAN.
The sessions insert query:
INSERT INTO sessions (
session_id,
user_id,
application,
machine_id,
user_agent,
ip,
method,
created,
last_active,
expires
)
VALUES (
string, // session_id
int, // user_id
string, // application
string, // machine_id
string, // user_agent
string, // ip
string, // method
CURRENT_TIMESTAMP, // created
CURRENT_TIMESTAMP, // last_active
NULL / FROM_UNIXTIME([PHP timestamp]) // expires
)
The sessions table (contains ~500'000 rows);
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| sessions_id | int(11) | NO | PRI | NULL | auto_increment |
| session_id | char(32) | NO | UNI | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| application | varchar(128) | NO | | NULL | |
| machine_id | varchar(36) | NO | | NULL | |
| user_agent | varchar(1024) | NO | | NULL | |
| ip | varchar(15) | NO | | NULL | |
| method | varchar(20) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| last_active | datetime | NO | | NULL | |
| expires | datetime | YES | MUL | NULL | |
+-------------+---------------+------+-----+---------+----------------+
The users update query:
UPDATE users
SET last_active = string // For example '2016-01-01 00:00:00'
WHERE user_id = int
The users table (contains ~200'000 rows):
+------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | NULL | |
| first_name | varchar(256) | NO | | NULL | |
| last_name | varchar(256) | NO | | NULL | |
| info | varchar(512) | NO | | NULL | |
| address1 | varchar(512) | NO | | NULL | |
| address2 | varchar(512) | NO | | NULL | |
| city | varchar(256) | NO | | NULL | |
| zip_code | varchar(128) | NO | | NULL | |
| state | varchar(256) | NO | | NULL | |
| country | varchar(128) | NO | | NULL | |
| locale | varchar(5) | NO | | NULL | |
| phone | varchar(128) | NO | | NULL | |
| email | varchar(256) | NO | MUL | NULL | |
| password | char(60) | NO | MUL | NULL | |
| permissions | bigint(20) unsigned | NO | | 0 | |
| created | datetime | YES | | NULL | |
| last_active | datetime | YES | | NULL | |
+------------------------+---------------------+------+-----+---------+----------------+
