…index` ( `transactionid` int(11) NOT NULL AUTO_INCREMENT, `dateandtime` datetime DEFAULT NULL, `cashregisterid` int(11) NOT NULL, `customerid` int(11) NOT…using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…-bdb default-time-zone=America/Caracas log-slow-queries=/var/log/mysql-slow-queries.log long_query_time=15 log-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache… server-id = 1 log-bin=/data/mysql/drbd expire_logs_days = 5 max_binlog_size = 100M [client] socket=/data/mysql/mysql.sock When I start…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…use this simple script: while true do date >> ps.log ps aux | grep mysqld >> ps.log…query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index…not): mysql> …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… | i | +——+ | 21 | | 25 | | 26 | | 30 | +——+ Phantom reads do not occur if you’re simply doing a …(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost… MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL …log_binblogpercona.commanual log_binblogpercona.commanual log_bin_indexblogpercona.commanual log_bin_trust_function_creatorsblogpercona.commanual log_errorblogpercona.commanual log_isamblogpercona.commanual log_outputblogpercona.commanual log_queries_not_using_indexesblogpercona.commanual log…
Post: Slow Query Log analyzes tools
September 6, 2006 By 83 Comments
…logging queries which do not use indexes even if they take less time (–log-queries-not-using-indexes) Slow query log is great to spot really slow queries…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… heavier ones? It’s probably not uncommon someone uploads a really badly designed application, which runs queries not using indexes or which examine a lot… slow log live, but that is a bit ugly solution. That is where this patch can be very useful. With simple MySQL SHOW… by the number of queries issued or any other information find active tables which do not have any indexes used meaning table scans nag…
Post: When EXPLAIN can be misleading
November 12, 2006 By 1 Comment
… with limit. The other little annoyance is – MySQL will report these queries to slow query log if –log-queries-not-using-indexes is enabled which may flood it quite badly. Too bad –min-examined-row-limit is not yet…
Comment: What to tune in MySQL Server after installation
December 15, 2009 By 116 Comments
…, the mysql process always use 1 cpu core at 1 time and it used 100% of the cpu resource. I’am not sure why…_cache_size = 8 query_cache_size = 32M log_queries_not_using_indexes = On log_slow_queries=/var/lib/mysql/log-slow-queries.log innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries…
