… share my experience in this post. Auditing user activity in MySQL has traditionally been challenging. Most data can be obtained from… someone more knowledgeable in MySQL internals can enlighten me here. – audit_record_objs : List of database objects (tables, according to the docs… the lines I had to add to my config file for the plugin to work (plus one commented line for switching between…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… unnoticed by most (not all) DBAs. Duplicate Index Detection I commented about this on my last webinar, but did not have…: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD…): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…columns in a MySQL table. So what’s the maximum number of columns in a MySQL table? Well.. I tried…_N_FIELDS – DATA_N_SYS_COLS * 2) Which adds up to (1024-1)-3*2=1017 …COMMENT ‘”.cname($_,254).”‘,\n”; } chop $sql; chop $sql; $sql.=”);”; print $sql; So the maximum number of columns for a table in MySQL…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size…’ll look at, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and… format all my analysis for sending, add in things I may have noticed along the way (comments on backups, for example) read…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… stored procedure would simply preform select to retrieve data from table. Avoid comment (and space) in the start of the query – Query… from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL has… application – You can’t change how it works with MySQL to add caching but you can enable query cache so it works…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… need to store hierarchical data, such as threaded comments, personnel org charts, or nested bill-of-materials…. We want to add new node D. A -> B -> C …add… D The Closure Table already has paths A-… from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax,…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…mysql Welcome to the MySQL monitor. Commands end with ; or \g. [...] mysql> SHOW ENGINES; +——————–+———+—————————————————————————-+————–+——+————+ | Engine | Support | Comment…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…())); and added key on column C: alter table sbtest add key c(c); The box I’m using … used 10mil row table which would look as following in terms of data and index size: mysql> show table status like… NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) So …
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…mysql> select * from queries where query_id = 7675136724153707161\G *************************** 1. row *************************** query_id: 7675136724153707161 fingerprint: select * from wp_comments where comment_post_id = ? and comment…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… software was not available for MySQL. Change Data Capture can be used to: Monitor a database table, or tables for changes. Improve ETL… appropriate changes. The file is well commented. The example settings file should work for most MySQL installations which allow connections as root… create a table, insert some rows, add a change log, delete rows and then insert some more of them: mysql> create table > test.demo…
