Tag Info

New answers tagged

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


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


1

This is so funny. Yesterday, someone asked how to do the opposite and I answered that question : Migrate from innodb_file_per_table to off in MySQL Here the steps for what you want STEP #1 Set innodb_file_per_table to 0 in my.ini [mysqld] innodb_file_per_table = 1 STEP #2 Login in Command Prompt in Administrator mode and run C:\> net stop mysql ...



Top 50 recent answers are included