All Questions
200 questions
26
votes
2
answers
10k
views
How do I properly perform a MySQL bake-off?
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
- 3,934
6
votes
2
answers
14k
views
Local database vs Amazon RDS
I am doing large INSERT into a local MySQL database
I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable
I expect this database to ...
- 163
6
votes
3
answers
6k
views
How do I determine how much data is being written per day through insert, update and delete operations?
The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
- 405
5
votes
2
answers
10k
views
When should I think about upgrading our RDS MySQL instance based on memory usage?
It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
- 353
3
votes
1
answer
3k
views
How does MariaDB handle ENUM types used in FOREIGN KEY constraints?
Off the heels of this question,
How does MySQL handle joins on ENUMs?
How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it ...
- 64.7k
151
votes
4
answers
256k
views
Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
user18530
5
votes
1
answer
10k
views
How do I create a point with an SRID in MySQL?
MySQL supports using ST_PointFromText to construct a point with an SRID,
ST_PointFromText(wkt[, srid [, options]])
Constructs a Point value using its WKT representation and SRID.
This ...
- 64.7k
6
votes
1
answer
2k
views
Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?
Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->
SELECT ...
- 64.7k
4
votes
2
answers
6k
views
How to `vacuumlo` an RDS PostgreSQL database?
As you may have noticed, as a fully managed data base as a service product, AWS Relational Data base Service (RDS) restricts the execution of userland commands.
I was staging a production database, ...
- 96
18
votes
2
answers
28k
views
Should I increase max_connections in AWS RDS t1-micro for MySQL?
I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
- 241
17
votes
1
answer
17k
views
How to temporarily disable foreign keys in Amazon RDS PostgreSQL?
I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
- 581
14
votes
4
answers
133k
views
Error 1236 - "Could not find first log file name in binary log index file"
Our setup:
Master: MariaDB 10.0.21
Slave: MariaDB 10.0.17
Replication was working fine until recently at which point the slave's DBs had to be restored from a dump. I performed all of the necessary ...
- 447
7
votes
1
answer
11k
views
How do I reduce the database size on AWS RDS MySQL?
I have an RDS MySQL database on Amazon Web Services.
I want to reduce the size of the database in the control panel in order to save money.
Q: How do I make it smaller? Their site says you have to ...
- 302
6
votes
3
answers
8k
views
MySQL INSERT INTO SELECT vs BULK INSERTs performance
Currently, I am trying to copy data from TABLE1 to TABLE2.
In terms of insertions performance, would it be the same or faster if I would to do
BULK INSERT manually (i.e BULK insert every 10K records ...
6
votes
1
answer
10k
views
MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows
We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE ...
- 163
5
votes
1
answer
3k
views
MariaDB 10.1.22 to use more RAM rather than CPU [closed]
Im using:
Server version: 10.1.22-MariaDB - Source distribution
server spec:
128GB RAM
CPU: E5-2600
we have around 80 tables with moderate data below 10k record per table, all of them are InnoDB. ...
- 177
4
votes
3
answers
5k
views
Extremely slow query on indexed column in Postgres
I'm getting a extremely slow query on an indexed column. Given the query
SELECT *
FROM orders
WHERE shop_id = 3828
ORDER BY updated_at desc
LIMIT 1
explain analyze returned:
QUERY PLAN
----...
- 143
2
votes
1
answer
2k
views
Postgres: Reclaiming Space
I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV ...
- 175
1
vote
1
answer
1k
views
How does MariaDB handle joins on ENUMs?
I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)?
...
- 64.7k
32
votes
2
answers
24k
views
Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance
At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need ...
- 1,095
15
votes
2
answers
10k
views
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
- 334
13
votes
1
answer
8k
views
Debug query on big table that is sometimes slow
I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
- 233
10
votes
7
answers
37k
views
TRUNCATE TABLE statement sometimes hangs
Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?
I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.
...
- 571
9
votes
2
answers
7k
views
Does MariaDB or MySQL implement the VALUES (expression) table value constructor?
I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?
SELECT *
FROM (...
- 64.7k
8
votes
3
answers
7k
views
How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?
I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
8
votes
2
answers
20k
views
MySQL - Autoincrement does not increment sequentally if last row was deleted
I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
- 257
7
votes
2
answers
21k
views
enabling BINARY LOG on AWS RDS MySQL
Context
I am trying to enable BINARY LOGs on a MySQL RDS instance.
Issue
I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error.
And using CALL mysql....
- 171
6
votes
1
answer
3k
views
AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?
I have a Multi-AZ AWS RDS instance running MYSQL 5.5
I've noticed that my write ops/sec are growing fairly rapidly, despite low (and sometimes zero) DB connections - see charts for Avg write ops/sec ...
- 163
6
votes
1
answer
4k
views
Does the Amazon RDS backup/snapshot service lock tables?
We have a database that is 100+GB. Most tables use the InnoDB engine.
A regular dump takes 1-2 hours.
If we moved to Amazon RDS, would this backup cause table locks?
Does Amazon use a simple ...
- 61
5
votes
1
answer
2k
views
Swap Usage but high Freeable memory
We have an AWS RDS with a MySQL database. Buffer Pool size is only 144GB out of the total available 244GB RAM. However, even at such a low RAM config, we see Swap Usage happening. Does anyone have any ...
- 223
5
votes
2
answers
20k
views
#1041 - Out of memory issue in mysql
I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I ...
- 581
4
votes
1
answer
5k
views
When to disable MySQL Query Cache
I have a high volume MariaDB 10 server thousands of databases of with over a million tables. Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other ...
- 356
4
votes
3
answers
875
views
Achieving "sequence number", rather than dateTime, for updates; postgres specifically
So a postgres database (specifically, an AmazonRDS instance fwiw).
It's easy enough to arrange things to get the last updated (or created) time for any record.
That information would be a datetime.
...
- 103
4
votes
3
answers
17k
views
The table is full - MariaDB
I am getting the following error whist trying to execute a long running query.
The table 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp#sql1664_349_19be' is full
The C drive (NTFS) on the server ...
- 143
4
votes
3
answers
10k
views
MariaDB: Missing FILE_CHECKPOINT
Running MariaDB v10.9.3 on Arch Linux. I've hosted a file storage cloud with it for years. Recently decided to create a database for another project, and am very new to MySQL interactions, so it's ...
- 111
3
votes
1
answer
6k
views
RDS Postgres oldest replication lag increases during inactivity even though Fivetran connector keeps synchronizing
I'm in the process of setting-up a datawarehouse using Fivetran as the ETL layer. One of the source databases is a AWS RDS Postgres instance.
I've configured the Postgres instance to perform Logical ...
- 201
3
votes
3
answers
1k
views
RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.
This is a problem for me because I would like to create ...
3
votes
1
answer
13k
views
How to start mysqld with read-only data directory?
I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
- 481
3
votes
0
answers
752
views
RDS MSSQL in-place upgrade how to update master db compatibility level when rdsa account is db owner
Background
My workplace infrastructure team ran a typical in-place upgrade of an RDS MSSQL instance to update the version from 2012 to 2017.
After the upgrade, while logged in as the "master ...
- 751
3
votes
4
answers
12k
views
Purge old records from mysql ibdata1 file
Is it possible to purge old records from an ibdata1 file without taking down the database attached to it?
Records that have been deleted from the database are still present when I scan ibdata1 for ...
3
votes
5
answers
19k
views
You do not have privileges to manipulate with the users!
Error message:
You do not have privileges to manipulate with the users!
Appears in PhpMyAdmin using Mariadb 10.3.7 on MacOs High Sierra installation from Homebrew
I'm logged in as root
installed ...
- 133
3
votes
1
answer
20k
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 ...
- 151
3
votes
1
answer
4k
views
mariadb Unknown system variable 'server_uuid'
Im trying to setup replication between mariadb(10.1.31-MariaDB - MASTER) and mysql(5.7.12-log - SLAVE). After executed the start slave, I got the below error.
Fatal error: The slave I/O thread ...
- 1,960
2
votes
2
answers
3k
views
Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB? [closed]
Title says it all. However i have to enter at least 30 characters.
- 131
2
votes
1
answer
3k
views
Synchronizing a RDS instance with another RDS instance
I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do ...
- 143
2
votes
2
answers
5k
views
json functions: double quotes added when going through a variable
Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go ...
- 23
2
votes
1
answer
6k
views
'kill <thread id>' gives ERROR 1095 (HY000): You are not owner of thread ... even though I am
I did an ALTER TABLE on a huge table on a MariaDB Galera Cluster 10.2, but after a while of waiting I changed my mind and tried to kill the connection. However, MariaDB said ERROR 1095 (HY000): You ...
- 6,479
1
vote
1
answer
2k
views
MySQL/MariaDB stored procedure inserts even with syntax error
createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail?
DELIMITER $$
USE `MobiFit_Dev` $$
DROP PROCEDURE IF EXISTS `...
- 171
1
vote
1
answer
4k
views
MySQL optimization
I'm trying to optimize a MySQL Server to be able to serve as many connections as it can.
The server is in AmazonAWS RDS and has currently the following resources:
--7.5 GB memory, 4 ECUs (2 ...
1
vote
1
answer
3k
views
Point in time recovery with mariabackup
I have a full backup dir and a bunch of hourly (incremental) backup directories taken with mariabackup, which is a port of the XtraBackup.
How does one restore the MariaDB up to a specific minute (...
- 473