InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

4
votes
3answers
961 views

Should we need to use Barriers on a production database (MySQL/InnoDB)?

Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended? e.g mount -o barrier=1 /dev/sda /mntpnt Reference: ...
3
votes
1answer
3k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
5
votes
1answer
3k 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 ...
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 ...
0
votes
1answer
383 views

Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring [closed]

I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the ...
3
votes
1answer
185 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
971 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. # ...
1
vote
1answer
150 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 ...
3
votes
2answers
702 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 ...
3
votes
2answers
416 views

Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records: SELECT COUNT(id) shareLikeNo FROM ...
0
votes
1answer
137 views

Backup and trimming data idea

Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows. We would like to maintain ...
1
vote
1answer
194 views

What storage engine should I use for this MySQL table?

I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters. The primary key is the url itself varchar(255). I have set it to use InnoDB but ...
4
votes
1answer
4k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
1
vote
1answer
499 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
3
votes
1answer
824 views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
3
votes
1answer
736 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
1
vote
2answers
2k views

Partitioning a table will boost the performance?

I have a very large table approx 28GB and that is continually increasing. I am thinking about partitioning my table. The table is InnoDB and The File Per Table is enabled I have a field name ...
0
votes
2answers
999 views

How do I get spatial index feature if I mainly use innodb?

My current strategy is to create another table in myisam that contains those spacial information. Then if I want to find 20 closest business, I'll just use join. Is this a good idea?
1
vote
1answer
29 views

Can we use innodb to store latitutude and logitude and retrieve 20 closest points well?

I've heard that innodb Why innodb doesn't store geospatial data? store geospatial data but cannot index it. Not really sure what it means. Is it a good idea to ask 20 closest businesses if point ...
1
vote
2answers
147 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 ...
5
votes
2answers
5k views

What to set innodb_buffer_pool and why..?

I have 170GB of InnoDB index and Data. I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB. So what should be the optimal ...
2
votes
1answer
101 views

Are two indexes needed?

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter. Which indexes should I use? Should I have two ...
2
votes
1answer
686 views

In InnoDB, does a Transaction imply any implicit locking of a table?

The Isolation level is REPEATABLE_READ. The logic is as under: Transaction begins Read data from Table A If (Table A has Any Data) End Transaction and exit If Table A has No Data, Proceed further ...
6
votes
3answers
8k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
2
votes
3answers
253 views

What is a database with “.res” in its name?

I'm not that experienced with MySQL and I'm trying to fix some databases. In mysql, a "SHOW DATABASES" shows (among others) the main database "MainDB" and another database "MainDB.res". If I'm trying ...
4
votes
1answer
2k views

Can I safely kill OPTIMIZE TABLE on an InnoDB table?

MySQL's documentation for kill warns: Warning Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to ...
3
votes
2answers
138 views

MySQL 1264 warning from libmysql

I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader). I managed to connect and things are going almost fine. I use mysql_real_connect and ...
1
vote
0answers
843 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 ...
2
votes
1answer
552 views

Need advice for a 'tag' table design

My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design: 2 tables option : 1 table for the ...
2
votes
1answer
158 views

Log file for mysql

I would like to know where will my error logged for mysql server. As of now I just see one log that is in /var/log/mysqld.log. I saw some have .err files too am I missing something here? What would ...
1
vote
1answer
207 views

some InnoDB settings in My.cnf are messing up everything, why?

When I edit my.cnf in MySQL 5.0 in filemanager and add the following settings, I get logged out automatically from Kloxo and when I remove these line and restart server everything is ok then: ...
3
votes
3answers
8k views

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
2
votes
1answer
228 views

Changing to InnoDB increases iostat

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat. But how can it increase ...
6
votes
1answer
2k views

How large will a MySQL database be relative to the dump file?

I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
1
vote
1answer
430 views

Restore the data from Snapshot Backup when the data spans over multiple directories?

Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF. We have done some testing work on one of the slave say server A.and it takes a huge amount of ...
3
votes
1answer
455 views

MySQL - Installing InnoDB Plugin

I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61. I want to upgrade the master server to a stronger machine with the following: 2 processors - 6 cores each 48GB of RAM To ...
1
vote
1answer
305 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
3answers
340 views

MySQL - Master/Slave Replication with different server specs

I have a Master/Slave replication (using MySql 5.1 / InnoDB). Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) ...
3
votes
1answer
431 views

why is mysql still writing data two hours after all transactions have stopped

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data). Lately, I noticed something peculiar. After I ...
0
votes
1answer
101 views

InnoDB possible error

We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB ...
9
votes
3answers
2k 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 ...
1
vote
2answers
1k views

InnoDB missing from MySQL

I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL server. I recently upgraded it from the dotdeb repository, then installed mysql-server. There is no ...
0
votes
1answer
192 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
3
votes
2answers
776 views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
6
votes
2answers
9k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
2
votes
1answer
953 views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
1
vote
1answer
249 views

MySQL lost records in a master slave scheme

We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter ...
4
votes
1answer
3k views

What is the use of ibdata1 file in mysql?

I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions. ...
0
votes
2answers
2k views

Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery

We have a MySQL database that we have manage to grab the raw files from a server with a fatal hardware failure. However, this failure seems to have occurred when the DB server was running. As a ...
18
votes
3answers
34k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?