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