I am new to the forums, so I may be a little off with the proper post ‘format.’ Feel free to correct me, I will not take it personal.
So, I have a session table typically has 20K rows in it and on every webpage hit (a few per second), one SELECT and one UPDATE are executed in the same connection. In addition, a ‘DELETE on data more than 2 weeks old’ is performed 5% of the time.
Sometimes, I can get locked out for 100+ sec, meanwhile, the person sitting next to me can access the data immediately.
Here is the info for the table, and the slow queries:
show create table session:
session | CREATE TABLE session ( ctr int(10) unsigned NOT NULL auto_increment, session_id varchar(40) NOT NULL, ip_address varchar(16) NOT NULL, user_agent varchar(50) NOT NULL, referrer_user_id int(10) unsigned default NULL, referrer_addr varchar(100) default NULL, last_activity int(10) unsigned NOT NULL, session_data text NOT NULL,
PRIMARY KEY (ctr),
UNIQUE KEY idx_session_id (session_id),
KEY idx_selects (session_id,user_agent,last_activity)
) ENGINE=InnoDB AUTO_INCREMENT=24402 DEFAULT CHARSET=latin1 |
SELECT *
FROM session
WHERE session_id = ‘06a05c42d9a58515d2bac592c3bdfc8b’
AND last_activity > 1234638681
AND user_agent = ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv’;
ad2.
explain SELECT *
FROM session
WHERE session_id = ‘06a05c42d9a58515d2bac592c3bdfc8b’
AND last_activity > 1234638681
AND user_agent = ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv’;
ad3.
explain select * from session WHERE session_id = ‘a73bc2035d2ca7a58844e4ec52e48c3a’;
ad4.
explain select * FROM session WHERE last_activity < 1235606350;
As for 4 You could add index to the last_activity and check query again.
edit:
Check also
explain SELECT *
FROM session
WHERE session_id = ‘06a05c42d9a58515d2bac592c3bdfc8b’
AND user_agent = ‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv’
AND last_activity > 1234638681
;
(with valid session_id and user_agent of course)
btw. Your user_agent is too short, 50 chars is not enough
Ok, the indexes should be fine. Does this server use query cache ? Xaprb wrote something about mysql freezes: http://www.mysqlperformanceblog.com/2009/03/19/mysql-random- freezes-could-be-the-query-cache/ . It could be also hardware problem (RAID failure). Or someone is running many queries on tables without indexes.
It appears that query_cache_size is disabled, but the query_cache_type is ON, I am not sure if it matters and what it really means.
mysql> show variables like ‘%query%’;
±-----------------------------±--------+
| Variable_name | Value |
±-----------------------------±--------+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
±-----------------------------±--------+
10 rows in set (0.01 sec)
As for your second point, would it make sense that a query takes 100+ seconds even if tables indexes are not set properly?
Is there anything else that I should try? I am open to any suggestions.
Here is what I tried before:
I launched 100 concurrent connections (with httpd_load) and they all returned in under 1 sec. This is a lot more than our typical usage. So it is not a currency issues, but more of a random locking or random cache cleaning issue.
You could set
query_cache_type = OFF;
This should disable cache (“should”, because there could be bug in mysql and some people have had problems even with query_cache_size = 0)
There are several variables to look at. I think that phpmyadmin gives good hints about those variables. Just look under status tab, and search for:
Handler_read_rnd
Handler_read_rnd_next
Select_full_join
Big values and red color mean that there is something wrong with the queries (and especially with indexes).
There is also Created_tmp_disk_tables and Created_tmp_tables. Second one shows how many temporary tables were made inside memory. As You know memory is faster than disk, so it is better when temporary tables are inside memory. As I remember temporary tables could be created by mysql when there is “order by”.
I enabled log-queries-without-indexes and we’ll see what comes up. As for the article, I tried installing innotop and got a bunch of errors. I’ve asked my support server team to install it. Hopefully, they do soon.
this is a VPS, so i’m wondering how i can track whether outside processes are actually affecting our DB? I’ve used top but that shows pretty minimal load on our system.
I currently have my memory pools at, how can i check what their max’s are?