… in the pages. So, table_id and index_id of the dropped table gets overwritten. mysql> CREATE TABLE actor ( -> actor_id SMALLINT UNSIGNED… work and thus slow There is no way to differentiate tables based on values only. For example, if you dropped table actor… # ./s_indexes /var/lib/mysql/ibdata1 15 0-18 0-19 # Apparently 0-18 is PRIMARY index and 0-19 is index idx_actor…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…’ ‘Com_do’, ’0′ ‘Com_drop_db’, ’0′ ‘Com_drop_function’, ’0′ ‘Com_drop_index‘, ’0′ ‘Com_drop_table’, ’0′ ‘Com_drop_user’, ’0′ ‘Com_execute_sql…=/tmp/mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql/slow…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible to temporarily drop the corresponding index…index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug #49120 Fast Index…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other…that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL 5.5 and MySQL 5.6….buffer_size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit is …
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…=mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql… OLTP on small table drops more than 150 times when heavy mysqldump is running concurrently. mysqldump itself also slows down about 2x… buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema, t.name, i.name) as index_name, sum(data…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query…table straight away takes over 3 hours, while dropping indexes besides primary key, optimizing table and …advantage of it and will use old slow method of building indexes…
Comment: An argument for not using mysqldump
November 9, 2010 By 26 Comments
…: “In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… Percona Software Repositories). Based on MySQL 5.1.66, including all… for the tablespace have been dropped, as that will already clean them….#1039536 (Alexey Kopytov). In cases where indexes with AUTO_INCREMENT columns where correctly… stored procedure logged to the slow query log. Bug fixed #719386 (…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
…learn when working with large data things in MySQL is patience and careful planning. Both of …old row versions to deal with which can slow down some queries considerably. You need to …” indexes by sort builds primary key and unique indexes using keycache) If it does not performance may drop …
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
…MySQL 5.1.24. If the following collations were used: utf8_general_ci ucs2_general_ci and any of the indexes… / #51196 and #61790 in MySQL (Alexey Kopytov). Uninitialized values in the Slow Query Log patch. Bug …: #803865 (Laurynas Biveinis). Using the innodb_lazy_drop_table option led to an assertion …
