InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

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 ...
4
votes
2answers
1k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
2
votes
1answer
397 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 ...
2
votes
1answer
2k views

Why can't I get a lock on an InnoDB table?

I have problem with lock table command: LOCK TABLE v3_cam_date WRITE; the command always running with state: "Waiting for table metadata lock". Try to find out which thread was hold the lock on ...
3
votes
1answer
8k 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
588 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
180 views

How to interpret explain sql?

SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance FROM `tablebusiness` AS TB ...
5
votes
2answers
4k views

Frequently slow queries while in the 'Update' state

I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ ...
3
votes
2answers
417 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 ...
6
votes
2answers
21k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
3
votes
1answer
311 views

Slow-running query on clustered MySQL environment

I've got a very simple query, directly equivalent to SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000; When I run this query on my non-clustered ...
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 ...
1
vote
0answers
1k views

Tables don't exist after MySQL restart

I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior: I have some tables with data in them I made ...
5
votes
3answers
6k views

Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

I need to design database to store information contained in millions of log files generated by devices on trial on network. I have designed 5 different tables to store log file information. But, as ...
1
vote
1answer
220 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 ...
2
votes
1answer
551 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 ...
3
votes
1answer
713 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 ...
3
votes
1answer
4k views

MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the slave. As for all the replication, ...
5
votes
2answers
6k views

Query runs a long time in some newer MySQL versions

I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
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?
4
votes
1answer
3k views

How do you read MySQL InnoDB Monitor output?

I am new to DB Administration. I am trying to figure out the cause of deadlock exceptions, but I am unable to make sense of the logs. The log says that "transaction 1 is rolled back". Why is this the ...
14
votes
3answers
3k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
6
votes
2answers
3k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
2
votes
1answer
6k 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 ...
2
votes
1answer
124 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
3
votes
2answers
2k views

Mysql load from infile stuck waiting on hard drive

I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
2answers
60 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 ...
1
vote
2answers
1k views

Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

I'm a bit irritated because of some warnings in mysql errorlog. Example 1: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement ...
4
votes
1answer
1k views

Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS

I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds. ...
2
votes
2answers
2k views

Deleting Entries from Table not reducing my DB file

Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file. When i Entered 512 Record in my users table its size get increased to 200kb from 96 ...
1
vote
2answers
1k views

How can I optimize this mySQL table that will need to hold 5+ million rows?

The table has about 12 rows. Indexes: idint(11), Unique, auto-increment, primary ATTACHMENTIDvarchar(14), Unique MLSNUMBERbigint(20) POSITIONint(10) FILETYPEvarchar(32) I'm doing an extended ...
1
vote
2answers
5k views

Innodb UPDATE performance

We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some ...
6
votes
1answer
4k views

Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
1
vote
2answers
618 views

MySQL: reducing ibdata file size for MyISAM tables

My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables: Is there any best way to reduce the size of ibdata in mysql.?, I have noticed ...
2
votes
2answers
3k views

Need suggestions for best way to archive MySQL (InnoDB) tables

Problem: I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active ...
2
votes
3answers
1k views

whether a big (but not used) ibdata1 slow anything down

Hi I have an ibdata1 that is around 10 gig. I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore. I recently ...
4
votes
1answer
1k views

InnoDB : Deadlock from one reader and one writer

This sort of has me miffed. One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that ...
1
vote
1answer
343 views

Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2. I was wondering if my PK ...
2
votes
2answers
2k views

How to sync two MySQL high load tables with 10M rows?

I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and ...
2
votes
3answers
5k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
13
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
3answers
2k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
4
votes
1answer
1k views

Which mysql storage engine to choose?

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
7
votes
1answer
4k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
24
votes
5answers
35k 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 ...
3
votes
1answer
238 views

Serialized objects vs multiple tables

What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information ...
0
votes
1answer
1k views

Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs. # ...
3
votes
2answers
818 views

MySQL insists on trying to use InnoDB even when told not to, please help

We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be ...
1
vote
2answers
161 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB ...