InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
8 views

Concurrency when simulating autoincrement with triggers on MySQL

I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns ...
1
vote
1answer
10 views

Acceptable to use Antelope and Barracuda simultaneously?

I've been doing some reading about the Antelope and Barracuda file formats for InnoDB. I recall at least one source mentioning that the two should not be used simultaneously. I can't think of a good ...
0
votes
1answer
12 views

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads. Will a static ...
1
vote
1answer
33 views

What is InnoDB Buffer Usage and how to clear it?

In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage" It is "95%" even if there is no query running. Why? It affects my performance in my new queries? ...
3
votes
0answers
32 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
1
vote
2answers
31 views

InnoDB row metadata size (minimum row size)

I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6). This is not exactly my case, but for simplicity, let's ...
0
votes
1answer
20 views

MySQL - Changing from system tablespace to file per table

The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table ...
3
votes
2answers
73 views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...
0
votes
1answer
35 views

What can I do about this MariaDB 10.0 error?

I have a serious error that crashes the mariadb service, and it won't start up again until I delete the database folder in the data directory of the database I was working on at the time. Here is the ...
0
votes
4answers
28 views

If all tables are myisam, should I use myisam engine instead of default innodb?

I have read all of the innodb/myisam threads and they are confusing. Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.? The software I am using creates ...
1
vote
4answers
47 views

with MySQL, are multiple single column indexes faster than one multi-column index? [closed]

I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?
0
votes
1answer
54 views

How to optimize a slow query?

I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This ...
0
votes
2answers
7 views

How does InnoDB access disk?

I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page ...
2
votes
2answers
36 views

Question About Large InnoDB Table with Deleted Rows

Our server has a DB that's over 600 GB - we are deleting older rows that are non-essential to contain the growth. We can't optimize the table due to possible downtown on the table but we are ...
1
vote
1answer
43 views

Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything. But, Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN ...
1
vote
0answers
10 views

How “relatively safe” is the data which was salvaged from corrupted MySQL InnoDB database?

I encountered the corruption like the following. 150208 14:27:52 InnoDB: Error: page 443375 log sequence number 8941 870477908 InnoDB: is in the future! Current system log sequence number 5968 ...
0
votes
1answer
55 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
0answers
21 views

InnoDB Deadlocks Involving Currency Transactions

We're dealing with InnoDB deadlocks that are affecting our virtual transaction system. LATEST DETECTED DEADLOCK 150210 20:51:03 *** (1) TRANSACTION: TRANSACTION 0 4240405649, ACTIVE 0 sec, process no ...
0
votes
0answers
23 views

DB Rolls Back And Loses Data

So we've come across an astounding issue here. At random moments, our DB is dropping the database and recreating the table with data from an earlier point in time. Unfortunately general query logs ...
0
votes
0answers
49 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0
votes
1answer
29 views

Change Storage Engine on Referenced Tables

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names ...
1
vote
2answers
37 views

How to Permanently solve MySQL error 2002 (HY000)

I have a problem on my cloud server with 2GB of RAM and 40GB Hard Disk that's hosting a Wordpress website that receives around 1000-2000 hits a day. My problem is that MySQL is always crashing and my ...
1
vote
2answers
59 views

Invision Power Board 3.4.6 - mysql locks during query with pending updates

about 2 weeks ago, we started experiencing random lockups and slowdowns on our forums. We've been running this site for around 6 years minimum with no issues. the site and db are on dedicated vms. The ...
20
votes
6answers
4k views

DELETE command not completing on 30,000,000 row table

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data made by an error by our programmer. Before I add any new, ...
0
votes
2answers
48 views

Is possible to backup database online using rsync?

Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the ...
1
vote
0answers
28 views

MySQL - Cannot add new Foreign Key

I'm using magento and it does some re-index for a product flat table structure. In this process a new table is created. The SQL for that table is something like: CREATE TABLE ...
0
votes
2answers
35 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
0
votes
0answers
29 views

54M InnoDB keeps shutting down unexpectedly [duplicate]

I have a mariadb server running several databases with very small load. From time to time, when I have 20+ users browsing one of the wordpress websites i have, the daemon just shuts down with the ...
2
votes
3answers
266 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 ...
0
votes
1answer
57 views

Dropped connections, corrupted schema … is MySQL sick?

Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are: Not being able to forward engineer said ...
1
vote
1answer
55 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
37 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 ...
0
votes
0answers
29 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
80 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
0answers
10 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 ...
7
votes
2answers
518 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
45 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
1answer
61 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'; ...
1
vote
0answers
34 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
74 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 ...
0
votes
1answer
49 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
58 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 ...
10
votes
2answers
131 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
24 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
94 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`), ...
0
votes
1answer
28 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
39 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
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 ...
1
vote
1answer
56 views

Dropping multiple KEYS of a table

Dropping multiple KEYS in a table is a preferred option or one by one is preferred? “DROP KEY A,DROP KEY B,DROP KEY C" For more 900 million records in the table. How does it work internally?
1
vote
2answers
66 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...