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

InnoDB Architecture Please keep in mind what goes into the InnoDB Buffer Pool 16KB Data Pages for Tables that have been accessed 16KB Index Pages for Indexes that have been accessed Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment) MySQL's idea of a JOIN Believe it or not, whether you have one big table ...


2

If you mean getting MySQL to engage multiple cores, you have come to the right place. In order to get MySQL use multiple core for InnoDB, you must use the new settings that came with the InnoDB Plugin starting with MySQL 5.1.38. Rather than reinventing the wheel, here are my earlier posts on this subject for InnoDB Mar 16, 2012 : Using multiple cores for ...


2

I don't know why you can't see the InnoDB plugin. I've never seen that happen. You can also use this method to check what version of InnoDB you have loaded: mysql> SHOW VARIABLES LIKE 'innodb_version'; If it returns a number, then you're using the InnoDB plugin. If it returns nothing, then you're using the builtin InnoDB (or no InnoDB at all). I ...


2

Configuration items You should adjust the innodb_buffer_pool_size parameter for better memory usage. This is where all the memory of large DB machines should go. For example, on a 32 GB RAM machine, this can go up to 24 GB. On bigger installations you should use innodb_file_per_table = 1, which is creating single files instead of one big blob. If you ...


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 ...


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 ...


1

Introduction to transactions and savepoints In your scenario "Creating a new user" the basic process could be the following: BEGIN; INSERT INTO `users` (`username`, `password`, ...) VALUES ("me", "XXX", ...); SET @userid := LAST_INSERT_ID(); INSERT INTO `profile_pictures` (`user_id`, `profile_picture_path`) VALUES (@userid, ...



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