…queries in the slow query log generated by our mysql build running with long_query_time=0, get …query SELECT whatstheweatherlike(5); Not overly useful for query optimization, is it? Well, I can still aggregate the slow query…stats, for example if it was slow because of locks, in the logs you may not…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… the same as for its predecessor – to get more information about query execution logged into slow log, however the new version is loaded… # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from…: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT COUNT(1) FROM t2; Important note: Although the patch was…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… > methods and get debugging and profiling tools…query(“Select email from user where name=%s”,$name) – > wrapper will detect query… do internal full table > lock if auto_…queries, especially > if you see them in slow query log. If you have trouble understanding > EXPLAIN or optimizing your queries…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… overload methods and get debugging and profiling …query(“Select email from user where name=%s”,$name) – wrapper will detect query…do internal full table lock if auto_increment …queries, especially if you see them in slow query log. If you have trouble understanding EXPLAIN or optimizing your queries…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…slow down transactions. You may have already guessed, but Galera does not use pessimistic locking cluster-wide, it uses optimistic locking…Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from …to get lock; try restarting transaction node1 mysql> select * from autoinc; +—+——-+ | i | j …
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…userstat_running=1 and run following query: mysql> select * from information_schema.user_…. 2) Use full slow query log and mk-query-digest. This method is … 1us 92s 669us 260us 120ms 93us # Lock time 28s 0 166ms 23us 49us … through replication they will surely get your replication capacity down for …
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… logs, slow query log or general log. flush, flush_time Flushing (closing) of tables after query is … as Query Cache also could use this feature. low_priority_updates If enabled selects will get higher …selects to the table until it is executed. It works for storage engines with table locks or if LOCK…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
…query_time=5 skip-bdb skip-innodb skip-locking query_cache_limit=1M query_cache_size=32M query…slow_queries=/var/log/mysql-slow…SELECTs from this table would take about 7-8 seconds each on a very simple query such as this: SELECT…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
…queries. The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects… size is not large enough snapshot will get invalidated and backup will be aborted. Sometimes…Copy data to backup. Normally you can skip slow query logs and error log while taking backup….
Comment: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
…site regularly and getting suggestions from …slow query log was filled with thousands of unsolvable mysteries every day and the slow query…queries a day are slower than that (usually because i dont have a failover server during backups which are causing locks that slow… do have 0 Select_full_join and …
