Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Andrew Moore
Hey Brad. What version are you using? My immediate thought is to check if innodb_stats_on_metadata is off. If it is on, switch off and check your timings again. Regards On 17 Mar 2014 04:40, Brad Heller b...@cloudability.com wrote: Hey all, I'm trying to figure out how InnoDB executes a SHOW

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Brad Heller
Hey Andrew, I'm on 5.5.27. Good thought. Just flipped that setting off and getting the same results. It pretty clearly seems to be InnoDB: If I create a HEAP table, I don't get this behavior. FWIW, I have (and always have had) innodb_file_per_table enabled, but my tablespace file is still

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Morgan Tocker
Hi Brad, I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE query so I can figure out what could possibly have made them suddenly slow down? mysql SHOW CREATE TABLE `my_table`; ... 1 row in set (37.48 sec) We tend to execute many of these statements concurrently, but it's

Questions about building a dedicated MySQL server.

2014-03-17 Thread Mister Vlad
I am looking at building a dedicated MySQL server... was wondering about the downside to using SSD drives? My thoughts was going 2 servers, with 4 drives each in raid 5 (3+1) configuration. Is this a good idea? I was originally thinking about going Raid5(3+1) and Raid 1 (Mirrored) but that

Re: Questions about building a dedicated MySQL server.

2014-03-17 Thread Carsten Pedersen
On 17-03-2014 16:21, Mister Vlad wrote: I am looking at building a dedicated MySQL server... was wondering about the downside to using SSD drives? My thoughts was going 2 servers, with 4 drives each in raid 5 (3+1) configuration. Is this a good idea? I was originally thinking about going

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Brad Heller
Hey Morgan, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. https://gist.github.com/bradhe/c9f00eaf93ac588b8339 We have the defaults for

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Morgan Tocker
Hi Brad, That sounds right. Here's the process list (scrubbed) and the show engine innodb status. Notice that all of the SHOW CREATE TABLE aren't for hte same table, just got cleaned up that way. It shouldn't matter if they are for the same or different - in 5.5 there is one table open cache

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Brad Heller
Hey Morgan, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behavior. mysql select @@table_definition_cache, @@table_open_cache, @@innodb_file_per_table, @@innodb_open_files;

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread shawn l.green
Hello Brad, On 3/17/2014 5:50 PM, Brad Heller wrote: Hey Morgan, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behavior. mysql select @@table_definition_cache, @@table_open_cache, @@innodb_file_per_table,

Re: SHOW CREATE TABLE suddenly slow on InnoDB?

2014-03-17 Thread Morgan Tocker
Hi Brad, We actually only have about 60 tables in that database. I've tried increasing the cache and open tables limits and get the same behaviour. Hmm.. Shawn’s guesses are probably better than mine then. A few other tests I've tried: 1. Stand up a new machine, dump just the schema in