InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

9
votes
3answers
14k 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 ...
4
votes
1answer
343 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
3
votes
4answers
6k views

How to add column to big table in MySQL

I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried: Alter table in screen with table ...
2
votes
3answers
1k views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
2
votes
1answer
377 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
0
votes
2answers
32 views

What storage engine do I need - Mysql?

I have designed a database and came to the point of actually deciding which storage engine is better for my case. I estimate that during the first few years I would have around 100,000 - 500,000 ...
3
votes
2answers
1k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
5
votes
3answers
1k views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
3
votes
1answer
3k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
1
vote
1answer
186 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
1
vote
0answers
23 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
-1
votes
0answers
28 views

Proper database to use for my project [closed]

I have a question in MariaDB: What is the difference between Spider and InnoDB and which is faster? Actually I am going to make an application with hell amount of data.. Hence I am getting puzzled ...
0
votes
1answer
127 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz ...
1
vote
1answer
164 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...
2
votes
3answers
506 views

UPDATE waits for lock too long after SELECT in MySQL (Innodb)

I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen ...
4
votes
3answers
120 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
1answer
36 views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

I've got the same error, although I'm running galera cluster with 3 nodes. I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after ...
9
votes
5answers
11k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
0
votes
0answers
9 views

Percona: Innobackupex failing when taking incremental backup

I have been trying to take incremental backup as below and its getting error all the time. Innobackupex 2.1.8 Linux x86_64 Mysql 5.5.45 Step1: innobackupex -uxxx --stream=xbstream datadir > ...
1
vote
1answer
40 views

How can i write a stored procedure which will perform a calculation and store the result in a table?

I have two tables: CREATE TABLE `siteArea` ( `length` decimal(6,2) NOT NULL, `breadth` decimal(6,2) NOT NULL, `Area` decimal(6,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ...
0
votes
1answer
215 views

MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

I have this mysql table: CREATE TABLE `codes` ( `code` bigint(11) unsigned NOT NULL, `allocation` int(11) NOT NULL DEFAULT '0', `used` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`code`), ...
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 ...
2
votes
2answers
2k views

InnoDB tablespace critical

Could the error shown on the screenshot be the reason why my site is very slow? If so, I really need help to fix it since I am far from being an engineer!
0
votes
1answer
112 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in mysql although I feel the schema should be optimized for it since it's based on the wordpress database model. I've got 4 tables... ...
3
votes
3answers
88 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
2
votes
1answer
187 views

Mysqli prepare error: Unknown storage engine 'InnoDB'

I'm getting this error on my XenForo installation. Server logs show out of memory because of too many php processes opened up which cause mysqld to shutdown. The WordPress installation associated with ...
1
vote
3answers
63 views

Convert MyISAM to InnoDB

What are the considerations needed when converting myisam to innodb. I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to ...
0
votes
0answers
38 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
3
votes
0answers
75 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
0
votes
1answer
116 views

Doubts about Converting MyISAM to InnoDB (master/slave and more)

I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process. Since I have a Master/Slave structure, do I need to manage this and change ...
0
votes
1answer
167 views

innodb_buffer_pool & innodb_buffer_pool_instances

I have a database dedicated server with one quad-core cpu and 48GB Memory. Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system. At First, to ...
2
votes
2answers
206 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
34 views

Unable to load mysql dump file

I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied ...
2
votes
0answers
27 views

FullText search using only some of columns in index

I have a full text index on (name,category,description) as shown below. mysql> show create table phppos_items; ...
1
vote
1answer
2k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
1
vote
1answer
250 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
0
votes
2answers
125 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
2
votes
1answer
25 views

SQLyog and MariaDB engine

I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am ...
0
votes
1answer
226 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
1
vote
1answer
71 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
1
vote
2answers
72 views

tmp_table_size with mostly InnoDB tables

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two ...
1
vote
1answer
164 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
2
votes
1answer
74 views

cannot delete db table

I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory. Then I used a ...
0
votes
1answer
220 views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
0
votes
2answers
2k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
2
votes
1answer
22 views

Too many TEXT fields leads to record too big

I have a table with multiple 9 TEXT columns, each containing JSON data. I usually UPDATE all the columns at once. I can't seem to add a 10th TEXT column. The table is InnoDB. What can I do?
4
votes
2answers
179 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
2answers
986 views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE ...
0
votes
2answers
31 views

MySQL InnoDB data restore using 3 files

I am able to restore MySQL table data along with structure with indexes (InnoDB Engine) using 3 files. MyTable.frm, MyTable.ibd and ibdata1. Can any body confirm, will these 3 files suffice to ...
0
votes
1answer
173 views

Lowering MySQL Memory Usage without Restart

We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...