InnoDB is the MySQL ACID-compliant storage engine.
3
votes
1answer
1k views
Consistent logical backup of databases that use MyISAM and InnoDB engines
I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.
The mysqldump utility supports these two options:
--single-transaction - Creates a consistent ...
2
votes
1answer
6k views
How to Recover an InnoDB table whose files were moved around
So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
2
votes
1answer
347 views
MySQL proxy and SHOW PROCESSLIST query time
I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2.
Everything seems to function normally replication wise, no ...
10
votes
4answers
8k views
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
6
votes
3answers
299 views
Is there a need to keep refreshing the indexes from time-to-time?
This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.
I have created a fresh database and the tables, at present, have no records. I have created ...
16
votes
3answers
16k views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
3
votes
2answers
2k views
Internal reason for killing process taking up long time in mysql
I copied a big table's structure with (it is an InnoDB table btw)
CREATE TABLE tempTbl LIKE realTbl
Then I changed an index, and filled it up so I could run some test.
Filling it was done using:
...
3
votes
1answer
270 views
InnoDB has a transaction running, but “SHOW PROCESSLIST;” shows nothing
I have a replicated database setup, mostly Innodb databases/tables. The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show ...
2
votes
2answers
1k views
Corrupt InnoDB tables after every mysql restart. What's wrong?
Recently I changed a few settings in my mysql configuration. I did the following:
- stop mysql
- save the changed settings
- delete all ibdata and ib_logfiles
- start mysql
Normally this works fine ...
8
votes
5answers
8k views
MySQL server has gone away obstructing import of large dumps
I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a
ERROR 2006 (HY000) at line 7758: MySQL server ...
3
votes
2answers
290 views
Preordering the table by a specified column
I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering.
Is it possible to order the table ...
10
votes
2answers
3k views
Rows missing after online conversion from MyISAM to InnoDB
We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.
Now that the ...
4
votes
1answer
1k views
MySQL 'LOAD DATA INFILE' : Can't replace entry when foreign key exists
Here's a minimal case for my database scheme:
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT ...
1
vote
1answer
215 views
InnoDB Foreign key updating vs. manual in-application updating
I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys.
However, I'm working on a new area of the ...
4
votes
3answers
507 views
Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction"
Does Oracle DB work around this issue? After all, Oracle owns ...
5
votes
1answer
2k views
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction".
I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement ...
1
vote
1answer
625 views
MySQL InnoDB data file directly on a disk slice (fixed-size)?
It seems that ibdata1 is like a disk slice. In Solaris (UNIX) UFS filesystems (the common one before ZFS), one would separate their disk c0t0d0 into slices, say a 10GB drive would be cut into three ...
4
votes
3answers
6k views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
3
votes
2answers
2k views
Do INSERTs get auto-committed?
Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform ...
1
vote
1answer
229 views
Why did the MySQL Storage Engine Change on Restore in one scenario?
In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all ...
2
votes
2answers
1k views
Different MySQL Datafile Sizes After Restoration
I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86.
Some weeks ago, we upgraded the company's ...
4
votes
3answers
3k views
ALTER TABLE on a large table with an indexed column
I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
4
votes
1answer
407 views
MySQL Replication: Insert generating VERY huge temporary tables
our replication slave is generating ~300GB temporary files while running relatively simple inserts.
Our setup:
Master: 5.0.45-0.sles10.x86_64,
Slave: 5.0.85-win32, also tried with 5.1.57-win64, ...
2
votes
1answer
131 views
InnoDB foreign key and index duplication?
I'm have a part table like this:
part_id INT
manufacturer_id INT
supplier_id INT
part_number VARCHAR(50)
part_description VARCHAR(120)
with foreign ...
2
votes
2answers
2k views
Using MySQL triggers or transactions?
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
4
votes
1answer
477 views
What are your use-cases for HandlerSocket?
There's a bit of hype around: HandlerSocket
It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in.
My question is, ...
4
votes
2answers
2k views
Will these two queries result in a deadlock if executed in sequence?
This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or ...
6
votes
2answers
2k views
Trouble deciphering a deadlock in an innodb status log
We are accessing MySQL from the Microsoft ADO.NET connector.
Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
6
votes
2answers
7k views
MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
5
votes
1answer
342 views
Dealing with temp tables when I have not control over db variables
I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically.
I am wondering if there ...
3
votes
2answers
524 views
Should I move to InnoDB during a planned migration?
I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
10
votes
3answers
9k views
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
3
votes
3answers
512 views
Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?
Is it possible to create an InnoDB table without a Primary Key?
Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
4
votes
4answers
779 views
Is tuning the innodb_buffer_pool_size important on Solaris ZFS?
We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
8
votes
1answer
3k views
Clarification on MySQL innodb_flush_method variable
Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
6
votes
4answers
3k views
How to improve InnoDB DELETE performance?
So I have this audit table (tracks actions on any table in my database):
CREATE TABLE `track_table` (
`id` int(16) unsigned NOT NULL,
`userID` smallint(16) unsigned NOT NULL,
`tableName` ...
5
votes
1answer
1k views
COUNT(*) on InnoDB whenever phpMyAdmin loads
It's a known issue that innodb is slow in
SELECT count(*) FROM Table
Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb ...
22
votes
3answers
22k views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
1
vote
1answer
425 views
Concurrent INSERTs in XtraDB (InnoDB)
I'm currently unable to insert rows concurrently in an XtraDB table on Percona Server 5.1.54. I had the same problem with a similar version of MySQL. My table definition is as follows.
CREATE TABLE ...
6
votes
2answers
1k views
What's a good way to add a PRIMARY KEY to a large InnoDB table?
I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows.
I want to add a primary ...
7
votes
1answer
1k views
Unexplained InnoDB timeouts
I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example:
//# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE photos ...
7
votes
1answer
4k views
InnoDB create table error: “Row size too large”
We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
2
votes
2answers
264 views
Will this Database design logic work and what should be used?
A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com
What he essentially wants to do is ...
15
votes
4answers
724 views
How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?
is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
9
votes
2answers
2k views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
17
votes
5answers
12k views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
11
votes
1answer
11k views
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
43
votes
7answers
23k views
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
50
votes
8answers
23k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
