Questions tagged [innodb]

1

votes
2

answer
56

Views

Why does a SELECT wait for a lock?

In my application I have the problem that sometimes SELECT statements run into a java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction exception. Sadly I can't create an example as the circumstances are very complex. So the question is just about general understanding. A litt...
Werzi2001
0

votes
1

answer
19

Views

Mysql Group By Query Performance Very Slow

I am using Mysql 5.7 I have table which having 1 900 516 rows. My query performing group by on column which is already indexed but still takes time for execution. Below is my query,execution plan and table schema. select `type`,count(`type`) from templog group by `type` ; Query Explain format { '...
5a01d01P
0

votes
1

answer
12

Views

MySQL/InnoDB: Update large table from smaller, mission critical, table without locking small table

In MySQL, I have two innodb tables, a small mission critical table, that needs to be readily available at all times for reads/writes. Call this mission_critical. I have a larger table (>10s of millions of rows), called big_table. I need to update big_table, for instance: update mission_critical c, b...
juacala
1

votes
0

answer
7

Views

No queries working on table after LOAD DATA INFILE

I've tried to upload a large CSV file (14.8GB) to a MySQL DB using InnoDB engine and the LOAD DATA INFILE command. The query was completed since InnoDB status shows the total number of rows were inserted: -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 1 read...
Pelayo Fernandez
1

votes
2

answer
328

Views

In which case would MySQL's InnoDB's COMPACT row_format would be faster/better than REDUNDANT?

I saw many comparisons on Stackoverflow, DBA and Server Fault, but it's never actually clear when it comes to performance with specific situations, whether using COMPACT or REDUNDANT row format with InnoDB Are there any cases in which some simple tables would have a definitive performance boost? For...
1

votes
1

answer
618

Views

mysql InnoDB: FOREIGN KEY constraint performance

I have the following InnoDB tables: CREATE TABLE `vehicle` ( `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) DEFAULT NULL, `Model` varchar(100) DEFAULT NULL, `Engine_Type` varchar(70) DEFAULT NULL, `Construction_From` date DEFAULT NULL, `Construction_To` date DEFAULT NULL, `En...
pankal
1

votes
1

answer
538

Views

How do I determine if I have uncommitted writes in a MySQL transaction?

I'm working with a very complex codebase and wish to do some introspection of how it works with MySQL. (Note that I'm using InnoDB). Specifically, in a method I'm writing, I wish to determine if there are any outstanding (non-committed) writes in its open db connection. Is there any MySQL command...
UsAaR33
1

votes
2

answer
2.2k

Views

How to move InnoDB table to another drive?

I have MySQL running on SSDs, SSDs that I'm about to run out of space on. My webhost overcharges for SSDs and the majority of the data in MySQL is 'archived' data (i.e. data that isn't actively used). I have larger HDDs that can hold this data. As such, I want to be able to move specific InnoDB tabl...
user4951834
1

votes
1

answer
254

Views

SQL - Cannot add foreign key constraint

I am completely new to writing SQL code and I am attempting to run a simple table creation, however I cannot find where the error in my programming is, and as I am completely new I am struggling with this creation. This is a school project that I am working on, and hoping anyone can help. The error...
Zachary Walker
1

votes
2

answer
24

Views

ERROR 1005 (HY000): Can't create table 'db.POSTS' (errno: 150)

Hi I'm having issues creating my post database. I'm trying to make a forenge key to link to my users database. Can someone please help? Here's the code for my tables : CREATE TABLE USERS( UserID int NOT NULL AUTO_INCREMENT, UserName varchar(255), UserPassword varchar(255) NOT NULL, UserEmailAddress...
Liamh101
1

votes
1

answer
66

Views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `title` (`title`) ) ENGINE=...
abdulmanov.ilmir
1

votes
2

answer
95

Views

InnoDB composite indexing order and INSERT performance

I have a MySQL table with 3 columns on which I'd like to use a multi-column index. Column A is TINYINT, B is SMALLINT and C is VARBINARY (16). Should I use the index as A, B, C, because A has lower granularity than B and B lower than C to achieve maximum INSERT speed?
pedmillon
1

votes
1

answer
126

Views

MySQL unexpectedly crashes

I'm running Ubuntu 14.04 and I've got a few WordPress installations on my server. Without warning, MySQL crashes every day on my server and I've yet to find the reason. You can find the error.log below. 160721 9:25:44 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-opt...
SAGShow
1

votes
1

answer
332

Views

Unable to rename table due to metadata lock

I am trying to rename a table but it always just hangs with a Waiting for table metadata lock. Here is how I am trying to do it: start transaction; RENAME TABLE `main_territorypricing` TO `main_territorypricing2`; RENAME TABLE `main_territorypricing1` TO `main_territorypricing`; COMMIT; Yet the firs...
David542
1

votes
1

answer
413

Views

Does a MySQL DELETE physically reorder an InnoDB table?

Does a DELETE statement on an InnoDB table in MySQL cause the table to be physically reordered? I have a huge (200 million) row table that I want to delete certain records from. When I delete from it based on a WHERE condition it takes something like 90 minutes but only affects a few hundred thousan...
AS7K
1

votes
4

answer
100

Views

What is the best and safest method to update/change the data type of a column in a MySQL table that has ~5.5 million rows (TINYINT to SMALLINT)?

Similar questions have been asked, but I have had issues in the past by using ALTER TABLE tablename MODIFY columnname SMALLINT I had a server crash and had to recover my table when I ran this the last time. Is it safe to use this command when there is that much data in the table? What if there are...
Justin B
1

votes
2

answer
29

Views

MySQL Indexes: composite vs column

I Have gone through some Q&A eg. How does database indexing work? Mysql covering vs composite vs column index These are good reads, But I got some more question about Indexes ie. Assuming below table and Execution plans: CREATE TABLE student(`id` INT(9), `name` VARCHAR(50), `rollNum` INT(9), `addr...
Suraj
1

votes
1

answer
127

Views

What happens during the insertion, deletion and update in sql?

I would like to know a few things regarding mysql architecture. 1. How sql process insert, delete, update operations in an indexed table? 2. It is said that changes are only made in the change buffer when the index page is not in the buffer pool. So if changes are made after the buffer pool loads th...
Naveen Karthikeyan
1

votes
1

answer
1.1k

Views

Running MySQL on RAMDisk

I am in the process of speeding up a test suite that's using MySQL for persistence. I have changed the datadir and tmpdir in the my.cnf to use the RAMDisk as shown below. datadir = /run/shm/mysql tmpdir = /run/shm/mysqltemp The problem is that every time i run the entire test suite,...
ivanorone
1

votes
2

answer
519

Views

InnoDB Select For Update

If I have a simple statement as such for an InnoDB Table UPDATE SET locked=1, col2= WHERE locked=0 Is there any need to really do SELECT FOR UPDATE? Since InnoDB supports row level locking is there any chance of a collision even thousands of clients are executing the same script simultaneously? Up...
Ray Saudlach
1

votes
2

answer
403

Views

Select Query is very slow on MySQL InnoDB Table having 160M+ rows

Below are my table structure and index I have created. This table is having 160+ Million rows. create table test ( client_id varchar(100), user_id varchar(100), ad_id varchar(100), attr0 varchar(250), scp_id varchar(250), attr1 datetime null default null, attr2 datetime null default null, attr3 date...
andy
1

votes
1

answer
90

Views

In InnoDB, Why rows which are not in index range are getting locked?

consider the below query.. select * from Employee where ID>0 and ID 0 and ID0 and ID0 and ID
user2641906
1

votes
2

answer
105

Views

mysql select sql very slow, which one index lose

Helo, I have a doubt, uid column different integral can not use index, what's cause?: please someone tell me, thanks!!! table item_sort_20170525 have 222466057 lines, show create table this: CREATE TABLE `item_sort_20170525` ( `id` int(10) NOT NULL AUTO_INCREMENT `iid` bigint(20) NOT NULL DEFAULT '0...
Coola
1

votes
1

answer
39

Views

A seperate table for the posts which each user has liked - practical or not?

In a social networking site I'm making, I need some way to store which posts a user has 'liked', to ensure they can only 'like' each post one time. I have several ideas. A seperate table for each user, to store all of the different posts' IDs which they've liked as rows in said table. A space-sepera...
Jacob Garby
1

votes
3

answer
355

Views

Running Query in Queue MYSQL

I want to know something really important for my website, Imagine if I have thousand of users that accessed the website, then, At that time there probably be thousand of queries that might be running at the same, So, Don't that fill up the DB pool as at the same time there are thousand of queries.So...
I. Sharma
1

votes
2

answer
284

Views

How to manage large MySQL databases (backup)

We have a production MySQL database of about 320GB on disk. We need to perform regular backups of the dataset but, either mysqldump or 'copying table files' are taking hours to complete; moreover, both commands lock the database leading to an unusable website. At the moment out tables are InnoDB and...
pfari
1

votes
2

answer
81

Views

Does `UPDATE SET WHERE` have concurrency issues?

Consider the statement below UPDATE SET is_locked = 1 WHERE id = 1 and is_locked = 0; Does it have consistency problem under concurrent updates? Why? (MySQL 5.7, with transaction isolation level of REPEATABLE-READ)
John Woo
1

votes
2

answer
112

Views

How to direct MySQL not to cache a table in memory?

Lets say I have several InnoDB tables: 1. table_a 20Gb 2. table_b 10Gb 3. table_c 1Gb 4. table_d 0.5Gb And a server with limited memory (8Gb) I want fast access to table_c and table_d, and can allow slower access to table_a and table_b. Is there a way to direct MySQL to cache c,d in memory, and NOT...
Lavi Avigdor
1

votes
2

answer
89

Views

Why insert statement's time cost ranges so big?

I want to import Wikipedia Database. Wikipedia Dumps I have changed some Innodb settings: innodb_buffer_pool_size = 4G innodb_log_file_size=8G innodb_log_buffer_size=512M innodb_write_io_threads = 16 innodb_flush_log_at_trx_commit = 0 max_allowed_packet=256M This picture shows that every single Sql...
fei_hsueh
1

votes
1

answer
780

Views

Perisistent in-memory database in Apache Spark

I have a custom foreach writer for Spark streaming. For each row I write to JDBC source. I also want to do somekind of fast lookup before I perform a JDBC operation and update the value after I perform JDBC operations, like 'Step-1' and 'Step-3' in below sample code ... I don't want to use external...
Manjesh
1

votes
1

answer
48

Views

InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

Table structure for table config: property (primary key) value description Query: SELECT property, value FROM config If I put a covering index on (property, value) the optimizer still chooses the PRIMARY index, but the extra in EXPLAIN is NULL. If I tell the optimizer to use my covering index, the e...
Dan
1

votes
2

answer
48

Views

How do I get different scores for partially matching single-word searches on an InnoDB full text index?

I've got the following table in a MySQL 5.6 with an InnoDB full text index. CREATE TABLE `blacklist_entries` ( `blacklist_entry_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `insertat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`blacklist_entry_id`), FULLTEXT KEY...
simbabque
1

votes
1

answer
39

Views

Erratic increment in Mysql Auto increment column

I am seeing AUTO_INCREMENT column of MySql table having erratically large values. Somehow I am trying to understand how can this situation occur so as to avoid it. Current scenarios: Multiple tables are residing each with single AUTO_INCREMENT columns. All are having this issue. I am doing frequen...
BlueSword
1

votes
1

answer
165

Views

How does InnoDB store character columns?

This Question addresses only how 'short' CHAR and VARCHAR columns are stored in an InnoDB table. Does a CHAR(10) column occupy exactly 10 bytes? What happens with trailing blanks? What about character sets that need more than 1 byte per character? How does VARCHAR(10) differ from CHAR(10)? EXPLAIN i...
Rick James
1

votes
3

answer
107

Views

Optimizing delete operations in Rails 4 / ActiveRecord / MySQL

Essentially, we need to delete T1 records that have not had a t3 record associated with it for given @user. Though not required, it would be nice to delete T2 records as well that do not have a T3 join. This was the code that got pushed to production. Obviously, it's great, because it passed a uni...
webaholik
1

votes
1

answer
64

Views

mysql LAST_INSERT_ID with parameter sequence implementation

While creating threadsafe sequence in mysql, I came across mysql documentation - https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id, suggesting to create sequence like this - 1. mysql> CREATE TABLE sequence (id INT NOT NULL); 2. mysql> INSERT INTO sequence VA...
prats
1

votes
1

answer
655

Views

How to shrink ibdata1 file size after moving to innodb_file_per_table?

I've followed the instruction in this thread ( https://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql ) successfully but after 2 months the ibdata1 continues to grow. I've tried optimizing tables but it didn't reclaimed lost storage space. How do i sh...
Christian Burgos
1

votes
1

answer
165

Views

Major causes of mysql/Innodb crash and restart

After a recent DB crash with no RCA from our vendor. I'm left want to expand my knowledge on what sort of things can cause a database to crash. In our specific case the logs show record index mis-matches just before the crash. We believe this was due to copying .frm and .idb files to another db rath...
Tyler Christian
1

votes
1

answer
292

Views

Multiple MySQL Kubernetes pods with CrashLoopBackOff status

I got an issue with the deployment of a WordPress + MySQL application on a Kubernetes cluster. When using HorizontalPodAutoscaler to autoscale my wordpress and wordpress-mysql deployments, it works fine for the wordpress one but not the wordpress-mysql one. Indeed, when multiple MySQL pods are creat...
Julien Camus
1

votes
3

answer
216

Views

MariaDB crashing once per day with memory issues — allocating more memory does not fix

Every day for the past week, our MariaDB is crashing (typically late at night). It crashes with this in the logs: 2018-10-20 6:44:35 140525276776704 [Note] InnoDB: Initializing buffer pool, size = 184.0M InnoDB: mmap(202080256 bytes) failed; errno 12 2018-10-20 6:44:35 140525276776704 [ERROR] Inn...
JStrange88

View additional questions