10

start:disclaimer

I'm not a MySql Server DBA; I know mostly of MSSQL - which is why I need your help.

end:disclaimer

I've been asked to check why the MySql server engine is performing poorly - I have not seen nor held the databases involved and would like to know where to start.

Where do I start?

What questions should I be asking those who have access to MySql - I don't even know if they are using phpmyadmin or some other tool.

Essentially:

  • What items should I ask for and how would I respond to each piece they give?

What are the key items to ask for when the issue is in the performance of the database?

in MSSQL I can check sp_who2 for existing connections to see if anything is blocking, what is the counterpart in mysql? * do not have to be specific as there can be many kinds of results for each item but would like to help get the ball rolling since it is affecting users - apparently they set up the mysql engine without having a dba on-board.

Improve this question
4
  • Have you checked the usual ressource issues - CPU full, RAM full / not used, IO overloading? The 101 stuffyou would start with on a sql server? – TomTom Nov 13 '13 at 8:36
  • Haven't had a chance to - I just want to be prepared once i come in so that everything keeps moving - it is possible the people on-site checked already and with both app and db servers with high-specs (company splurged) i would like to focus on what happens if the hardware is not the problem – Yasker Yasker Nov 13 '13 at 8:45
  • Out of esxperience - start there. I have seen "high end servers" with one SCSI hard disc (that they must have pulled out of some ebay auction) that had multi second response times... I would validate the IO side. It also will help - too much IO can point to missing indices if the numbers are just totally ridiculous. – TomTom Nov 13 '13 at 8:48
  • Try MySQL creates temporary tables on disk. How do I stop it? and A description of how temporary tables are created and managed in MySQL. – user30431 Nov 13 '13 at 9:44
9

  • Log slow queries- If your system has a ton of queries, it gets tougher to find out which queries are slowing your system. MySQL provides a tool to log slow queries for further analysis http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

  • EXPLAIN Extended command shows details about your queries when your have no idea what is happening http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html

  • To speed up your queries use Index - A good practice is add index by seeing which fields are in the WHERE clause add index for them. Also if you are retrieving all fields from a table, the query gets slower by fetching all data from disk. In SELECT query you should specify which fields you need to bring instead to bring them all with *
  • Make use of Query Cache http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html
  • Make sure that your MySQL server configuration file options are optimized according to your hardware http://dev.mysql.com/doc/refman/5.5/en/option-files.html
  • Make sure that you are using optimized data types while creating a table structure For example "Comments" fields has size of 256 characters, reply it to MYSQL with a field with type VARCHAR(256) instead of using TEXT. The query will be much faster.

Procedure_Analyse() can help you in finding optimal data types:

http://www.mysqlperformanceblog.com/2009/03/23/procedure-analyse/

http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.