Tag Info

Hot answers tagged

2

If it's showing in your processlist then it's still running. In the future, for situations like this we're you're pruning 90%+ of the table consider this approach: create table new_table like old_table; insert into new_table (select * from old_table where <what you want to keep>) rename table old_table to old_table_drop_prep, new_table to old_table; ...


2

The table simply does not exist. How can you verify this ? Goto the operating system and run the following cd /var/lib/mysql/crs ls -l CRS_PAIR.frm If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following InnoDB Architecture Take a closer look at ibdata1 ( ...


2

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why? MyISAM Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is ...


2

I see one of the comments in the question mentions caching indexes please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs It just so happens I have post from Feb 04, 2012 that lets you make those ...


2

Does it increase memory ??? Absolutely !!! I wrote a post about INFORMATION_SCHEMA (Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?) In that post I explained how metadata for MySQL tables are stored in temporary tables using the MEMORY storage engine. For example, when you create a table mydb.mytable, look at what gets populated into these ...


1

Analysis Since we are talking about InnoDB, let's zero in on the gen_clust_index. It's a special index that keeps the PRIMARY KEY and associated row data accessible from the same InnoDB pages. According to MySQL Documentation on the gen_clust_index If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. If you do not ...


1

With large datasets, where data does not fit in RAM, you will warm your buffer pool -- and beyond. You will thrash the buffer pool. Your query does a full table scan. Assuming id is the PRIMARY KEY, you will be warming up the entire index tree (though not necessarily rows nor secondary indexes). But then you'll do the same for all your tables, and ...


1

You can take a backup from the live MySQL with Xtrabackup. Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions: Install the key: # apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A Add the source file: # cat /etc/apt/sources.list.d/percona.list deb http://repo.percona.com/apt precise ...


1

InnoDB Architecture Percona created this picture a long time ago. As you can see, ibdata1 contains many classes of data structures. There are as follows: Table Data Pages (if innodb_file_per_table disabled) Table Index Pages (if innodb_file_per_table disabled) Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables) Double Write Buffer ...


1

In general when running queries (SQL Statements) against a database you want all of the statements to successfully completed and if they do not revert data to what is was before you ran the sql statement. The overall concept you are looking for is ACID In order to provide this functionality, Databases generally support the concept of a transaction. When ...


1

We hit the same problem on deploying 5.6 as part of our standards we always install from .rpm and relocate the data dir after the first set up . 5.5 and lower we would change our settings in the /etc/my.cnf stop mysql and then tar up the database directories in the default data dir and untar to our new one . As we change all our innodb settings we then got ...



Only top voted, non community-wiki answers of a minimum length are eligible