… no comparable variables of how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which are… restrict memory usage. For Permanent tables it is easy. We can look at information_schema to see how much memory is being used by current MEMORY tables: mysql…
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: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
…rows and doing it completely in memory. The plan optimizer picked was very …how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how…MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…with equi-joins. Now let me briefly explain how hash join algorithm works. Suppose you have two… first operand used in the build step is such that the hash table fits in memory. You can read…much of a difference here. Now let’s take a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…can create and never deallocate. Any more? Of course… I can also set a bunch of user variables — they use memory too. And they can…mysql…much, in my opinion. I think it’s far better to use a monitoring tool to watch the actual memory…
Post: How much memory Innodb Dictionary can take ?
May 6, 2010 By 3 Comments
… to restrict that limit. So how much memory can it really take ? Here is some production stats from real system: mysql> select count(*) from INNODB…) mysql> select count(*) from INNODB_SYS_INDEXES; +———-+ | count(*) | +———-+ | 451773 | +———-+ 1 row in set (2.75 sec) In this case The memory stats… not included in “Total Memory Allocated” in SHOW INNODB STATUS any more, as that allocations were moved to use malloc() and so…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
….02 sec) Looking at SHOW INNODB STATUS we can see: History list length 5 Total number … lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count…second on modern systems. So we have tested how much memory is using locking all the rows – so all …
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… but it still can make server unavailable plus in many cases you can use result overload to make MySQL to consume so much memory so it… being unable to run their queries. Stored Procedures – How much memory can stored procedure allocate ? say can you create 1000 variables in stored procedure and…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… 32GB of memory and there is very little load. I see that mysqld is using 10g of memory with… If it’s not possible to use a patched binary, we can use MySQL Proxy, packet sniffing, or other techniques…, and a simple mental calculation can then tell me how much total gain I can get from it. Queries are…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…include others as well. Here’s how you can use our new monitoring plugins to check … allocating too much memory. A runaway server process, or poorly optimized queries, can consume too much memory and cause…the problem has grown much more serious. You can use the pmp-check-mysql-status plugin to alert…
