One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… critical activities are overloading the system causing user experience to degrade. The first thing you need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id | User | Host | db | Command | Time …and you’re not actively changing users, stored procedures etc…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… enough ACTIVE transactions which have some locks, like in this case: MySQL thread id 2059, query id 1014425 10.10.10.10 user Trx read view will not see trx with id >= 2 1418401228, sees < 2 1418374947 ---TRANSACTION 2 1418401057, ACTIVE 295…), undo log entries 1 MySQL thread id 1720, query id 1011283 10.10.10.10 user —TRANSACTION 2 1418397881, ACTIVE 317 sec, process…
Post: Three ways to know when a MySQL slave is about to start lagging
October 8, 2008 By 8 Comments
…you a USER_STATISTICS table. mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE USER=’#mysql_system#’\G *************************** 1. row *************************** USER: #mysql_system# TOTAL… of the time the slave thread was actively processing statements. If the slave threads are …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… might be: is the disk saturated? Is the machine swapping actively? (This is more important than whether it’s using swap…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult 28852… things I’ll look at, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant…
Post: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
… be in an acceptable range. It opens the possibility for active master – active master setups. You can write on both masters and…, I am pretty excited that this solution is available for MySQL users as Free / Open Source Software. “Where is the catch?”, you… three node cluster is much more complex than an average MySQL master-slave setup. Potential performance penalty: Although to have performance…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G *************************** 1…-trx-commit=1 innodb-flush-method=O_DIRECT innodb-ibuf-active-contract=1 innodb-import-table-from-xtrabackup=1 innodb-io…-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql wait-timeout=86400 To be continued You can now set…
Post: MySQL Life Cycle. Your Feedback is needed.
November 18, 2011 By 6 Comments
… web. Since that time I matured and so did MySQL ecosystem. MySQL is powering a lot more demanding and business critical applications… enough. Many users select rather traditional/prudent approach these days and will only start considering upgrade to next major MySQL version 6… applications which have not been actively developed recently are also more likely to run on old MySQL release which is understandable as…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…few cache implementations which I’d call an Active Cache meaning it takes care of the misses…to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL Query Cache so … where this query comes from or what application user triggered such query. Query Cache matching can …
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… useful. With simple MySQL SHOW command it gives you the basic statistics on the database activity broken down by users, tables or indexes… users causing the most traffic to help you with some strategic decisions. Sample outputs: mysql> SHOW USER_STATISTICS LIKE ‘hosting2′\G *************************** 1. row *************************** User…
