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

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, @@inno

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 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 cac

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 table_definition_cac

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 Raid

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 mig

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, b

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 giganti

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" wrote: > Hey all, > > I'm trying to figure out how InnoDB executes a SHOW CREATE TABLE quer