InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
2answers
40 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
33 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
25 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
42 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
4 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
34 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
38 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
42 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
28 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
35 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
54 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
47 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
26 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
28 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
259 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
54 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
52 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
77 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
9 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
507 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
40 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
59 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
73 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
44 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
52 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
130 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
80 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
37 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
55 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
63 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 ...
2
votes
1answer
40 views

View last several innodb deadlocks

I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock ...
1
vote
2answers
56 views

OOM in mysql-percona

Every 4th day I run in to OOM problem. [Sat Dec 27 12:20:21 2014] Out of memory: Kill process 3662 (mysqld) score 990 or sacrifice child [Sat Dec 27 12:20:21 2014] Killed process 3662 (mysqld) ...
0
votes
2answers
35 views

sync 2 tables in a single database in mysql with no duplicacy [closed]

I have 2 tables in a single database of MySQL. The structures are like this: mysql> desc main_tbl; +-----------------+--------------+------+-----+-------------------+----------------+ | Field ...
2
votes
1answer
74 views

SQL aggregate function performance in a table with many records that is well-indexed

My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information. There are three tables: ...
4
votes
2answers
90 views

Full text search results in a large amount of time spent in 'FULLTEXT initialization'

I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like: CREATE TABLE `socomments` ( `Id` int(11) NOT NULL, `PostId` int(11) ...
2
votes
1answer
31 views

Only InnoDB databases locking up

I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation. At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up. ...
0
votes
1answer
60 views

what's a reliable way to determine the ideal innodb_thread_concurrency setting?

on an article from 2006 , here they suggest such formula. 2*(number of cpus + Number of Disks) has anyone tried their settings according to this formula ? or do you have any other formula ? our ...