InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
853 views

Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
0
votes
0answers
54 views

Federate engine to help overcome distributed transaction?

Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we ...
2
votes
1answer
1k views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
1
vote
1answer
749 views

Why InnoDB ALTER TABLE is so slow? [closed]

The machine has a 4-core hyper-threaded Intel i7 670 @ 2.8GHz, 16G RAM, 8G of which is InnoDB buffer pool. The database resides on Intel SSD disk. DBMS is MariaDB 5.5.30 x64 running under Windows ...
1
vote
1answer
449 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
1
vote
1answer
548 views

Would Mongo's ObjectID work well in InnoDB's clustered index?

Mongo's ObjectID is defined like this: ObjectId is a 12-byte BSON type, constructed using: a 4-byte value representing the seconds since the Unix epoch, a 3-byte machine identifier, ...
0
votes
2answers
475 views

Convert the table PROCESSLIST from InnoDB to MYISAM

I use MySQL. My database's name is "db", and I have a table called "Test". I want recover the last update, so I used this query: SELECT update_time FROM information_schema.tables WHERE ...
2
votes
1answer
79 views

Which hardware you suggest?

I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be ...
5
votes
2answers
12k views

MySql - changing innodb_file_per_table for a live db

I have a large MySql DB (150GB) and only now i've noticed that the innodb_file_per_table is set to off which cause the entire DB to be hosted on one single file (ibdata1). i want to active ...
1
vote
2answers
517 views

mysql innodb_flush_log_at_trx_commit clarification

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years) So ...
0
votes
2answers
573 views

what happens when InnoDB logspace is too small

I've just seen several mysql errors in my log: InnoDB: ERROR: the age of the last checkpoint is X InnoDB: which exceeds the log group capacity Y I understand, that I do have to increase the ...
0
votes
0answers
74 views

InnoDB data back in 3 months after OS shutdown

I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash ...
1
vote
1answer
2k views

MySQL dynamically optimize innodb tables without “file per table” setting

We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
1
vote
1answer
1k views

Sorting Result state taking very long time

I am using a MySQL 5.1.69-log DB for analysis purpose. I've designed my database in a star schema. All tables are using InnoDB. My innodb_buffer_pool_size=2Gb This is the query I am running : ...
1
vote
1answer
1k views

MySQL - Export two columns from large table without causing a lock

I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I ...
0
votes
1answer
169 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
1
vote
1answer
283 views

Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Environment: Ubuntu 12.04 (and 13.04) MySQL 5.6.11 I have a table which has a full text index on it (real table has much more columns and rows): DROP TABLE IF EXISTS articles; CREATE TABLE ...
5
votes
5answers
9k views

TokuDB not much faster than MySQL

I have converted a MySQL database with 80.000.000 rows to TokuDB. Now when I run: select count(id) from xxx where active=1 it takes 90% of the time of the normal MySQL request. What do I have to ...
9
votes
1answer
21k views

How to solve “The table … is full” with “innodb_file_per_table”?

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
2
votes
1answer
834 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
0
votes
1answer
35 views

InnoDB Tablespace critical error in great need of a fix [duplicate]

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG Question: Could the error shown on the screenshot be the reason why my site is very slow?
11
votes
2answers
3k views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
2
votes
1answer
2k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
4
votes
2answers
7k views

Cannot Utilize Maximum CPU and Memory Usage for MySQL

Good day. I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem. I have a single stored procedure ...
1
vote
1answer
523 views

Percona xtrabackup Prepare Fails

I made a backup and according to Percona it completed successfully. Now I'm trying to prepare it and having the following issue: ./xtrabackup_55 --defaults-file="/etc/my.cnf" ...
4
votes
2answers
3k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
1
vote
2answers
4k views

Is it better to use AUTOCOMMIT = 0

Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like INSERT INTO SomeTable (column1, column2) VALUES ...
1
vote
2answers
211 views

TokuDB/InnoDB Question

What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second. That's okay to answer of one engine. TABLE: `game` `gameId` bigint 20 (PK) `gameMapId` int 4 ...
3
votes
1answer
2k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
0
votes
1answer
2k views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are send ...
9
votes
3answers
15k views

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
2
votes
1answer
2k views

Ubnutu/MySQL - Error “Unable to locate package innotop”

I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29. Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop" root@server1:~# apt-get install ...
1
vote
1answer
402 views

MySQL-5.5 InnoDB memory issue

Version in use is mysql-5.5.24. In the Enterprise version of MySQL innodb_buffer_pool space is released after a while, however this does not appear to be happening in the community version. I am ...
1
vote
1answer
870 views

Queries getting stuck on very simple COUNT queries

process list: | 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | ...
0
votes
1answer
2k views

Lock wait time out exceed restart transaction

I had got error on the lock wait time out so below I got 3 samples taken. One I took before the increase innodb_lock_wait_timeout to 120. So is there anything else I must tweak based on the logs ...
4
votes
1answer
90 views

How can I get this query to use it's index?

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime). As I don't really know what I'm doing I tried adding indexes to all columns in ...
4
votes
2answers
5k views

Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?

Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ? For example : /etc/init.d/mysql stop rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql
2
votes
1answer
1k views

Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that ...
2
votes
2answers
3k views

How to tune mysql to be inmemory-like

I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is). Is it possible to force ...
0
votes
2answers
522 views

Using MySQL InnoDB as an Archive

My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
0
votes
0answers
664 views

InnoDB errors after having deleted “ibdata1”

By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly. After reading so many forums, I found a post that recommended deleting the 3 files ...
1
vote
1answer
434 views

MySQL InnoDB does not return the last row on select with DESC order

We have a table scheme like following: CREATE TABLE IF NOT EXISTS `offers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaign_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `price` double ...
2
votes
1answer
643 views

What causes InnoDB to write 100% more pages while slowing down answering queries?

I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
3
votes
1answer
3k views

MySQL: Can't find .ibd files after turning on innodb_file_per_table

Question: This a question about MySQL. Let me give the specifics of my setup: Mac OSX 10.6.8 MySQL 5.5.28 Workbench 5.2.44 for all administration InnoDB engine exclusively In order to manage ...
3
votes
1answer
922 views

MySQL - Installing InnoDB Plugin

I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61. I want to upgrade the master server to a stronger machine with the following: 2 processors - 6 cores each 48GB of RAM To ...
2
votes
1answer
467 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
0
votes
0answers
133 views

InnoDB tuning with 1G of ram limit

I am trying to calculate variable moving averages crossover with variable dates. That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
1
vote
2answers
610 views

What is the size of currently-existing tmp tables in innodb?

This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
1
vote
1answer
494 views

innodb changing row format doesn't decrease table size? [closed]

We're currently using MySQL with innodb and we have some large tables that are compact in row format. When I change the row format to compressed we are still seeing the same size for the table. Anyone ...
0
votes
1answer
287 views

Maintenance in MYSQL when innodb_file_per_table disabled

I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace. But what if I ...