InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

2
votes
2answers
170 views

MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

I want to configure a MySQL server for the following scenario. A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a ...
5
votes
3answers
8k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
2
votes
1answer
2k views

MariaDB and XtraDB/InnoDB plugins

In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the ...
3
votes
1answer
505 views

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables. While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
26
votes
1answer
32k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
16
votes
4answers
8k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
3
votes
3answers
12k views

ERROR Cannot find or open table?

MySQL version: 5.5.24 Due to the following problem: mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist ...
11
votes
2answers
26k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
5
votes
2answers
1k views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
4
votes
1answer
4k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
2
votes
2answers
3k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
2
votes
1answer
2k views

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all ...
1
vote
3answers
480 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
3
votes
1answer
967 views

InnoDB “corruption”

I use: 5.1.63-0ubuntu0.11.10.1 Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.63 The system is continuously displaying the following error during reading data from a MySQL database: ...
4
votes
1answer
375 views

How badly does innodb fragment in the face of somewhat out-of-order insertions?

Let's say there's a composite primary key on (field1, field2) Also, let be X a theoretical set of n numbers Inserts look like this: INSERT INTO table(field1, field2) VALUES([a number picked at ...
1
vote
2answers
188 views

What MySQL storage engine should I choose?

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows. What engine should I choose for my database, InnoDB or MyISAM? There will only be about 1,000 new records ...
2
votes
1answer
1k views

ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

I get an error when I try to create a table: ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121) CREATE TABLE IF NOT EXISTS `cache` ( `cid` varchar(255) NOT NULL ...
11
votes
3answers
1k views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
7
votes
2answers
2k views

MySQL : perform a big data migration between tables

I want to migrate data between 2 InnoDB tables. Currently I'm running this query: INSERT INTO table_a SELECT * FROM table_b; If the dataset grows, what's the best way to avoid CPU overload? ...
4
votes
2answers
9k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
5
votes
2answers
16k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
2
votes
2answers
3k views

MySQL 5.1.63 - using tuning-primer - Table Cache question

tuning_primer says the following: TABLE CACHE Current table_open_cache = 812 tables Current table_definition_cache = 256 tables You have a total of 498 tables You have 812 open tables. Current ...
13
votes
2answers
4k views

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
1
vote
1answer
1k views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...
1
vote
1answer
109 views

Select ``,* from table?

I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits. However, we use VARCHAR(50) ...
4
votes
2answers
1k views

Could running ANALYZE on all databases on large InnoDB tables show some performance increase?

So we have an old database that was fragmented until we ran OPTIMIZE on all tables. The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity. There are so many ...
3
votes
1answer
9k views

innodb_force_recovery when InnoDB corruption

When I start mysqld (in /etc/init.d), it failed with InnoDB: corruption in the InnoDB tablespace. What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but ...
1
vote
2answers
617 views

Simple queries taking ages to run after changing from MyISAM to InnoDB

I have a quite large (300GB) table on MyISAM, which I've "converted" to InnoDB, and performance for a specific query has dropped through the floor. When I say "converted", I can't afford to take the ...
1
vote
1answer
861 views

Removing/overwriting sensitive data located in already deleted records in ibdata files

As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file. I know this is by-design, and that I could switch the ...
3
votes
2answers
457 views

MySQL InnoDB Index in swap

I can't find any info regarding my question. This is more theoretical question. For example, i have table keywords. CREATE TABLE IF NOT EXISTS `keywords` ( `kid` int(11) unsigned NOT NULL ...
4
votes
1answer
8k views

How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine. When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work. I manually ran OPTIMIZE TABLE ...
4
votes
1answer
3k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
2
votes
1answer
413 views

MySQL table locks solution -> InnoDb / Partitions

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows). It's mainly caused due many heavy inserts. My current thoughts to optimize: Changing to ...
0
votes
1answer
2k views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...
0
votes
1answer
2k views

MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead. What are the drawbacks of doing this through ...
1
vote
1answer
227 views

Data storage in innodb

I am new to mysql administration and I came to know about the data storage in myisam is through .frm, .myd and .myi. I could not find the data storage mechanism in InnoDB rather than its storage in ...
3
votes
1answer
7k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
1
vote
1answer
161 views

Optimizing table and choosing storage engine

Having the follow MyISAM table: +----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
1answer
773 views

Fastest way to copy data from MyISAM to InnoDB

I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes. However, every approach I try ...
7
votes
1answer
5k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
4
votes
3answers
4k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
3
votes
1answer
2k views

Optimizing MySQL for Read-Only?

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
6
votes
1answer
1k views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
1
vote
2answers
2k views

Similar function NOWAIT in MySQL

Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
3
votes
1answer
4k views

error 1118: row size too large. utf8 innodb

I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question. Using MySQL 5.6 - windows ...
3
votes
1answer
998 views

How do you extend the tablespace when using innodb_file_per_table?

With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc. With the option on, how do you control growth of the files? Do ...
0
votes
1answer
748 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
2
votes
1answer
7k views

Enabling InnoDB on an existing MySQL production server

We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases. Can I just enable/configure InnoDB in /etc/my.cnf and restart ...
3
votes
1answer
3k views

Slow insert performance in mysql innodb table for solr indexing

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow. ...
3
votes
2answers
61 views

Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...