… sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large blobs. It is easy to check though… can even go deeper to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
…1000 rows and doing it completely in memory. The plan optimizer picked was very good …check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used…the following explanation from Timour Katchaounov in MySQL mailing list archives I have some …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… versions for tables (increment each time table is updated) and check whenever version matches for all tables on which query depends… exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local memory, which is great however memory amount might be limited especially…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… system check out Thinking Clearly About Performance by Cary Millsap. So how we can use this data to estimate capacity of MySQL system… of memory for example is unlikely to help. One helpful way to use this information is to compare systems with different memory amount having same workload. You will often see increasing amount of memory…
Post: Getting MySQL Core file on Linux
August 26, 2011 By 1 Comment
… may be looking at many times the amount of memory MySQL consumes worth of disk space. You have to …/core_pattern echo “1″ > /proc/sys/kernel/core_uses_pid First we enable dumping cores of suid applications… more core file options and some more explanations check out this Fromdual page it has a lot …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…. The pmp-check-unix-memory plugin checks for a single process consuming too much memory and/or for too little free memory in general. This… the problem has grown much more serious. You can use the pmp-check-mysql-status plugin to alert when the server’s Uptime… checking that the server is alive and responds to a query. The pmp-check-mysql-status is also very flexible and can be used…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the amazing MySQL manual, especially the option and variable …status_writesblogpercona.commanual innodb_use_native_aioblogpercona.commanual innodb_use_sys_mallocblogpercona.commanual …idblogpercona.commanual shared_memoryblogpercona.commanual shared_memory_base_nameblogpercona.commanual show_slave_…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did not add the… tests. We start by checking if there is any performance regression when the variable-length records are not used, i.e. if… inserted). In the second test we check if the dynamic row format delivers the memory savings for VARCHAR fields agains the fixed…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…KEY `j` (`j`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row…12 07:31:51 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum…should take half GB of memory, which is small fraction of memory used on serious systems. …
