What Bugs makes you to recomend upgrade most frequently ?

09:31 Unused indexes by single query » MySQL Performance Blog

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

PLAIN TEXT

SQL:

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IS
ON (s.TABLE_SCHEMA = IS.TABLE_SCHEMA AND
s.TABLE_NAME=IS.TABLE_NAME AND
s.INDEX_NAME=IS.INDEX_NAME)
WHERE IS.TABLE_SCHEMA IS NULL;

PLAIN TEXT

SQL:

+--------------+---------------------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME |
+--------------+---------------------------+-----------------+
| art100 | article100 | ext_key |
| art100 | article100 | site_id |
| art100 | article100 | hash |
| art100 | article100 | forum_id_2 |
| art100 | article100 | published |
| art100 | article100 | inserted |
| art100 | article100 | site_id_2 |
| art100 | author100 | PRIMARY |
| art100 | author100 | site_id |
...
+--------------+---------------------------+-----------------+
1150 rows IN SET (1 min 44.23 sec)

As you see query is not fast, mainly because information_schema.statistics is slow by itself, but in any case very helpful.

Entry posted by Vadim | No comment

Add to: | | | |

05:18 Google’s user_statistics V2 port and changes » MySQL Performance Blog

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)

New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user

FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

INFORMATION_SCHEMA tables - USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS
Access protection - USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information of tables that current user has access to (SELECT privileges required). This is important for hosting providers

Here is example of new tables:

PLAIN TEXT

SQL:

mysql> SELECT * FROM information_schema.user_statistics\G
*************************** 1. row ***************************
USER: root
TOTAL_CONNECTIONS: 2
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 4417
BUSY_TIME: 7
CPU_TIME: 5
BYTES_RECEIVED: 12163659
BYTES_SENT: 4479
BINLOG_BYTES_WRITTEN: 12164231
ROWS_FETCHED: 2
ROWS_UPDATED: 348382
TABLE_ROWS_READ: 0
SELECT_COMMANDS: 2
UPDATE_COMMANDS: 16
OTHER_COMMANDS: 26
COMMIT_TRANSACTIONS: 15
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 0
LOST_CONNECTIONS: 0
ACCESS_DENIED: 0
EMPTY_QUERIES: 0
1 row IN SET (0.00 sec)

PLAIN TEXT

SQL:

mysql> SELECT * FROM information_schema.client_STATISTICS\G
*************************** 1. row ***************************
CLIENT: 192.168.10.174
TOTAL_CONNECTIONS: 10
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 0
BUSY_TIME: 118
CPU_TIME: 118
BYTES_RECEIVED: 2174226
BYTES_SENT: 16222528
BINLOG_BYTES_WRITTEN: 0
ROWS_FETCHED: 51596
ROWS_UPDATED: 0
TABLE_ROWS_READ: 233
SELECT_COMMANDS: 5904
UPDATE_COMMANDS: 0
OTHER_COMMANDS: 30
COMMIT_TRANSACTIONS: 0
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 0
LOST_CONNECTIONS: 0
ACCESS_DENIED: 0
EMPTY_QUERIES: 506
*************************** 2. row ***************************
CLIENT: 192.168.10.161
TOTAL_CONNECTIONS: 41539
CONCURRENT_CONNECTIONS: 0
CONNECTED_TIME: 0
BUSY_TIME: 3369
CPU_TIME: 3369
BYTES_RECEIVED: 152609007
BYTES_SENT: 1489795778
BINLOG_BYTES_WRITTEN: 0
ROWS_FETCHED: 593836
ROWS_UPDATED: 0
TABLE_ROWS_READ: 549073
SELECT_COMMANDS: 214801
UPDATE_COMMANDS: 0
OTHER_COMMANDS: 124497
COMMIT_TRANSACTIONS: 0
ROLLBACK_TRANSACTIONS: 0
DENIED_CONNECTIONS: 0
LOST_CONNECTIONS: 0
ACCESS_DENIED: 0
EMPTY_QUERIES: 70748

and INDEX/TABLE statistics:

PLAIN TEXT

SQL:

mysql> SELECT * FROM information_schema.index_STATISTICS LIMIT 10;
+--------------+------------+-----------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+-----------------+-----------+
| art119 | img_out119 | from_message_id | 4160 |
| art117 | img_out117 | from_message_id | 3324 |
| art119 | article119 | forum_id_3 | 1855 |
| art84 | img_out84 | from_message_id | 8363 |
| art115 | article115 | forum_id_2 | 6 |
| art61 | img_out61 | from_message_id | 4475 |
| art90 | img_out90 | from_message_id | 42853 |
| art104 | forum104 | site_id | 9660 |
| art61 | forum61 | site_id | 17744 |
| art50 | img_out50 | from_message_id | 19306 |
+--------------+------------+-----------------+-----------+
10 rows IN SET (0.00 sec)
mysql> SELECT * FROM information_schema.table_STATISTICS LIMIT 10;
+--------------+-----------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+-----------------+-----------+--------------+------------------------+
| art86 | forum86 | 2186 | 2 | 6 |
| art104 | forum104 | 9660 | 6 | 18 |
| art57 | link_out57 | 8406371 | 3393 | 23751 |
| mysql | user | 15 | 0 | 0 |
| art53 | article53 | 7103377 | 4564 | 45640 |
| art116 | thread_stats116 | 0 | 8479 | 16958 |
| art50 | link_out50 | 11420660 | 2051 | 14357 |
| art60 | article60 | 5048218 | 824 | 8240 |
| art92 | author92 | 0 | 131 | 262 |
| art50 | img_out50 | 1772663 | 2305 | 6915 |
+--------------+-----------------+-----------+--------------+------------------------+
10 rows IN SET (0.01 sec)

If you would like to test it here is link to patch for 5.0.67.
http://www.mysqlperformanceblog.com/mysql/experimental/userstatv2.patch. When we consider it stable we will include it in our releases.

Btw working with patches I found very useful utils patchutils, which recommend you if you also need to separate one big patch to several small

Entry posted by Vadim | 5 comments

Add to: | | | |

04:09 What Bugs makes you to recomend upgrade most frequently ? » MySQL Performance Blog

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like "sometimes my slave clone script does not work" may hang on for years.

Entry posted by peter | No comment

Add to: | | | |

	九月 2008
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30