Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

436 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
0answers
247 views

What are the steps to change tables engines from MyISAM to InnoDB?

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table : ALTER TABLE t ...
0
votes
0answers
2k views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
0
votes
0answers
605 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
0
votes
0answers
2k views

Is it possible to optimize a range query so an explain statement would state “using index” instead of “using index condition”?

I have a very large table where specific queries made by my application are timing out. The table looks something like this: LargeTable ------------ Id [BIGINT(20)] UtcTimestamp [BIGINT(20)] Pivot [...
0
votes
0answers
264 views

Lock timeouts on a table with two keys, where only one connection writes for a given value of the first key at a time

I have a table user_rollup_message_list that is keyed by (user_id, message_reference). It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, ...
0
votes
0answers
45 views

Accessing Database from Slave server

I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost. ...
0
votes
0answers
2k views

MySQL always crash on start, ibdata1 bigger than 4GB

I'm having a problem with MySQL when testing my production data. Whenever I started MySQL (mysqld), it always crash. My suspect is on my InnoDB data file (ibdata1) since the size is already bigger ...
0
votes
0answers
57 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 ...
0
votes
0answers
818 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 ...
0
votes
0answers
275 views

table is full in innodb

One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached ...
0
votes
1answer
235 views

How to find the MySQL query that got rolled back

I want to know if a certain transaction got rolled back. If we analyze SHOW ENGINE INNODB STATUS\G; we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
2answers
1k views

how to remove Mariadb temp files

I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November. -rw-rw---- 1 mysql mysql 1032192 Nov 19 07:47 #sql899_5d5_0.ibd -rw-...
0
votes
1answer
93 views

Convert table from MyISAM to InnoDB and keep MyISAM rows order

I'm currently rewriting old project and I've got this strange scenario. It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part ...
0
votes
1answer
945 views

InnoDB row level locks without SELECTs?

I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation: Each client runs the following ...
-1
votes
0answers
16 views

Why my rds instance has so high write iops?

the sum count of the tps is about 500 (Com_insert: 30, Com_delete: 3~4, Com_update: 450~500) while the write iops is 8k+ and I found that the Create_tmp_disk_table is nearly always 0 ...
-1
votes
3answers
62 views

Optimal Indexing Strategy

For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the ...
-1
votes
1answer
106 views

Trick MySQL into thinking that table was created with DATA DIRECTORY option

We are runing MySQL 5.7 that has a very large table. We want to move that table outside of data directory, to another device. MySQL supports external tables but in order for it to work the table must ...
-1
votes
2answers
27 views

UPDATE with too much values at IN state takes too long

I have the next query: UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
-1
votes
2answers
245 views

How do I select specific columns in a sql prepared statement?

I have created a prepared statement to output a file and add a date to the filename. That part works if I select the entire database. I want to select specific columns to include in the query, not all ...
-1
votes
1answer
59 views

MySql query suddenly became very slow after a while

I have a program that reads a set of entries from a database, performs some set of operations on these entries, and updates them in the database, the select query is pretty straight forward, It just ...
-1
votes
1answer
254 views

Cannot find innodb_page_size option in [mysqld] section in my.ini file

Where can I find innodb_page_size option? Since I cannot find it in [mysqld] section in my.ini file. I want to change it to 32k or 64k since I am getting the below error: MySQL: Error Code: 1118 ...
-1
votes
1answer
1k views

What does innodb_table_locks setting do exactly?

Can someone please explain how this setting affects the locking behaviour in innodb (with an example). I ran numerous experiments with different configurations of (autocommit, innodb_table_locks, LOCK ...
-1
votes
1answer
1k views

How can Mysql server auto re-index tables

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table. Ex : +-----------------------+--------+---------+------------...
-1
votes
1answer
256 views

Optimizing sql query to search in more than 10 million record

I have a SELECT query to search a large table which contains more than 10 million records. I wish to optimize this query for reduce CPU load and get faster response: $query= 'SELECT DISTINCT ...
-1
votes
2answers
333 views

page size Issue in mysqlbackup command

I am facing issue while taking MySQL remote backup using enterprise utility mysqlbackup, the output and the command used to take backup is is as following 170717 09:30:02 MAIN INFO: A thread ...
-1
votes
1answer
201 views

Percona pt-deadlock-logger strange results

Running the deadlock detector from Percona Toolkit, I get something like the following (MySQL 5.7 / AWS RDS, reformatted for clarity) server ts thread txn_id txn_time user hostname ip db tbl idx ...
-1
votes
1answer
245 views

Can't determine cause of MySQL Deadlock

It appears to me that (2) TRANSACTION is attempting to acquire a lock it already holds. Here is the information from "show engine innodb status": ------------------------ LATEST DETECTED DEADLOCK ---...
-1
votes
2answers
163 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
275 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 ...
-1
votes
1answer
1k 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 ...
-1
votes
1answer
544 views

Can we restore mysql db without .ibd & .frm files?

I have a data folder and the database folder with .frm & .ibd files is missing, any way to restore the database? Currently the data folder has: ibdata1 ib_logfile0 ib_logfile1
-1
votes
1answer
150 views

2 tables in mysql database overloading cpu even after optimize

thanks for reading this question i hope someone can give some insight into some steps. Here is my steps report. I added memcached instead of mysql cache. optimize the 2 tables from buddypress that ...
-1
votes
1answer
50 views

Mysql InnoDB exsited but can't show it

I have a InnoDB table A , While I type "show tables" , I can't see it. Even I type "drop table A" , it responses "table doesn't exist" . While I create a table B which refers from A , table B can ...
-1
votes
2answers
617 views

High traffic slows down website with 8Gb RAM Server - my.cnf configured

We configured the my.cnf of our server yesterday. Today comes to a high traffic on our site. The site is going slow as the traffic goes high, and for sometimes get a downtime. We already switch from ...
-2
votes
1answer
137 views

VARCHAR vs TEXT performance when data fits on row

mysql> desc temp1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | value | ...
-2
votes
1answer
45 views

How can we migrate Data with MySQL 5.1 - MyISAM to another server with MySQL 5.6 - InnoDB

Currently we have a MySQL 5.1 - MyISAM database on a physical server. We need to move it to another server with MySQL 5.6 - InnoDB at another location. DB size is quite large - 210GB Can someone ...

1
5 6 7 8
9