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.

Hector
  • 1,012
  • 1
  • 8
  • 22
Yasker Yasker
  • 123
  • 2
  • 2
  • 7
  • 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 08: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 08: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 08:48
  • Try [MySQL creates temporary tables on disk. How do I stop it?](http://dba.stackexchange.com/q/53201) and [A description of how temporary tables are created and managed in MySQL](http://stackoverflow.com/questions/13259275/mysql-tmp-table-size-max-heap-table-size). – user30431 Nov 13 '13 at 09:44

1 Answers1

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

Paul White
  • 67,511
  • 25
  • 368
  • 572
Mahesh Patil
  • 2,946
  • 14
  • 22