InnoDB : MySQL's ACID-compliant Storage Engine
1
vote
1answer
23 views
MySQL Performance vs. Memory
We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD).
Besides MySQL, we also run some Java services on this machine, which access the ...
1
vote
2answers
66 views
Find MySQL InnoDB index corruption
I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c.
When I run query:
SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3;
the ...
0
votes
1answer
22 views
Can select read index page while modify this page in innodb?
When mysql innodb insert or update secondary index page , will at the same time this index page can searched by select ?
0
votes
2answers
22 views
MySQL - Delete with IN Clause taking more time
In MySQL 5.6 I have around 6 crores of rows. I wanted to remove rows based on Id. Here Id is the primary key.
One strange behavior I had today.
delete from tbl where id=1;
Its executed in ...
1
vote
1answer
16 views
Recommended innodb_file_per_table value for AWS MySQL RDS database
We have the following scenario: Our databases are hosted on AWS MySQL 5.6, and we are planning the following migration: we currently have 10 distinct instances with about 50 databases split between ...
1
vote
2answers
28 views
When mysql innodb undo log write and fsync to disk?
Before transaction commit , every dml operation will modify innodb page which will flush to disk and generate undo log , so when the undo log write and fsync to disk to ensure rollback ?
0
votes
1answer
43 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 ...
2
votes
1answer
22 views
Optimising MariaDB for large AWS machines
Been reading up on DB optimisation (I am an analyst, not really a Sysadmin), and it would seem the biggest adjustment one can make is change the innodb_buffer_pool_size, innodb_buffer_pool_instances ...
1
vote
1answer
46 views
What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool?
We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are:
7.5gb RAM
2 VCPU
RDS defaults to a calculation that allocates 75% of RAM to ...
0
votes
1answer
17 views
How mysql innodb know which transaction should rollback after crash restart?
I know when I execute dml sql before commit , innodb will write the result to the innodb page cache which can flush to data page and write to the undo log file ; but at the same time , mysql crash and ...
2
votes
1answer
27 views
Suggestions for large DB maintenance
i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database!
Will the size be a problem ...
0
votes
2answers
51 views
InnoDB: mmap failed; errno 12
I am getting following error after modifying the innodb buffer pool size values.
InnoDB: mmap(137428992 bytes) failed; errno 12
InnoDB: Cannot allocate memory for the buffer pool
137428992 is ...
0
votes
2answers
51 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 ...
0
votes
0answers
32 views
Modelling a database for a scenario with different member types that are interrelated
I have database with different member types(Dealer, Supplier, Dealer Family, Supplier Family), each type
have common columns and there is some unique columns for the Dealer and Supplier types:
...
1
vote
0answers
39 views
Status shows InnoDB: 128 rollback segment(s) are active waiting for purge to start
MySQL process was killed due to memory issues. When start command was issued, server started and is working but a status command shows the following:
InnoDB: 128 rollback segment(s) are active.
...
1
vote
2answers
34 views
Strange deadlock with multi-column index with innodb on MySQL
I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about.
CREATE TABLE `active_foo` (
`active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT,
`c_id` ...
-1
votes
1answer
21 views
Mysql sudden high disk usage when connections increased
recently we had an incident where because of a slowdown in an external service, the number of processes on the web server increased a lot. This caused mysql to reach max connections. However, the ...
6
votes
4answers
83 views
Why the query optimizer choose this bad execution plan?
We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else.
innodb_version: 5.6.36-82.1
version : 10.1....
1
vote
0answers
17 views
MySQL InnoDB stop locking when large sql is executed
We face a issue that the InnoDB table is locked, when a sql file that is executed is large than a certain file size.
The problem does not appear when the file size is much lower, for example 25MB. ...
0
votes
3answers
78 views
Database performance improvements for current setup. (mysql - marriaDB)
I've currently got a pretty hefty database (for me anyway) with multiple tables exceeding 15 million rows. I'm trying to improve performance on the whole table lookup, I implemented memcached to cache ...
0
votes
0answers
22 views
Not able to alter a MySQL table. Gives error 'Table is full'
I'm trying to alter a 24 GB table by adding another column but i get the error TABLE IS FULL every time i try to alter it. INNODB is the storage engine used and i have already set ...
1
vote
0answers
25 views
Database with lots of writes to a big table uses less CPU than the same database with less writes?
I'm trying to understand why InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it.
I have a table with nearly 5M rows, about 9GB in size. I also ...
0
votes
1answer
23 views
How does DYNAMIC Row format handle NULL storage MYSQL 5.7?
As a newbie to Mysql I just can't understand how NULL values are stored in each row formats.
As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. ...
0
votes
0answers
17 views
Peformance problem for statistical queries for table that is update very often
I do have a database with a table with more than 200mi entries. There is near of 300 inserts per minute, 30 to each "customer".
I'm working with MySQL and innodb storage due high insert/update ...
2
votes
0answers
65 views
Database design for Subscription, Coupons and Plans
I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem.
I am developing a website ...
0
votes
1answer
28 views
What are the pros and cons of adding secondary columns to a table's primary key?
In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table.
I have a photos table. Queries on it come in many different ...
0
votes
0answers
34 views
mysql - help to understand where mysql using memory
Im running a server with 10GB memory. Ubuntu 16.04 LTS.
I have configured innodb_buffer_pool size to 6GB. Today I got the alert from monitoring tool which says mysql using more memory.
When I saw ...
1
vote
0answers
54 views
MySQL memory usage is too high - OOM
Im using 32vCPU and 120GB VM for Mysql 5.6.
it's a dedicated MySQL server.
15000 IOPS SSD disk.
Total database
size 40G
This server was working fine for a couple of months. Memory usage is ...
1
vote
0answers
62 views
Which columns are off-paged (ROW_FORMAT=DYNAMIC)?
When going through the MySQL 5.7 manual, I found some contradictory facts.
Fact 1:
When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length ...
1
vote
2answers
46 views
Repair/Recover data from MySQL database
I have a big problem with a MySQL database stored on my raspberry (InnoDB engine). I'm not very expert in this field but I'll try to explain to you the problem.
MySQL crash everytime that I try to ...
2
votes
0answers
17 views
IDB file Maximum kb limit
I'm new in handling MySQL database, I made a tracking system I using the MySQL database incorporated on the xampp package as a database. Everything is running smoothly, however upon reaching 90,112 kb ...
1
vote
1answer
26 views
Mysql refuses to start: mysqld got signal 4 ; “ib_logfile0” more than 2GB
Last night my mysql database crashed and I don't know how to restart it.
sudo /etc/init.d/mysql start fails. The /var/www/mysql/error.log contains the logs. The following log entry is from last ...
0
votes
0answers
28 views
A database with MyISAM tables on Galera cluster and perform an ALTER TABLE, will that change be replicated?
I've inherited a Galera cluster with DBs that are ENGINE = MyISAM. I know that by default, MyISAM tables are not replicated in Galera cluster. I'm not interested in "experimental support" within ...
1
vote
0answers
15 views
Unable to drop table/database when using innodb_force_recovery = 3
I'm attempting to follow several guides to recover a corrupted database, by setting innodb_force_recovery to 3, dumping the database, dropping the database, and then importing it back in.
I'm able to ...
0
votes
3answers
39 views
MySQL InnoDB Full Text Search is very slow when combined with other filters
I have a database with 23 million records which occupies about 16GB on HDD. 64-bit Operation System with 4Gb RAM.
For example the query
SELECT * FROM robjects
WHERE MATCH(type) AGAINST ('commercial'...
1
vote
3answers
46 views
InnoDB “holes” in large table 200 Million Rows?
I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the ...
4
votes
1answer
111 views
Is this surprising deadlock reasonable or a MariaDB bug?
I recently had to diagnose a deadlock in an application that seemed impossible to me. Even after I found out how to reproduce it, I don't really undestand why it happens. I'm considering filing a ...
1
vote
2answers
274 views
Inserts in MySQL 8 are slower than Inserts in MySQL 5.7
I've started to use MySQL 8 (8.0.12), and I'm realizing that the "inserts" are slower in this version than MySQL 5.6 or MySQL 5.7. It doesn't matter if the table is MyISAM or InnoDB, with just one "...
0
votes
1answer
15 views
Set InnoDB as default engine and set a default collation
What should I do to set InnoDB as the default database engine?
And is possible to set a default collation when I create a schema and tables (e.g. utf8mb4)?
I don't want to have to choose these ...
1
vote
0answers
52 views
Why does the Select statement stalls when executed directly in sequence?
When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go.
If the table is MyIsam, it works ok. If executed [...
1
vote
3answers
113 views
Would my MySQL database benefit from increasing the InnoDB buffer pool size?
Of course, the answer is pretty much always "yes", but I'm interested in my specific situation.
This is similar to Making sense of INNODB buffer pool stats but, IMO, a little more specific.
First of ...
1
vote
1answer
73 views
getting innodb buffer usage
I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run 'show engine innodb status' I see this:
----------------------
BUFFER POOL AND MEMORY
-----------------...
1
vote
1answer
38 views
Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'
Currently long_query_time is set to 5, and that works.
However, many queries are part of a transaction, and the slow query log only keeps the final commit;.
# Time: 2018-08-15T15:18:18.205309Z
# ...
1
vote
2answers
32 views
Will moving the MATCH…AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?
Basically, I have the following query:
select `t`.*
from `tracking` `t`
where exists
(
select `ti`.`id` from `tracking_items` `ti`
inner join `cars` `c` on (`c`.`car_id` = `ti`.`...
1
vote
3answers
78 views
Event doesn't work
I have a working command:
UPDATE ads SET ad_points = ad_points - 20
And I would like to perform this action every day.
After creating the event in phpMyAdmin (MYSQL) nothing happens (on the next ...
1
vote
1answer
50 views
Innodb updates records with delay
I have two tables in my database like these:
CREATE TABLE `main` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`status` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ...
0
votes
1answer
23 views
Zero Date Values & Converting Entire DB From MyISAM to InnoDB
I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's ...
1
vote
2answers
79 views
MySql 5.6: Ignores innodb_page_size setting in my.ini
So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini:
innodb_page_size=32k
I then stopped the MySql service, and restarted it. The service in Win looks like this:
"C:\...
2
votes
1answer
59 views
Caching queries that last longer then then X seconds
Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.
...
1
vote
0answers
965 views
InnoDB: Unable to lock ./ibdata1 error: 11
I'm pretty sure this issue has occured before and I've looked around some of the answers. However, I wasn't able to find someone with an exactly similar problem so I thought I'll ask a new question. :(...
