All Questions

Tagged with
Filter by
Sorted by
Tagged with
2
votes
0answers
67 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
1
vote
1answer
71 views

Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no ...
0
votes
0answers
89 views

Which MySQL settings are the closest equivalent to RDS' “Queue depth”?

I'm trying to find out which MySQL status variables can be used to get as close as possible to the RDS value of "Queue depth". On a test system, I reckon that %pending% variables may be at least a ...
1
vote
1answer
24 views

LOAD DATA keeps CPU usage high after it finishes

I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this: mysql.RegisterLocalFile(fileName) _, err = dbConnection.Exec(...
-2
votes
1answer
55 views

VARCHAR vs TEXT performance when data fits on row

mysql> desc temp1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | value | ...
0
votes
0answers
65 views

index corrupt error while trying to index a large table

I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
0
votes
1answer
32 views

Why do some write sometimes go faster than expected for MEDIUMTEXT

We are running a MySQL Innodb database, our server has 4gb memory. We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column. Currently, we have this query ...
0
votes
2answers
37 views

What is going on with my user and max date query?

OK query below. Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate. I have used this query quite a few times but ...
1
vote
1answer
39 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
2
votes
2answers
156 views

When does MySQL begin to start sending data to a client?

I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
3answers
343 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
0answers
291 views

What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?

Mysql version: 5.7.18 (Innodb) I have a table with the following specification Structure: CREATE TABLE `dummybigint10` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT 'A ...
0
votes
1answer
57 views

BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT

This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table: | column name | type | |----------------+-------------| | id ...
0
votes
1answer
69 views

Database schema tracking patient stats over time

I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time. I found a database schema like the one discussed in this Q & A ...
2
votes
2answers
1k 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
votes
1answer
553 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
3answers
6k 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 "...
1
vote
1answer
123 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 # ...
0
votes
1answer
1k views

Innodb: When bulk inserting or updating - how to disable transactions?

When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
2
votes
3answers
309 views

How MySQL use the InnoDB Buffer?

For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from ...
0
votes
1answer
488 views

Help with setting up MySQL on a dedicated server - detailed report given

I need help because I do not understand much MySQL settings on a dedicated server. I have one WordPress website installed with medium traffic with W3 Total Cache. The storage engine is InnoDB. ...
0
votes
2answers
65 views

MySQL - Simple update is very slow and causing high load average with large amount of server

Simple update is very slow and causing high load average with large amount of server : table name : CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` ( `id_list_prom` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
5answers
544 views

Slow queries on indexed columns (large datasets)

I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete. product_base (1.5GB, 26 million rows) CREATE TABLE `product_base` ( `EAN13` varchar(13) NOT NULL DEFAULT '', ...
3
votes
1answer
1k views

Database slows down at millions of rows, how to optimize schema and queries?

I am building a database to store Ethereum Transactions that are connected to specific addresses. Once the database table eth_addresses_tx starts hitting 3 million + rows, things start slowing down a ...
1
vote
1answer
68 views

Setting up indexes to support a flexible API search endpoint

I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure ...
4
votes
2answers
1k views

Why query with SQL_NO_CACHE runs slower on the first run?

Query is made to be as fast as possible, using only SQL_NO_CACHE 1 in select and limit 1. The results are pretty curious; on the very first run it takes 0.125 - 0.203 sec. and all next runs take ...
2
votes
5answers
2k views

How to speed up update join query between 2 mysql tables?

alright long story short., i have powerful dedicated server. Intel I7-6700K - 64GB DDR4 2400 MHz 1x480GB SSD running mysql server along with nginx,php innodb-ft-min-token-size = 1 innodb-ft-...
3
votes
4answers
1k views

Read performance when indexing one column vs indexing multiple columns

(MySQL, InnoDB) I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by ...
0
votes
2answers
293 views

Multiple Schemas for increasing MySQL (5.7) Performance

Lets start with my question first: Does having multiple schemas (1 main and 1 daily - having 5-6 tables with same structure as main, but only working for the day and will be synced back with the main ...
1
vote
2answers
10k views

How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed

I ran a multi-threaded client spawning 25 threads to make concurrent API calls and insert data to AWS Aurora server. After some time, I started to see timeout error: lock wait timeout exceeded try ...
4
votes
2answers
13k views

MySQL - Simple update is very slow

We are having trouble with simple updates on a single table taking a long time. The table contains ~5 Million rows. Here is the table: CREATE TABLE Documents ( GeneratedKey varchar(32) NOT NULL, ...
3
votes
4answers
2k views

InnoDB memory/cpu use optimization for WordPress with MySQL

I'm not a DBA and need some help here... I have a high traffic WordPress site hosted in a EC2 instance. We are working in a new theme code that will have optimized queries, but right now I need to ...
0
votes
1answer
89 views

Is using innodb_file_per_table improve speed of alter table command?

As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table? I have a table of 40M ...
1
vote
2answers
2k views

Changed MyISAM to InnoDB, huge performance loss [closed]

So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss. Here is my.cnf: innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_buffer_pool_instance=2 ...
0
votes
2answers
753 views

InnoDB Read and Write speed constantly spikes

I am not a dedicated mysql admin but my friend and I have been working hard to fix the badly configured mysql files. We have reduced our number of pools to 2 from 20 and increased pool size to 90GB ...
1
vote
1answer
39 views

high concurency issue with read-write on mysql table

I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) : +-------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
2
votes
1answer
920 views

Query very slow on innodb

I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb. I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of ...
3
votes
2answers
566 views

Create table if not exist each time API called: bad practice?

I'm writing a simple API in PHP with MySQL as database for a web application. I intend to put CREATE TABLE IF NOT EXIST for all tables in each API call. I expect that I will have 5-6 tables. I'm ...
0
votes
2answers
113 views

What does slow down an InnoDB database that is fixed by re-importing it?

I recently noted a performance degradation on a MySQL 5.6 (InnoDB) database with several million records. This particular query was taking around 20 seconds on the first run (apparently data was ...
0
votes
2answers
681 views

Why Mysql is not using indexes on join

I have a query that joins table SPACES with 3 rows. But MySql is not using indexes. EXPLAIN SELECT environmen0_.description AS col_0_0_, environmen0_.fk_user_profile_id AS col_1_0_, ...
0
votes
2answers
2k views

Why does this trigger slow down updates so much

I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records. On tableA, there's a trigger on update which looks ...
1
vote
2answers
233 views

Does MySQL InnoDB table perform faster when data is removed

I have a 5GB InnoDB table in MySQL. I updated several rows where a column had massive amounts of data to be blank, hoping to make the table smaller and perform quicker. I then discovered that InnoDB ...
4
votes
2answers
5k views

How to find out “InnoDB Write buffer efficiency”?

Is there any way to find out that "InnoDB Write buffer efficiency" without tools like mysqltuner? I can't find it in INNODB STATUS or information_schema.
1
vote
1answer
1k views

Randomly slow inserts with InnoDB

TL;DR : How can I improve insertion time in my table so that every inserts take under 100ms to perform? This table is used by a webservice which receives transactions one by one so I can't group ...
0
votes
1answer
975 views

Periodically very slow execution of simplest queries

Inspecting MySQL slow query log I found out that in certain periods of time there are a bunch of queries in log which are supposed to be very fast but were actually very slow. For example: # User@...
3
votes
1answer
634 views

DELETE Rows with or without LIMIT

When deleting, should I use LIMIT, or not (MySQL INNODB tables) Each customer has a unique hash. Each customer can have one or more records in a single table. If I know in advance a customer can ...
1
vote
3answers
1k views

Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?

I am running a Nextcloud instance and I suffer from a slow sync when a lot of small files are synchronized. I found out that innodb_flush_log_at_trx_commit = 1 is responsible for this so I was forced ...
0
votes
1answer
7k views

Performance tuning large mysql databases for zabbix

I have been working on tuning a mysql database for zabbix for quite some time now. We do not actually have a DBA to assist with this. We have a Zabbix server running 16k values per second. Our ...
2
votes
2answers
273 views

Different execution with same query

In a migration plan I am taking a mysql 5.5 database with only MyISAM tables and converted it into mysql 5.7 with InnoDB tables. The create table statements are here: http://sqlfiddle.com/#!9/bb815d. ...
2
votes
1answer
4k views

Handling a very large MySQL database

Sorry for the long post! I have a database containing ~30 tables (InnoDB engine). Only two of these tables, namely, "transaction" and "shift" are quite large (the first one have 1.5 million rows and ...