I've set long_query_time to 1 sec and found only 2 query which took 1 sec to execute at 2 different points of time since yesterday. So I don't suppose such 2 queries cause my first byte time to be 3 seconds most of the times. My site is based on WordPress by the way.
I'm using a m1.xlarge RDS MySQL instance by the way. I have followed the tuning guide here.
This is the statistics of my RDS instance right now
And this is phpMyAdmin's alerts
Variable Value Description
Aborted clients 1 The number of connections that were aborted because the client died without closing the connection properly.
Binlog cache disk use 13 The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Created tmp disk tables 1 M The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
Handler read rnd 65.8 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler read rnd next 2.9 G The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Innodb buffer pool reads 9.3 k The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb row lock time avg 1.2 k The average time to acquire a row lock, in milliseconds.
Innodb row lock time max 11.2 k The maximum time to acquire a row lock, in milliseconds.
Innodb row lock waits 631 The number of times a row lock had to be waited for.
Opened tables 9.1 k The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Qcache free blocks 13.7 k The number of free memory blocks in query cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement.
Qcache lowmem prunes 30.4 k The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.
Select full join 34.7 k The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Slow queries 33 The number of queries that have taken more than long_query_time seconds.
Sort merge passes 2 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Table locks waited 862 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
And this is phpMyAdmin's advices:
There are lots of rows being sorted. While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting
There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins
The rate of reading the first index entry is high. This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.
Many temporary tables are being written to disk instead of being kept in memory. Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group
Many temporary tables are being written to disk instead of being kept in memory. Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the MySQL Documentation
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
The rate of opening tables is high. Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.
The rate of opening files is high. Consider increasing open_files_limit, and check the error log when restarting after changing open_files_limit.
Too many table locks were not granted immediately. Optimize queries and/or use InnoDB to reduce lock wait.
The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. Especially on a system with a lot of writes to InnoDB tables you should set innodb_log_file_size to 25% of innodb_buffer_pool_size. However the bigger this value, the longer the recovery time will be when database crashes, so this value should not be set much higher than 256 MiB. Please note however that you cannot simply change the value of this variable. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also this blog entry
The InnoDB log file size is inadequately large. It is usually sufficient to set innodb_log_file_size to 25% of the size of innodb_buffer_pool_size. A very big innodb_log_file_size slows down the recovery time after a database crash considerably. See also this blog entry
