Tag Info

New answers tagged

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


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


0

What you are asking is not really an automatic feature in MySQL 5.6, I could be wrong. Notwithstanding, you can set it up manually. Given the following information Your datadir is /var/lib/mysql You data is in the Database mydb You have this InnoDB table Table Description CREATE TABLE mytable ( if int not null autop_increment, ... primary key ...


0

Yes, you can separate tables into individual files and spread those files across different disks. One example of documentation in MySQL (in this case for 5.7) is: https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html A partial quote says: "This mode is controlled by the innodb_file_per_table configuration option, and is the default in ...


0

One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder. First, take a look at the InnoDB Architecture Note the system tablespace (which was know is named ibdata1 in the OS). In MySQL 5.0, the default value for innodb_file_per_table is 0 or ...


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


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


0

I had discussed this in earlier posts Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it? Sep 26, 2013 : query cache hit value is not changing in my database InnoDB internals has a very hands-on approach to the query cache since it micromanages query cache entry invalidation. In the MySQL 4.x days, the query cache was ...


-1

Alternatively you may consider setting up replications from the main server to your dev machines. It would require to have a direct connection from your master to the slaves (the dev machines), but it doesn't need to be permanent. If you connect it once a month then the master will replicate only the changes, which should still be much faster. I doubt any ...


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


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


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

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


0

You need to mysqldump all the data (InnoDB and MyISAM) to a local text file and follow your steps. I have discussed this topic before Dec 06, 2012 : InnoDB insertion faster Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files Oct 29, 2010 : My Original Post in StackOverflow You may be concerned about the ...


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


0

I'm not 100% sure I understand your question but anyway. Generally there's no need to wrap SELECT statements in transactions because they are read only, although you can specify the isolation level you want the SELECT to use (READ UNCOMITTED for instance). See here for a question about locks caused by SELECT statements in MySQL and how to change the type of ...


0

I try to avoid mixing MyISAM and InnoDB tables in the same database, but this is for sanity rather than practical reasons. However, I find it useful to have a database with MyISAM tables for fulltext search so I can run that on sites. Keeping it in a separate database with a foreign key for each entry makes it easy for any other developers working on the DB ...


0

This situation sounds a lot like a post I answered two years ago (InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)) First, here is the InnoDB Architecture In that post, I basically point out that the metadata of the table is still present inside the system tablespace file ibdata1. When you ran SHOW TABLES;, ...


0

Yes, you can tray to repair your innoDB table First you can backup you tables. 1.- Stop you MySQL service 2.- backup all the data from "C:\xampp\mysql\data" to another folder, example: "C:\xampp\mysql\databackup" 3.- Add or change the value on you my.ini innodb_force_recovery option. [mysqld] innodb_force_recovery = 4 4.- Start your mysql service. ...


-1

You can try creating a new table according to old one's structure. Not sure if this will realy help as you say that your original table is corrupted: CREATE TABLE `new_table` LIKE `MY_TABLE`; If new table is ok, you can drop old one: DROP TABLE `MY_TABLE`; Otherwise you will have to drop the table (with the command above) and recreate it manually or ...


0

BUFFER POOL INSTANCES The rule of thumb I usually use is based on a special program in in the Linux environment numactl --hardware When I run this, I get the following output sh-4.1# numactl --hardware available: 4 nodes (0-3) node 0 cpus: 0 1 2 3 node 0 size: 49151 MB node 0 free: 241 MB node 1 cpus: 4 5 6 7 node 1 size: 32768 MB node 1 free: 39 MB ...


0

You should use combination of innodb_buffer_pool_size in accourdance with innodb_buffer_pool_instances You should set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you ...


0

Two potential workarounds: Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore). Manually hex edit the .ibd file, changing the table id. See Recovering an InnoDB table from only an .ibd file for the ...


0

I found that MySQL has string extraction functions like ExtractValue and SUBSTRING_INDEX which work inside WHERE and ORDER BY clauses. So I replaced the cells tables with a rows table which contains a tab delimited string, and I use SUBSTRING_INDEX and CAST to get SQL access to the individual cells.


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


0

First all take a look at the InnoDB Architecture InnoDB Your status for the Buffer Pool says Buffer pool size 1310719 That's your Buffer Size in Pages. Each page is 16K. That turns out 20G - 16K. Please note the the following: You pushed data into the InnoDB Buffer Pool. What changed ? Buffer pool size 1310719 Free buffers 271419 (It was ...


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


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


0

No, MySQL cannot do this alone, but you can bind unix processes to specific cores in general, see this question on unix.stackexchange and the linux manual concerning taskset, and this can also be applied to mysql processes of course. But actually I would not try this first. Processes under linux are more or less good distributed. Sticking processes to ...



Top 50 recent answers are included