All Questions
7,398 questions
0
votes
1
answer
2k
views
MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin
I am a developer so was hoping someone with DBA experience could help me out here.
I am using a MySQL database (MYSQL 5.6).
I came across a situation where a particular column stores Ids generated ...
CommunityBot
- 1
0
votes
2
answers
494
views
Give third-party access to AWS hosted MySQL
I have a production MySQL database hosted on AWS for an eCommerce store I work with. Employees in the firm are requesting access to our live SQL database to use with BI tools like Tableau/Qlikview. ...
CommunityBot
- 1
3
votes
5
answers
2k
views
Mysql: 1000 select queries vs 1 query ( select .. in )
I have just read about N+1 queries problem. So I have the doubt about the performance of 2 ways.
The way 1: I have 1000 queries like that:
select * from topic where user_id = 1
select * from topic ...
- 3,622
0
votes
2
answers
919
views
Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables
I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned.
About the application: It's Nimsoft suite, so the SQL queries are not ...
CommunityBot
- 1
0
votes
1
answer
350
views
Best way to store HTML dropdown options
I'm developing a web site for a model agency. I have a "model" table with the details about each model (this is just an example):
CREATE TABLE model {
model_id INT PRIMARY KEY,
name VARCHAR(...
CommunityBot
- 1
4
votes
1
answer
5k
views
PostgreSQL Enum : Search on ENUM type
Defined an ENUM
CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');
Using this as a type in a table
CREATE TABLE if not exists transaction(
id BIGSERIAL NOT NULL PRIMARY KEY ,
amount NUMERIC(35,...
CommunityBot
- 1
0
votes
1
answer
1k
views
mysqldump from AWS RDS slowing down
Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using -
mysqldump -hxx -u xx -p --extended-insert --single-transaction --max_allowed_packet=1G db > ...
CommunityBot
- 1
1
vote
1
answer
452
views
Query Tuning and Index Tuning - SQL Server
I've been asked to improve the performance of the following query.
I can't amend any existing Indexes but I can create new ones.
The table structures can be found in this SQL Fiddle
The row sizes of ...
CommunityBot
- 1
2
votes
1
answer
430
views
Optimizing cloud server (php and mysql)
I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
CommunityBot
- 1
4
votes
1
answer
2k
views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
CommunityBot
- 1
0
votes
1
answer
119
views
How to reduce running time? Execution time exceeded
Excuse my grammar Im dyslexic:
The main issue is that after calculating all the lags and flows at the ISIN level, when I try to do the final grouping by bucket, asset class, and date, I'm forced to re-...
- 1,762
1
vote
1
answer
1k
views
MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?
I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).
My solution (do not work) was to add a AFTER TRIGGER ...
CommunityBot
- 1
1
vote
1
answer
517
views
Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)
Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.
Let's assume the following statement update three rows:
UPDATE table_name ...
CommunityBot
- 1
1
vote
1
answer
664
views
Aurora MySQL: how do I keep binlogs around after reboot?
We have a request to enable binlogs on an Aurora MySQL instance and to keep those logs around for a certain amount of time. To test, we enabled binlogging with binlog_format=ROW and set our retention ...
CommunityBot
- 1
0
votes
0
answers
8
views
In which sections of AWR and Statspack can we identify how much CPU the database usually consumes?
We need to migrate a database, but we need to evaluate the possibility of reducing the number of resources (CPU) to decrease the number of licenses. In which sections of AWR and Statspack can we ...
- 801
0
votes
1
answer
1k
views
High CPU usage in AWS RDS
When I visit the woocommerce orders page in WordPress, MySQL RDS CPU usage goes to 100% but the website is working perfectly fine.
In 'active sessions' section, "wait/io/tables/sql/handler" ...
CommunityBot
- 1
0
votes
1
answer
732
views
Can I run commands from aws_s3 extensions as non-root on PostgreSQL?
I'm trying to copy data from S3 to PostgreSQL (Amazon AWS RDS). I've set up the following:
CREATE EXTENSION aws_s3 cascade schema reporting;
CREATE DATABASE my_database;
\connect my_database; -- ...
CommunityBot
- 1
2
votes
1
answer
529
views
Aggregate data with multi-schema tenant architecture
We have an application that uses the tenant schema architecture, one database, has multiple schema per "tenant", each tenant is a replica with same tables etc.
Sometimes we don't know the ...
CommunityBot
- 1
0
votes
0
answers
30
views
Why am I getting an INDEX SKIP SCAN?
Scenario is as follows:
Table T is created as of
CREATE TABLE T
(
f_a ...
,f_b ...
,f_c ...
,ctv NUMBER(*,0) NOT NULL
,ffc DATE NOT NULL
,v NUMBER(1,0)
,uc NUMBER(1,...
- 123
0
votes
1
answer
320
views
Accelerated Database Recovery SQL Server
I have ADR enabled on my production database running on SQL Server 2019. In the SQL Server logs , it shows the messages:
[VersionCleaner][DbId:6][sweepOfSelectivePages]: Cleanup of sidelist
aborted ...
CommunityBot
- 1
0
votes
2
answers
762
views
What is the best way to measure INDEX impact on production?
We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
CommunityBot
- 1
2
votes
1
answer
33
views
Innodb - Can you safely ignore the warning "Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size"
We get this warning in our online shop
report.WARNING: Memory size allocated for the temporary table is more
than 20% of innodb_buffer_pool_size. Please update
innodb_buffer_pool_size or decrease ...
- 16k
0
votes
1
answer
419
views
Read Replica block queries postgres
I've RDS PostgreSQL DB with read-replica. Also, have one script which frequently checks the blocking query but I faced one issue yesterday where the blocking query was a startup(backend_type)process ...
CommunityBot
- 1
0
votes
1
answer
31
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g., name, email, phone_number, address, etc.)
Work-...
- 79.4k
1
vote
1
answer
448
views
Cassandra query response time degradation
I am experimenting with Cassandra on 4 sites with 2 nodes on each one. Have small network latency between them. I always query the data in DC where I inserted it. I see very strange behavior: when I ...
CommunityBot
- 1
0
votes
1
answer
690
views
Postgres index not being used
Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
CommunityBot
- 1
1
vote
1
answer
324
views
RDS import from SQL backup
I'm migrating a SQL Server 2008 database from a corporate data center to an RDS instance on AWS.
So far I have performed a native backup which gave me a .bak file
Is it possible to simply upload this ...
CommunityBot
- 1
0
votes
1
answer
39
views
Postgres slow query on large tables when doing a search radius search and joining on large tables with indices
I've been experiencing extremely slow queries despite having indices (hopefully the correct indices) on my tables. What I'm doing is searching for providers within a search radius as well as their ...
- 41.3k
0
votes
1
answer
40
views
Does setting a default value for Alter Table in Postgres make the query significantly slower?
We had an incident very recently that caused our service to be unavailable for some minutes.
Here is the liquibase script that led to the incident:
<addColumn tableName="registration">
...
- 119
1
vote
1
answer
3k
views
AWS RDS mysql deadlock
Everything in our production environment was working well. Yesterday, suddenly out of no where we were getting "lock wait" on any transaction related to a specific row. Same transaction on another row ...
CommunityBot
- 1
1
vote
1
answer
587
views
MySQL Database on RDS with memory issues
I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help?
My database has 120GB, including indexes.
Can anyone help me out to ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)
I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
CommunityBot
- 1
0
votes
2
answers
974
views
How to improve performance of frequently updated table in mysql?
my table structure::
CREATE TABLE `gps_device_data`(
`imei_no` bigint(15) NOT NULL DEFAULT '0',
`ioelementpriority` int(1) DEFAULT NULL,
`longitude` int(15) DEFAULT NULL,
`latitude` int(15) ...
CommunityBot
- 1
3
votes
1
answer
442
views
How to deal with seldom used big database and postgresql?
I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries.
My problem is :
This database is ...
CommunityBot
- 1
9
votes
2
answers
2k
views
Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
...
CommunityBot
- 1
0
votes
2
answers
761
views
Fastest way to update column value
I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'?
Facts:
This is an Oracle MySQL 8.0.20 ...
CommunityBot
- 1
1
vote
1
answer
3k
views
Getting by list of ids: `unnest() JOIN` vs `= ANY()`
Here I have an array of 42 ids from a table of 800,000 and I want to lookup the rows with those ids.
I know of two ways to do this (besides creating a lot of parameters -- that one is harder to write ...
CommunityBot
- 1
0
votes
3
answers
128
views
OS memory usage is high, but MS Sql server has plenty of free memory
On sql server 2019, I occasionally get "Physical Memory pressure" alert.
When I check the memory usage, I get two different cases.
There is a large memory in free pages.
Can anyone explain ...
- 395
0
votes
1
answer
2k
views
Does a read replica help if the problem are heavy queries?
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
CommunityBot
- 1
0
votes
1
answer
673
views
Parameter innodb_flush_log_at_trx_commit not available in AWS Aurora 5.7
I'm trying to modify the parameter innodb_flush_log_at_trx_commit on a RDS Aurora 5.7 instance from the AWS RDS Web Console and when I look through the parameters in the database I can not find it.
...
CommunityBot
- 1
0
votes
1
answer
2k
views
Database ExecuteSQL(MySQL client run out of memory)
I've an online game
the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec
the problem the memory usage still increasing i dun know why ??
i mean if we suppose that ...
CommunityBot
- 1
0
votes
1
answer
32
views
Where can I find more detailed MySQL deadlock logs?
We have a MySQL database in AWS RDS that was migrated recently from 5.7 to 8. One of our microservices running in ECS executes bootstrapping code at launch to iterate through a large list of table ...
- 1
7
votes
1
answer
2k
views
How to Choose Between VALUES and SELECT for INSERT?
This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
...
- 182k
0
votes
0
answers
11
views
Can you downgrade provisioned iops on gp3 for postgresql RDS?
Doing an occasional analysis on a postgreSQL RDS instance with gp3 storage.
Running out of provisioned IOPS.
Is it possible to temporarily raise the provisioned IOPS limit? e.g. just for a day or ...
- 347
0
votes
1
answer
1k
views
Amazon RDS change collate for mysql database in production without downtime
I saw a solution like this below:
create a new table like your source table.
alter that new table the way you want.
insert your data into the new table.
create indexes etc. as needed on the new table....
CommunityBot
- 1
2
votes
1
answer
2k
views
MariaDB Inner join is slow
Here is my SQL (with explain):
EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
gbi.vendor = kred.vendor;
Giving below the outcome:
Now, the select SQL takes ~10 seconds to produce the ...
CommunityBot
- 1
0
votes
1
answer
58
views
Long running waits- how to check/prove if the root case is at the SQL Side
Some of my users complain about the poor performance of the application developed/supported by an external company. The app uses SQL Server 2019 Enterprise Edition for a database engine.
I can see in ...
- 18k
2
votes
1
answer
743
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
CommunityBot
- 1
38
votes
3
answers
9k
views
Why does SQL Server use a better execution plan when I inline the variable?
I have a SQL query that I am trying to optimize:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @...
9
votes
1
answer
3k
views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers ...
