InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
2answers
514 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
4answers
59 views

SQL retrieve last elements of table using group by like behaviour

I am trying without success to retrieve the last version of all articles in a table. The table structure: CREATE TABLE `journalarticle` ( `uuid_` varchar(75) DEFAULT NULL, `articleId` ...
0
votes
2answers
27 views

Performance impact of adding a foreign key to a 1M rows table

I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q). I'd like to make a connection between T and Q by adding a ...
2
votes
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
2answers
107 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 ...
0
votes
0answers
5 views

Dropping a corrupt table in innodb_force_recovery 6

As often happens in mysterious shutdowns during large queries, things ran afowl with InnoDB on my XAMPP MySQL installation (Windows 7 x64). I rebooted and ran Startup Repair, then loaded MySQL to ...
1
vote
2answers
2k 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 ...
1
vote
2answers
851 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 ...
2
votes
1answer
350 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
3answers
36 views

guideline for calling optimize table after alter table?

Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing ...
0
votes
2answers
15 views

How to check innodb history list length?

The output for show engine innodb status has the history list length as a number, but that output is hard to parse, and there's just too much data when all you want is a single value. Is the value ...
0
votes
0answers
15 views

Error 1911 'STATS_PERSISTENT' [closed]

When I run the code mysql -u root -p < cpt-all-db.sql and then enter my password I get the error code ERROR 1911 (HY000) at line 6609: Unknown option 'STATS_PERSISTENT On line 6609 this is the code ...
0
votes
1answer
909 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 ...
0
votes
1answer
244 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
4
votes
5answers
796 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
1answer
147 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, ...
2
votes
1answer
45 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
2
votes
0answers
38 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
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 ...
1
vote
2answers
31 views

Auto Incrementing column based on another mysql

I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2,3 or 4. ...
0
votes
1answer
42 views

.ibd file is missing and alter table discard tablespace crashing the server

Data file(.ibd) for one of my mysql table is missing in data directory. As read in some articles i want to resolve the issue by discarding tablespace first ,import the tablespace (cfg file of said ...
-1
votes
1answer
32 views

Mysql InnoDB vs Mongodb write performance

I need to increment a counter each time a web page is rendered. When I use mongodb to do that, I can do about 16000 writes per second on a 4 cores/8 threads CPU on a regular disk. When I use Mysql ...
3
votes
3answers
11k 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 ...
2
votes
1answer
46 views

MySQL Performance: conditionnal left outer join vs where only

I have three tables: FORUM ( id PRIMARY KEY, public BOOLEAN ) MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id) USER( id PRIMARY KEY, ...
0
votes
1answer
10 views

Aruba-MySQL: can't create/change table to engine=INNODB

We have our database stored in aruba (mysql.aruba.it) where there is a table called "task". Because of many changes in the requirements we decided to drop the table and create it again from 0 with ...
0
votes
0answers
7 views

Database storage engine and performance [duplicate]

What will be the performance issue when I change my database storage type from MyISAM to InnoDB?
3
votes
2answers
967 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
0
votes
0answers
18 views

Should innodb_buffer_pool_size be much larger than the innodb_buffer_pool_bytes_data?

Our old server had 32GB total RAM, 18GB allocated to innodb_buffer_pool_size. It also had 10GB for the php_memory_limit. The new one had 16GB RAM, 9GB for innodb, 5GB for php. The same application ...
2
votes
3answers
86 views

All My Databases Are Gone?

Today I changed my username and password in MySQL and I logged into phpmyadmin to see that all my databases were gone. I've tried changing the datapath but then I realized I didn't see any of my ...
1
vote
1answer
246 views

mysql innodb_buffer_pool_size let me understand

I couldn't find answer for my situation. I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM). I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size). ...
1
vote
0answers
35 views

Mariadb support rtree index on innodb soon?

I was looking through mariadb bug list online (https://mariadb.atlassian.net/secure/RapidBoard.jspa?rapidView=24&projectKey=MDEV), but I am unfamiliar with JIRA so I am not sure exactly what I am ...
0
votes
1answer
157 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
2answers
2k 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: ...
0
votes
1answer
110 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 ...
0
votes
1answer
157 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 ...
31
votes
4answers
54k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
3
votes
3answers
2k 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 ...
-1
votes
1answer
30 views

How important is InnoDB versus MyISAM? [duplicate]

Does it really matter if the default storage engine is InnoDB? My default storage engine is MyISAM
0
votes
1answer
160 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
1answer
18 views

MySQL Crashing every few days

Our website hasn't launched yet so we're gaining little to no traffic at the moment. For some strange reason MySQL seems to crash every 2-3 days unexpectedly. Here's the SQL log of the crash ...
4
votes
1answer
10k views

Mysql slowly uses memory until it starts to use swap

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
1
vote
3answers
104 views

Are TEXT fields stored separately from the row in MySQL-ISAM?

We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed ...
1
vote
1answer
30 views

Innobackupex apply-log should be done before emergency restore or after each backup?

Basically Percona Innobackupex consist with three steps: 1. Backup 2. Apply log 3. Copy back restore during emergency My question: When should I do apply log? Is it after each backup or before ...
0
votes
1answer
26 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
2
votes
2answers
89 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
2answers
38 views

Difference between Handler_write and Innodb_data_writes

Handler write is the number of requests to insert a row in a table. InnoDB data writes is the total number of data writes. Questions are Does inserting a row count as a disk write? If I do an ...
1
vote
2answers
2k 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 ...
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 ...
3
votes
2answers
168 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 ...
2
votes
3answers
58 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...