…used in results set which clearly puts query as optimization candidate. It is not always possible to optimize queries…used to generate result set – for example any filter which can’t use indexes…
Comment: What to tune in MySQL Server after installation
October 8, 2012 By 118 Comments
…-time-zone=America/Caracas max_connections = 1000 wait_timeout = 60 thread_concurrency = 2 max_allowed_packet = 1M [Slow Querys] #log-slow-queries = /var/log/mysqlslowq.log #long_query_time = 5 #log-queries-not-using-indexes [Cache] query_cache_type = 1 query_cache_size = 64M query_cache_limit = 64M thread_cache…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…-name-resolve skip-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…-restart the server. I’ve checked the security aspect and not an attack, will have any suggestions or ideas of what…
Comment: Impact of logging on MySQL’s performance
February 1, 2011 By 8 Comments
…have the slowlog on, and to set long_query_time = 2. (Some systems use higher or lower values.) When …The rest of the time, I find it useful to proactively look for naughty queries and propose tuning / index / schema / design… you are talking only about the slowlog, not the binlog, nor the general log…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries…time we’re spending, and shortcut effort if it’s not useful, e.g. skipping analysis for a query…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 30898, query…logs and not flushed from buffer pool so such log sequences can’t be overwritten in log files. By monitoring log…pages – dirty pages which were not accessed long time, flush list – old …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…query date ranges most of the time, you should define the primary key as (day, ad). Don’t use…INFILE. Keep those big fat log files out of the database.Â…of primary key columns. And not only will every index be a little narrower…’re still not what you need unless you’re okay with long queries that …
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…time it is a lot easier to optimize your system when your replication runs normally – if you need to add/remove indexes…is not only needed in case you’re planning to use …long_query_time=0 and log_slave_slow_statements=1 for this method to work. Get the log file which will include all queries…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…LONG_SIZE | 10000 | 100 | | PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 | | PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | 100000 | 2223 | | INNODB_LOG…1 | 8 | | QUERY_CACHE_SIZE | 0 | 1048576 | | SLOW_QUERY_LOG_FILE…not enabled by default. innodb_old_blocks_time…based=on,use_index_extensions=on 1…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…query results get the highest priority. We are slowly working on the performance enhancement requests. It takes time…long will this take? Is there a facility we can run to show any table / index…indexes are not used for primary key lookups or not…
