50 tips to boost MySQL Performance Webinar follow up

Thank you for attending the webinar !  Here are the ppt slides.

If you missed it, you can still join the archived event by clicking the URL below.
http://w.on24.com/r.htm?e=748845&s=1&k=171F8C0CECD105B0F4ED721CA6F2C704

There were a lot of attendees and a lot of questions. I could not answer everything during the limited time. But here are finally the answers !

Question Answer
Can MEM be used on community version? Yes, of course.
any known problems for MySQL running on an overprovisioned VMWare environment….for instance, the VMWare admins over allocate CPU for all the Vms? Yes, a busy MySQL server needs cores, memory and fast storage and network. If these ressources are shared by busy servers then performance drops dramatically.
what makes XFS better over EXT4 ? XFS works better with O_DIRECT. XFS had historically better support for SSD (TRIM). See http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-linux-io.html
Does sync_binlog=1 effect on write performance or read performance Write performance only (fsyncs)
What are the disadvantage of setting wait_timeout and interactive_timeout value to 1 ? Any idle query sleeping longer than 1s will be interrupted.
You focus on innoDB, is there no case where MyISAM would be preferable? Not really. MyISAM uses the OS to cache the data and requires a lot of system calls.
MySQL 5.6 introduced read only transactions and full text indexes for InnoDB. It can only think of spatial indexes.
what monitor instruments you recommend in mac os x system. On Mac OS X, you can install MEM to monitor the database. The performance_schema and ps_helper will also work on any platform. DTrace can be helpful too.
How do i check Qcache_free_blocks? show global status like ‘Qcache_free_blocks’;
How Table Partitioning effect the performance on read and write ? Partitioning can improve read and write performance. But it requires more tuning than a normal table. The table and query designs are critical. I would recommend to use as little partitions as possible, use pruning for all queries. Partitioning is the best way to delete useless rows (drop or truncate partitions). If a query does not use pruning, then it is more expensive in terms of locking and table access, especially range index scans. Partitioning is good for big data when the insertion rate is limited by the table size.
Are there any recommendations for running MySQL on ESX? see http://www.vmware.com/files/pdf/Virtualization-for-MySQL-on-VMware.pdf
what is the scope of mysql dba in market as per current trends? I can not answer this question. I know that there is a high demand for MySQL DBAs worldwide
Sir I have E-5 family server getting slow while fetching 1 row from table  
any tips to make it more faster, Using MYISAM It is hard to tell. Again I recommend InnoDB. If you can, please open a support request or submit your question to http://forums.mysql.com/
This entry was posted in conference, tip, Uncategorized. Bookmark the permalink.

2 Responses to 50 tips to boost MySQL Performance Webinar follow up

  1. Trent Lloyd says:

    Your comment about wait_timeout and interactive_timeout is incorrect.

    These timeouts only apply to idle connections (sleeping), not executing connections.

  2. aadant says:

    Yes, Trent, it is correct. I fixed the answer. Percona has something similar for interrupting running queries after a timeout.

Leave a Reply

Your email address will not be published. Required fields are marked *


+ 3 = six

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">