network profile
| bio | website | about.me/ericrrath |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 1 year, 1 month |
| seen | 2 days ago | |
| stats | profile views | 8 |
|
Apr 15 |
awarded | Supporter |
|
Mar 20 |
awarded | Popular Question |
|
Jun 8 |
comment |
MySQL query took 98 seconds to run but EXPLAIN looks okay I think rows_examined excludes index access. This might be far-fetched, but maybe the pages containing the indexes were not in the pool, had to be loaded from disk, and high IO led to contention and slow performance. How large is the database in question? Do you have access to sar logs, or anything else that would show CPU, IO, etc on the system at the time of the slow query? That might shed some more light on the issue. |
|
Jun 8 |
comment |
MySQL query took 98 seconds to run but EXPLAIN looks okay It's also possible that the original query took a long time due to other activity on the server at the time. I.e. MySQL may have been contending with another process for some shared resource like CPU or disk IO. |
|
Jun 8 |
comment |
MySQL query took 98 seconds to run but EXPLAIN looks okay The size of the buffer pool is controlled by the innodb_buffer_pool_size ( dev.mysql.com/doc/refman/5.5/en/… ). But I think you might want to keep it around, and if you have enough memory, adjust the size of the buffer pool to accomodate the "active set" of your data. |
|
Jun 1 |
awarded | Teacher |
|
Jun 1 |
comment |
MySQL query took 98 seconds to run but EXPLAIN looks okay Sorry, I should have been more clear. If you're using the InnoDB storage engine, the data necessary to satisfy the query might be on disk, but not in the InnoDB buffer pool. The first execution will cause InnoDB to load this data from file into the pool, and subsequent exuections will not induce that expensive file IO. Even with MyISAM, you might see similar behavior with data getting cached by the filesystem, although I'm not as well-informed on OS-level details like that. |
|
Jun 1 |
answered | MySQL query took 98 seconds to run but EXPLAIN looks okay |
|
Jun 1 |
answered | I've got 16GB of ram, how should I configure MySQL Server? |
|
Mar 26 |
comment |
When are InnoDB table index statistics updated? I checked row0mysql.c for 5.5.20. It defines a function row_update_statistics_if_needed that behaves as described: it calls another method dict_update_statistics if the table-update counter is over 2 billion, or if the table-update counter is greater than 1/16 of the number of rows in the table. This row_update_statistics_if_needed method is called on insert, update, and cascade-update. |
|
Mar 26 |
comment |
When are InnoDB table index statistics updated? I found a Percona reference --mysqlperformanceblog.com/2011/10/06/… -- that suggests the statistics are updated when the table size changes by 1/16, but I can't find any corroboration for that statement. Do you know if that's true or not? |
|
Mar 26 |
awarded | Scholar |
|
Mar 26 |
accepted | When are InnoDB table index statistics updated? |
|
Mar 26 |
awarded | Student |
|
Mar 26 |
comment |
When are InnoDB table index statistics updated? Thanks. If It turn it off, when will index statistics be updated? |
|
Mar 26 |
asked | When are InnoDB table index statistics updated? |
