Tagged Questions

InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
3 views

Move a MySQL 5.5 database to a server running MySQL 5.1

I have a backup of a MySQL database (using InnoDB) from a Windows machine that was running MySQL 5.5. The Windows machine is no longer available and now I need to import the backup into an Ubuntu box ...
0
votes
1answer
20 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from dump-file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys. ...
0
votes
1answer
4 views

How to resolve “InnoDB data dictionary has tablespace id N” [duplicate]

I recently messsed up a migration to table-per-file for my dbs. In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed ...
0
votes
1answer
35 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 ...
0
votes
0answers
13 views

MySQL - Pending flush list, aio write, and the innodb_use_native_aio parameter

I'm running an Amazon RDS instance with MySQL 5.5. The applications using that are very write intensive, and there's over 1TB of data. Everything was fine until a recent minor version upgrade and ...
0
votes
1answer
28 views

Best Innodb Buffer Pool Size for 8GB RAM?

I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size. That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina ...
0
votes
0answers
38 views

Benchmarking MyISAM vs InnoDB using Sysbench on MariaDB-10.0.14

I performed a "read" benchmark on MariaDB-10.0.14 db server using sysbench to compare performance between InnoDB and MyISAM on a single. Below are the sysbench commands i used: Create test table in ...
0
votes
1answer
22 views

update column without auto timestamp update

Hello everyone I have got a product stock table with a time-stamp column so the so any updated to that row will update the time stamp ,I'm happy with this setting but sometimes I want to update the ...
0
votes
1answer
33 views

Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table. The table in question is an InnoDB table and the ...
0
votes
0answers
10 views

How to diagnose MariaDB crash when changing InnoDB column to AUTO_INCREMENT?

I have this table, and I'm trying to change the first column to AUTO_INCREMENT: +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
0answers
18 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
0
votes
0answers
15 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...
0
votes
1answer
29 views

In which situations is TokuDB higher performance? [closed]

I noticed that WordPress runs quite a bit faster when I use XtraDB (MariaDB's InnoDB replacement) instead of TokuDB. This is strange, because TokuDB is advertised as being 20x faster. However, I ...
0
votes
1answer
23 views

InnoDB error: exceeded log capacity…but only during import

I see this error in mysql.log: InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc.. I think I understand it, but the fact is that it's happening only during a ...
0
votes
1answer
21 views

mysql autoincrement counter

I need a way to keep auto-increment counter between mysql service restart. As docs states by default after restart mysql autoincrement counter start from max(id)+1. How can I keep the last ...
1
vote
0answers
41 views

innodb slower much slower than expected

Trying to figure this one out and I apologize for not providing a lot of information from the start (if you need further let me know and I can try to provide it). Query is performing very poorly - it ...
1
vote
2answers
70 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
0
votes
3answers
27 views

Mysql Create table shows ERROR 1005 errno 150

I am trying to make a junction table, but I getting a ERROR 1005 errno 150 CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage ...
2
votes
1answer
54 views

Super slow (day long) count(*) after upgrade to 5.6

After upgrading to Percona 5.6.20 from 5.5.34 I noticed counts on larger tables take a massively long time to execute. The table is about 27G and 300M rows. It is not compressed and was innodb ...
0
votes
1answer
18 views

How to combine duplicate parent rows in a relational MySQL table

I have my DB structured like this with people.person_id as the foreign key for each row in people_meta. people table ╔═════╦════════╦═══════╗ ║ id ║ fname ║ lname ║ ╠═════╬════════╬═══════╣ ║ ...
1
vote
0answers
53 views

MySQL keeps inserting new rows automatically [closed]

I have no clue of what is going on. My script goes this way: $i = 0; $c = $somecount; while($i < $c){ $en = comment_version($comments, $i, 'en'); $ru = comment_version($comments, $i, 'ru'); ...
0
votes
2answers
73 views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
1
vote
1answer
37 views

I broke replication. Could this have been fixed?

I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but ...
1
vote
1answer
39 views

How to take physical backup of MYSQL database

I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone ...
0
votes
1answer
21 views

Identifying root cause of slow query outliers from pt-query-digest?

I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of ...
0
votes
1answer
33 views

Sudden periods of performance degradation

For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version ...
1
vote
1answer
48 views

Shut Down MySQL during InnoDB rollback after crash

I am puzzled once again. We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is ...
0
votes
0answers
31 views

innodb fulltext Table handler out of memory

I am running mariadb 10 and have a large innodb table with a fulltext index. When searching I am regularly getting this error ERROR 128 (HY000): Table handler out of memory Is there some way I ...
0
votes
2answers
55 views

MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

I've acquired a fail-over structure and I migrated my databases to new structure converting all tables of my database from MyISAM to InnoDB engine, but, after import all databases, I have a problem ...
0
votes
1answer
33 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; ...
0
votes
0answers
16 views

Does storing page views in my page table slow performance?

I am storing page views in a view_countcolumn on my page table. So every time the page gets loaded I increment the view count and update the entry. What affect can this have on the performance of my ...
0
votes
1answer
63 views

MySQL keeps crashing

I've restored my vserver from a backup which was created by my host. After that my MySQL server is not starting anymore. I've tried several ways to recovery my tables, but it's still not working. ...
0
votes
1answer
25 views

Fastest way to alter column type in a 2-billion+ records innodb table

I have a MySQL table with 4 columns. The 1st column record_id is of type int, primary key, and auto-incremented. Now that I have added more than 2 billion records to this table I am finding that I ...
0
votes
1answer
28 views

innodb fulltext ibdata1 size

I am running mariadb 10 and have a large innodb table with a full text index. I have innodb_file_per_table=ON I've noticed though that the ibdata1 file grows at the same rate as my table file. I ...
1
vote
1answer
44 views

Innodb issue is preventing MySQL from starting

For some reason mysqld refused to start after I rebooted my server. Now when I try to start it I will get the following error: [root@vps1 ~]# service mysqld restart ERROR! MySQL server PID file ...
2
votes
2answers
165 views

High MySQL “InnoDB Writes Per Second” - Can't see the queries?

On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc. I noticed today that on the "Server Status" tab there's ...
0
votes
1answer
20 views

Automatic slave reconfiguration after updating master

I have development mysql master and slave servers and a prod server. I need to set up regular (once a week) syncronization of dev and prod databases. It would be easy for me to use prod# mysqldump > ...
0
votes
1answer
71 views

Very slow response even with a high number of available connections

I'm running this server for data mining purposes. It runs several compute-intensive data mining applications in parallel and does simultaneous access to the MySQL server. Here are the ...
0
votes
1answer
57 views

Appropriate use of mysql Optimize Table

I want to come up with some best practice for maintaining our mysql database. versions 5.5/6 and using InnoDB. i came across this artice - ...
0
votes
0answers
7 views

Why pages not made young is zero?

In results of Show INNODB Status showed me very weird result: Pages made young 81821279, not young 0 The official statement says: Pages made young, not young: The number of old pages that were ...
0
votes
1answer
32 views

MySQL InnoDB log files on different location

I am using MySQL version: 5.5.38 There are following parameters that can control InnoDB log files: innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ...
0
votes
0answers
43 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
119 views

mysql - Recovering databases from ibdata1 , .frm and .idb

I have a server running a mysql instance which crashed today. Now there are a massive amounts of tables in multiple database exceeding over 200GB stored on disk. After the server crash mysql is ...
2
votes
1answer
74 views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (suprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
1
vote
1answer
48 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
0answers
16 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
2
votes
1answer
55 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
0
votes
1answer
47 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: ...
1
vote
0answers
37 views

mysqldump with --single-transaction seems to be “fixing” InnoDB tables with page corruption - why?

I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest ...
2
votes
2answers
61 views

Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases)

We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client. Each database has ~200 table, and we ...