…in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table… for foreign-key constraint checking or duplicate-key checking. The most important… reads (non repeatable reads) so in order to prevent problems …
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… website develop in PHP with MYSQL, these are MYSQL values: datadir=/data/mysql socket=/data/mysql/mysql.sock max…size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql…re-restart the server. I’ve checked the security aspect and not an …
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592, 62717660, 62717716, …(group_id,deleted) This pattern of medium size IN lists is typical for a lot of …select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, … local_infileblogpercona.commanual lock_wait_timeoutblogpercona.commanual locked_in_memoryblogpercona.commanual logblogpercona.commanual log_binblogpercona.commanual log_…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…check-mysql-pidfile plugin verifies that MySQL‘s PID file exists. Evidence of contention in the processlist. The pmp-check-mysql-processlist plugin checks…table-checksum tool from Percona Toolkit can generate table checksums, and the pmp-check-pt-table…the use of fixed-size connection pooling, for …
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
…MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL… larger than indexes in many cases. Check however if all…tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 2 Comments
…size specifies current size in pages, so we’re looking at about 2GB of waste in this case. If not you can check history size in…
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
… in run time we can’t detect such change easily because MySQL Server does not seems to have an easy way to check…-mutexes FALSE tmp-table-size 16777216 tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with…
