June 18, 2013

Post: 10+ Ways to Crash or Overload MySQL

November 13, 2007 By 20 Comments

…-8K per table though complex tables can require larger sizes, so this is mainly the problem for smaller servers. Table Cache Merge Tables – Table Cache is… the case with Merge tables for example – creating and accessing few merge tables with 1000 of subtables will likely cause your MySQL server to run…

Filed Under: Insight for DBAs Tagged With:

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

… dump and load a copy of that table into your working index_analysis schema. Merging stats from several servers In the case… two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Modeling MySQL Capacity by Measuring Resource Consumptions

March 8, 2011 By 1 Comment

… to provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S….88k 258.55k 1.46k # Merge passes 0 0 0 0 0 0 0 # Tmp tables 21.36k 0 2 0.01….88k 1.23k 1.46k # Merge passes 0 0 0 0 0 0 0 0 # Tmp tables 0 0 0 0 0…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… that are added to or removed from the table and which need to be merged back into or removed from the main… actually rebuild the table. -rw-rw—-. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_ADDED.ibd -rw-rw—-. 1 mysql mysql 98304 Feb… | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +—————————+———+ 10 rows in set (0.02 sec) mysql> optimize table dir_test_innodb; +———————-+———-+———-+———-+ | Table | Op | Msg…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

…table I am getting: mysql> check table test; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> check table test; +———–+——-+———-+———-+ | Table | … modes to block purging activity, insert buffer merge or recovery from transactional logs all together. …

Filed Under: Insight for DBAs Tagged With:

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

… referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently,…commanual socketblogpercona.commanual sort_buffer_sizeblogpercona.commanual Sort_merge_passesblogpercona.commanual Sort_rangeblogpercona.commanual Sort_rowsblogpercona.commanual…

Filed Under: Insight for DBAs, MySQL

Post: MySQL and PostgreSQL SpecJAppServer benchmark results

July 25, 2007 By 13 Comments

… MySQL sweet spots come from. MySQL performance gains usually come from MySQL unique features – non transactional MyISAM, Multi Value inserts, Query Cache, Merge Tables, Archive Tables… at connection creation and MySQL is very fast, while SubSelect optimization is very bad in MySQL for many cases until MySQL 5.2

Filed Under: Benchmarks Tagged With:

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb tables) physical file offset (for MyISAM tables) or… do not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be… disable index on the second column we get a full table scan: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: A workaround for the performance problems of TEMPTABLE views

May 19, 2010 By 7 Comments

…table such that a scan of all rows takes a second or two. mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE…then MySQL will let us use the MERGE algorithm. Notice that there are no warnings: mysql> create algorithm=merge …

Filed Under: Insight for Developers, MySQL Tagged With: , , ,

Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

February 23, 2012 By 3 Comments

…]# lvconvert –merge /dev/sb/mysql-data-201202230157 Merging of volume mysql-data-201202230157 started. mysql-data: Merged: 2.3% mysql-data: Merged: 0.2% mysql-data: Merged: 0.0% Merge of… logical volume to start the merging. So let’s see if our salaries table is restored. mysql> SHOW TABLES; +———————+ | Tables_in_employees | +———————+ … | salaries | … +———————+ 6 rows…

Filed Under: Insight for DBAs
« Older Posts
Newer Posts »