InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

2
votes
0answers
61 views

Identifying table causing runaway rollback in MySQL 5.5 (InnoDB / Windows)

I have a large database (180 GB), running under Windows, with a runaway rollback that starts running as soon as the database is started up. I have started the database with innodb_force_recovery=3 ...
1
vote
1answer
1k views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
1
vote
2answers
849 views

ERROR Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

We are facing the issue in replication that we are not getting any error in Show slave status\G output rather facing the following issue in Error Log File [ERROR] Error reading packet from server: ...
1
vote
3answers
88 views

Very slow Insert and update on 20 core 250 Gb RAM dedicated MYSQL 5.5 server on Ubuntu [closed]

Most of the data is in tbl_user_ads. Its a huge table. Many concurrent processes (over 1000) try to select, insert or update this table. The CPU utilization is just around 2%. As I increase the ...
1
vote
1answer
59 views

Mysql stop working woocommerce

I have a WooCommerce website hosted on an Amazon EC2 centOS server with 4GB RAM and 4GB swap. My problem is that sometimes MySQL stops working due to failure to allocate memory and also the website ...
4
votes
3answers
3k views

Optimal Number of MySQL InnoDB Buffer Pool Instances

Server Characteristics Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL) Number of CPU Cores: 6 I have data in the db amounting to about 2GB I have ...
0
votes
1answer
69 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
0
votes
1answer
53 views

when do i usually optimize an innodb table?

I have a table that's always used 24/7. There is always a stream of update and/or insert of 10-20 rows per 30 to 60 seconds. As stated on the MYSQL documentation: "After doing substantial insert, ...
1
vote
0answers
111 views

How to configure correctly Mysql heavy used server to prevent it from write IO huge spikes?

I have mysql 5.6 server which handles thousands of users online. All tables are Innodb. The problem is, that once or twice a day it writes huge amount of data to disk, or it slows down a lot. Than ...
1
vote
1answer
70 views

Multi-Column PK or Auto-Increment PK?

In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those ...
2
votes
1answer
214 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
0
votes
0answers
108 views

Discard tablespace of temporary innodb table in MySQL

I have following problem, i need to discard tablespace of temporary table(file is present, i want to delete this file too) or somehow completely remove this table. This table can't be shown with ...
2
votes
4answers
2k views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
0
votes
1answer
627 views

MariaDB/InnoDB: Temp file write failure

When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error: ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB The command ...
0
votes
2answers
48 views

What storage engine do I need - Mysql?

I have designed a database and came to the point of actually deciding which storage engine is better for my case. I estimate that during the first few years I would have around 100,000 - 500,000 ...
1
vote
0answers
76 views

Update queries randomly slow on INNODB Mysql

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine. The issue is that, ...
1
vote
1answer
2k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

I've got the same error, although I'm running galera cluster with 3 nodes. I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after ...
0
votes
0answers
66 views

Percona: Innobackupex failing when taking incremental backup

I have been trying to take incremental backup as below and its getting error all the time. Innobackupex 2.1.8 Linux x86_64 Mysql 5.5.45 Step1: innobackupex -uxxx --stream=xbstream datadir > ...
1
vote
1answer
52 views

How can i write a stored procedure which will perform a calculation and store the result in a table?

I have two tables: CREATE TABLE `siteArea` ( `length` decimal(6,2) NOT NULL, `breadth` decimal(6,2) NOT NULL, `Area` decimal(6,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ...
0
votes
0answers
50 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
3
votes
4answers
298 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
3
votes
1answer
126 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
2
votes
0answers
32 views

FullText search using only some of columns in index

I have a full text index on (name,category,description) as shown below. mysql> show create table phppos_items; ...
1
vote
1answer
90 views

Unable to load mysql dump file

I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied ...
1
vote
3answers
254 views

Convert MyISAM to InnoDB

What are the considerations needed when converting myisam to innodb. I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to ...
2
votes
1answer
74 views

SQLyog and MariaDB engine

I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am ...
2
votes
2answers
776 views

tmp_table_size with mostly InnoDB tables

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two ...
1
vote
1answer
146 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
1
vote
1answer
60 views

Too many TEXT fields leads to record too big

I have a table with multiple 9 TEXT columns, each containing JSON data. I usually UPDATE all the columns at once. I can't seem to add a 10th TEXT column. The table is InnoDB. What can I do?
0
votes
2answers
52 views

MySQL InnoDB data restore using 3 files

I am able to restore MySQL table data along with structure with indexes (InnoDB Engine) using 3 files. MyTable.frm, MyTable.ibd and ibdata1. Can any body confirm, will these 3 files suffice to ...
-1
votes
1answer
42 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
1
vote
2answers
901 views

HeidiSQL: Import CSV

A few lines of my CSV: "id","date","date_","pow","ene","reac" "ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43" "ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43" ...
2
votes
1answer
545 views

MySQL server won't start with foreign_key_checks=0

I'm running Ubuntu with MySQL version 5.5.44 mysqld Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu)) I have a DB with foreign keys. I would like to have the foreign key ...
2
votes
2answers
29 views

Should the Redo log on a production MySQL server be configured to another location?

Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order ...
0
votes
0answers
153 views

Tuning MySQL with InnoDB engine for my application

I've read through a lot of answers, and I realize this is mostly application specific, so I wanted to give some background as to what I'm doing with the database and hopefully get some tips on tuning ...
0
votes
1answer
41 views

Is my import still committing?

I'm importing around 9 million rows into my MySQL DB using the following command: SET autocommit=0; SOURCE my_import.sql; COMMIT; The command line is no longer outputting lines of Query OK, X rows ...
0
votes
0answers
69 views

MariaDB error on alter table - table removed - constraint name is blocked

Today, we experienced a strange behavior in 5.5.41-MariaDB (InnoDB): An ALTER TABLE (add constraint) statement failed with error (mysql server has gone away after that). Then the database table ...
2
votes
2answers
164 views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
1
vote
1answer
83 views

Int comparison very slow in query

I have this for simplicity fictional table which explains my problem: CREATE TABLE IF NOT EXISTS `posts` ( `pid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `posts` int(11) NOT ...
2
votes
0answers
116 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
2
votes
1answer
199 views

cannot delete db table

I tried to use a DROP statement to delete a table but I could not.Instead I tried the following....I deleted the .frm and .ibd files from of the respective table from the directory. Then I used a ...
3
votes
0answers
537 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
1
vote
1answer
60 views

Clarification on mysql deadlock situation

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that 1) Transaction (2) holds exclusive ...
2
votes
2answers
84 views

Information schema not showing reliable information?

I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information ...
1
vote
1answer
151 views

Hardware specs for MySQL database with millions of records

For 35 million records I have this table CREATE TABLE IF NOT EXISTS `records` ( `code` varchar(12) NOT NULL, `type` varchar(255) NOT NULL, `used` set('y','n') NOT NULL default 'n', PRIMARY ...
1
vote
0answers
48 views

Migrated servers… Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

I moved servers. I exported DB on old, imported to new. Kept getting syntax error on import until I tried no compression. Weird, but it worked. I was skeptical so I compared the two carefully and ...
2
votes
1answer
329 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
0
votes
1answer
37 views

Which are the differences and advantages on single INDEX or compound INDEX?

I have two INDEX on a n:m relationship table as you can see on pics below: Now I can have this also: My questions: What is the advantage from last way(pic compound) over the two first? Can ...
0
votes
2answers
130 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...
3
votes
0answers
80 views

Flexible and robust way to cull older data?

I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...