InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
418 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
3
votes
2answers
162 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
1answer
92 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 ...
1
vote
1answer
35 views

Cannot add a column to an InnoDB table in Mariadb

I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64). +-----------------+--------------+------+-----+-------------------+----------------+ | Field | ...
1
vote
1answer
32 views

MySQL Data Files Missing?

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there ...
2
votes
2answers
148 views

ib_logfile opened with O_SYNC when innodb_flush_method=O_DSYNC

mysql will open and flush data/log files as below when innodb_flush_method=O_DSYNC Open log Flush log Open datafile Flush data O_SYNC NONE NONE fsync () ...
1
vote
2answers
222 views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB sql dump that I need to import into MySQL. I haven't had to import such a huge sql dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
0
votes
1answer
125 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 ...
0
votes
1answer
24 views

Would we see a performance increase by moving one table to another physical drive?

We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space. We quite often have to query this table - sometimes as far back as a few years, so we can't ...
0
votes
1answer
32 views

Row size too large (> 8126)

I'm facing the following problem. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, ...
0
votes
1answer
265 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
2
votes
2answers
73 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
1
vote
1answer
192 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 ...
1
vote
1answer
274 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
0
votes
2answers
450 views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
2answers
1k views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
2
votes
1answer
4k views

MySQL requires FORCE INDEX on huge table and simple SELECTs

We have an application which stores articles from different sources in a MySQL table and allows users to retrieve those articles ordered by date. Articles are always filtered by source, so for client ...
0
votes
0answers
6 views

How to synchronize locally two DB table in Master server in MySQL Server 5.6.19

I want to synchronize the locally two DB tables in Master Server. Suppose that first i have already table Employee in a Master Server. The Table Employee have already 20 fields records. Then i have ...
70
votes
9answers
57k views
0
votes
2answers
763 views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
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 ...
0
votes
1answer
214 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
7
votes
2answers
443 views

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
0
votes
1answer
31 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
0answers
19 views

MySQL InnoDB Configuration with 60Gb of Memory

I'm running a large MySQL Database, in the range of 30Gb in size. The MySQL 5.5 server is running on a dedicated server with 60GB of RAM, with no other processes installed apart from monitoring tools, ...
1
vote
1answer
1k 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 ...
3
votes
2answers
2k views

MySQL information_schema doesn't update

I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true. After I run the query delete from abc.test, I want ...
1
vote
0answers
47 views

Is >58 hours to create an index normal? Should I cancel and try again?

I have an InnoDB table with ~43,964,557 rows containing 20.5 GiB of data (not counting existing indexes). mysql> show table status like 'backpack_items'; ...
0
votes
1answer
181 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
0answers
58 views

Why are tiny Innodb row updates causing so much disk IO?

This is a followup question to my earlier How can I determine which Innodb table is being written? Using the accepted answer from the above-referenced question I was able to determine which (two) of ...
10
votes
2answers
115 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
0
votes
1answer
28 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 ...
2
votes
1answer
44 views

How can I determine which Innodb table is being written?

I am running Mysql 5.5. I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The ...
3
votes
1answer
137 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
0
votes
0answers
18 views

Is a update-only-once-row table worth sharding?

I have a big MySQL 5.6 Inno DB table that contains user's request. A table row updates only once, shortly-after the initial insert! (The initial insert is to get the unique auto-incrementing ...
0
votes
1answer
58 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`), ...
1
vote
2answers
908 views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
45
votes
4answers
55k 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: ...
0
votes
2answers
232 views

Restore InnoDB to different machine

It was pointed to me from serverfault that the following question would be a better fit here so this is basically my original question from there. I currently have backups that were taken with ...
1
vote
2answers
1k views

MySql InnoDB tables are missing after restarting

I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at ...
2
votes
1answer
245 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` ...
1
vote
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
1
vote
1answer
90 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, ...
0
votes
1answer
24 views

Should I use additional id values as unique values that only applies in junction tables in MySQL?

I have created three tables called person, metadata_image and person_image. person table contains info about people. person_id person_name person_bio metadata_image table stores the images. image_id ...
0
votes
2answers
23 views

Flush/Clean MySql ib_logfile0 ib_logfile1

Is there a way to flush ib_logfile0 and ib_logfile1 without dumping the tables to a .sql file, deleting, then re-inserting? Somebody entered a couple plain text credit card numbers into the customer ...
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 ...
0
votes
1answer
28 views

Will canceling InnoDB compression that's already in progress corrupt data?

I have a 155GB table that is in the process of being compressed. ALTER TABLE $table_name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; This compression has been running for a long time and is impeding my ...
2
votes
3answers
2k views

Can't create table, but table doesn't exist

I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db: mysql> USE my_db; mysql> DROP TABLE my_user; mysql> ERROR 1051 (42S02): ...
0
votes
2answers
564 views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
2
votes
1answer
85 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...