Nginx – MySQL Execution Time Spikes

mysql_pconnect is going to be faster, but, is going away (and I don't believe it is even supported with mysqli). Persistent connections remove the connection/teardown time.

Is your mysql connecting via TCP or socket? If TCP, is compression or SSL turned on? Compression is good when you expect larger responses, but, on small responses you spend more cpu time compressing/decompressing on both sides. You want to use compression about 99% of the time. SSL connections between php and mysql will be definite performance hinderances.

If you are connecting via TCP, skip-name-resolve in mysql will speed things up a bit. Using an IP rather than a domain name in your grants would then be required.

However, your assumption that these queries would likely be in the cache is dependent on how large your cache is and how large your result set is. If your queries average 400k per result set (assuming a 4k article length), you would need at least a 40mb query cache to cache things. If you are sending back any integer fields, remember that the libmysqlclient library converts integers to ascii, and then php must convert it to whatever datatype when it receives the results.

mysql_fetch_assoc is slightly slower than mysql_fetch_row, but, not enough that you would find it worthwhile to convert your code to use it.

I believe you're probably running into an issue with the connection to the mysql server. If you're using TCP, you have a few changes you're going to want to make. If you're using a socket connection, then you're probably not going to be able to tweak too much more performance out of it.

@pQd, in my experience, I have found that the query optimizer on a limit clause on an indexed query will be answered from the index, and using a ranged index wouldn't provide much change in the results. The ranged query also wouldn't take into account article IDs that were later deleted which would throw off pagination. Article 100-150 if 20 of those articles are deleted would return 30 rows, which may not fill your page properly. A limit clause for data presentation is usually the right way. The only time mysql will do the entire query is when you use sql_count_rows which is another useful tool for pagination. You want to make sure you are answering those queries from the index to prevent tablescans.

You need to look into mk-query-digest

This tool can actually run against mysql for a specific length of time you designate and it will produce a map of the top 20 queries by query pattern and give you average running times.

Here is an excellent YouTube video on how to use it as an adhoc slow log

UPDATE 2011-07-21 15:58 EDT

I have a pleasant surprise for you !!! If you are using MySQL 5.1, you can convert the general log and slow log into tables. I am using it right now with a big client. Here is how you can do it:

In the mysql schema, there are the tables general_log abd slow_log.

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.05 sec)

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.11 sec)

You can activate the use of them by setting this in /etc/my.cnf

[mysqld]
log-output=TABLE

Of course, who wants a CSV file for a general log and slow log ??? SURPRISE : CONVERT THEM TO MYISAM AND ADD AN INDEX TO THE TIME OF EACH ENTRY !!!

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;
SHOW CREATE TABLE mysql.slow_log\G

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL slow_query_log = @old_log_state;

Please note that the general log has the column event_time and the slow log has the column start time. Only the slow log has the column query_time.