Yes there’s one hidden.

 
Kelder: Dagb: It’s a good point. I was thinking about that. I was specifically looking at things like mysql DB settings, operating system settings, storage configuration, and things like switching tables from ISAM to InnoDB .

Lamudio: But it did occur to me that a similar exercise should be done on optimising the DB schema, queries and indexes

Willis: But for low level things I probabyl can’t pull out just the queries that matter but instead should probably try to replicate the full typical load

Flax: VSpike: This is loosely true: For planning – competent hardware, competent mysql server config as to buffers and caches, normalized schema design, correct table storage engine choice, proper data types, adequate indexing, competent queries. For troubleshooting – reverse the order. :-

Affleck: I would jump and do myisam to innodb and give enough innodb buffer pool as a early step though.

Millonzi: VSpike, MyISAM vs InnoDB is not a simple “config” thing, it has some implications transactions

Bisarra: As for any other testing: understand what you are testing and how to interpret the results.

Krenning: Myisam to innodb is much simpler than innodb to myisam

Deridder: Jkavalik: yeah, I’ve spoken to the devs and they’d be quite keen to move to InnoDB. They don’t expect any issues from the change but we’d have to functionally test it.

Sienko: They were originally forced to use ISAM for performance reasons, apparently

Moretti: VSpike: ISAM or MyISAM?

Orozco: Yeah MyISAM sorry. Is ISAM a thing? Didn’t realize

Houdek: The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.

Cassem: I probably need to prove first that switching tables to MyISAM a doesn’t break things in the applications and b is not noticeably slower

Halyk: Because trying to optimise a server that is running MyISAM and InnoDB is a lot harder, I think

Croney: VSpike, when it was for performance reasons? non-ACID can save some time but we did not find it worthwhile

Wadman: Jkavalik: probably going back at least 5 years, maybe up to 10 . before my time anyway

Bussy: It was one of those things that might have made sense at the time but was never revisited later

Insognia: Next I want to check some settings relating to the flush log on trx commit and the other file mode related settings . want to change one at a time and measure the changes

Mirto: On our physical server we have it set for maximum safety, but it’s using local disks with battery backed RAID write cache

Stofflet: On AWS we seem to have some latency issues sometimes, but the devs say losing a few seconds of data would not be an issue and they’d prefer the speed :

Bogdan: We have a mysql server we’re having problems starting. it looks like something has happened to some of the indices

Dambrozio: Is there any reason why mysql would jam on “DESC SELECT * FROM .”?

Buhlig: Are there any subqueries in this SELECT?

Bogdan: Http://pastebin.com/Tptt8Ee2

Bogdan: This is the output from when trying to start mysqld

Dambrozio: The actual query just hanged 8 minutes until I stopped it and I wanted to use DESC to see what it actually does

Mcnolty: Dambrozio: which version of mysql?

Losh: Dambrozio: http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html#derived-table-optimization

Alstrom: Dambrozio, “show processlist;” – in some cases optimize and flush etc iirc metadata might be locked so statistics cannot be read

Dambrozio: Naktibalda: 5.1.55-enterprise-commercial-pro-log :

Dambrozio: Jkavalik: nothing unusual appearing there

Trousdale: Run SHOW CREATE TABLE separately for each table in the statement, find a view

Baiotto: Then let it run, see what you get

Dambrozio: Yes there’s one hidden gem