Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
5
votes
1answer
5k 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 ...
16
votes
4answers
28k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
15
votes
3answers
31k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
11
votes
2answers
7k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
8
votes
1answer
3k 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?
5
votes
3answers
5k views

How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
6
votes
1answer
2k views

“Site Offline” MySQL server failing to start and stop

I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the "...
77
votes
3answers
176k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
22
votes
2answers
31k 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 ...
24
votes
2answers
51k 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 re-...
10
votes
3answers
58k views

Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist. I've read the other ...
8
votes
2answers
4k views

Insert-heavy InnoDB table won't use all my CPU

I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
4
votes
4answers
15k views

Optimized my.cnf for high-end and busy server

Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and ...
6
votes
2answers
2k 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. ...
2
votes
2answers
2k views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
27
votes
7answers
58k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
39
votes
4answers
15k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
12
votes
2answers
39k 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 ...
20
votes
3answers
8k 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 ...
14
votes
4answers
31k views

Optimal Number of MySQL InnoDB Buffer Pool Instances

Server Characteristics Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL) Number of CPU Cores: 6 I have data in the db amounting to about 2GB I have ...
7
votes
3answers
18k views

InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21 There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 (HY000):...
5
votes
3answers
26k 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 /var/log/mysqld....
7
votes
1answer
6k 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 ...
6
votes
3answers
8k 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 ...
2
votes
1answer
3k views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - 100%)...
6
votes
1answer
4k 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 ...
3
votes
2answers
5k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
2
votes
1answer
972 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
1
vote
1answer
675 views

The most efficient method to obtain the total entries into a mysql table

I have a table that has a very high usage (insert/update/select) - one of the operations is to obtain a count of the number of rows. So far I've been using the classic select count(pk) from table but ...
1
vote
1answer
2k 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 ...
35
votes
5answers
56k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
6
votes
4answers
40k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
10
votes
4answers
25k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
13
votes
1answer
12k views

MySQL InnoDB Deadlock For 2 simple insert queries

I have a deadlock for this two insert queries: insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, ...
8
votes
1answer
18k views

InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz MemTotal: 2058776 kB MemFree: 244436 kB We are running IP.Board from Invision ...
6
votes
2answers
19k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
9
votes
4answers
13k 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` ...
24
votes
3answers
17k 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 ...
9
votes
4answers
21k views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
8
votes
5answers
23k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
5
votes
1answer
18k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set the ...
5
votes
2answers
19k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, P....
6
votes
2answers
8k views

How much memory do I need for innodb buffer pool?

I read here that You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces) On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 ...
6
votes
3answers
17k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
5
votes
1answer
19k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
3
votes
2answers
16k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
12
votes
3answers
4k views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
5
votes
2answers
8k 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 ...
5
votes
2answers
2k 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 ...
5
votes
3answers
5k views

MySQL tables/databases into different disks

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 tables/...

1
2
3 4 5
9