Questions tagged [database-tuning]
Performance tuning done by adjusting database parameters or physical layout of the database.
137 questions
1
vote
0
answers
404
views
MySQL suddenly running very very slowly
I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs.
Everything was flying along until this morning when I noticed MySQL down.
I tried to restart MySQL and first got the ...
- 11
0
votes
3
answers
1k
views
SQL Server : primary keys advice to my whitepaper needed
I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should read....
- 3,427
0
votes
3
answers
763
views
Custom SQL Server Computer Build
I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong ...
- 117
0
votes
1
answer
413
views
Table and Primary Key sizes in Oracle
I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table.
To be clear, my statistics are the following (approx.):
NAME ...
- 247
0
votes
1
answer
73
views
multiple indexing issue [closed]
I used Database Engine tuning advisor for indexing a table and when I am checking indexing , it showing multiple indexing on same table . as shown in
fig
I am wondering for these question as ...
- 185
0
votes
1
answer
862
views
What happens when the buffer pool size is insufficient in mysql
Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
- 45
0
votes
2
answers
2k
views
SQL Query, how to know if is resource intensive? I/O
I am not a SQL guru, but I am troubleshooting an issue with our marketing platform as its become slugish and slow, I've requested the top 50 heavy/resource intensive queries executed on our sql server ...
- 357
0
votes
1
answer
623
views
What is the purpose of Database Tuning? [closed]
This sounds like a stupid question but when posed with it on an exam yesterday, I wasn't entirely sure how to answer...I mean I know you have to put indexes on certain fields to speed up slow queries, ...
- 1,722
0
votes
1
answer
2k
views
SQL Server 2012 Tuning process exited unexpectedly. DTAEngine
I am using Windows Server 2012 x64 and SQL Server 2012.
I am running the Database Tuning Advisor with these settings :
Advisor plan cache.
Database workload master.
Check my database all the tables.
...
- 103
0
votes
1
answer
2k
views
How to measure a perfomance of SQL Server database? [closed]
I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics.
So, I need to measure "performance" of the RDBMS ...
- 1,392
0
votes
1
answer
130
views
Indexes and Optimizations for Logging System
I'm designing tables for a new log system and am having trouble figuring out what indexes I'll need (I'm a developer, not a DBA), and am also open to any suggestions/criticisms/etc. for making the ...
- 267
0
votes
1
answer
2k
views
AWS RDS Postgresql and max_worker_processes
AWS RDS Postgresql 12.10
According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql,
max_worker_processes Set this to ...
- 658
0
votes
1
answer
77
views
Data modeling with the goal to get the best performance in Oracle and SQL Server [closed]
I have a question about how I can model my stock database in order to get the best performance possible.
In SQL Server or in the Oracle, each update executed generates a little lock.
I'd like to ...
0
votes
2
answers
1k
views
reduce number of innodb pages read by a query
In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of ...
- 33
0
votes
1
answer
67
views
Feasibility of Automated and Continuous Performance Tuning in the future [closed]
First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, create/...
- 171
0
votes
1
answer
235
views
Creating a flexible table containing prices and periodes
I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price.
Room Type | 01 | 02 | 03 | 04 | 05 | ... | 31 |
-------------...
- 1
0
votes
1
answer
62
views
Big Size Database and increasing number of users [closed]
Me and my colleagues are using quite big size normalized database.
Basically it's used for warehouse management and daily minimum 100 of the user perform data manipulation operations.
80% of the ...
- 101
0
votes
1
answer
70
views
Effects of monolothic databases on performance and security?
Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems.
As a developer I don't see the design ...
- 115
0
votes
1
answer
130
views
Access and filter in oracle plan
I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them.
My question is what are the reasons that can cause that(all ...
- 533
0
votes
1
answer
6k
views
Oracle 11g Enterprise downgrade to Standard One
I'm checking possibilty of downgrade Oracle 11g from Enterpise Edition to Standard One edition. I've cross-checked edition comparison with
select name from dba_feature_usage_statistics where ...
- 103
0
votes
1
answer
66
views
Retrieve list of matched words in PostgreSQL
I'm new to PostgreSQL and really more than your more basic queries/inserts.
I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in ...
- 115
0
votes
1
answer
71
views
MySQL 8.0.39 - MySqlException (0x80004005): Out of memory;
We have a Windows Server 2022 with 56Gb RAM. This server is dedicated to MySQL DB and during idle it has ~50.9Gb available memory. But while bulk inserting rows (200k rows) into a table from an ...
- 1
0
votes
1
answer
206
views
Different performance of SQL Server instances on same server
I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). ...
- 63
0
votes
0
answers
1k
views
How to correctly set wait_timeout for MySql server
I have a dedicated server to run a WordPress website.
I use Nignx + php fpm + MySql 8.0.
Php processes are set to run for a maximum of 60 seconds, and mysql is used just to run this website.
I checked ...
- 1
0
votes
1
answer
76
views
What dataset(s) is Marcus Winand using in his book "SQL Performance Explained"?
I am currently reading SQL Perfrmance Explained by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand ...
- 103
0
votes
2
answers
472
views
Optimization: Increase Innodb_pool_size useful?
We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.
I have kept the default setup of innodb_pool_size to be half the memory.
What I see is that almost 30Gb of ...
- 103
0
votes
0
answers
98
views
Tuning SQL Server 2012 [closed]
We migrated from Oracle to SQL Server, the new server has good resources and should burn through data.
When reading OS MasterMap data it consumes over 50% of cpu and takes several minutes to load.
I ...
0
votes
1
answer
423
views
opened_files large, open_files tiny
on my mariadb server OPENED_FILES is currently 2,775,453 and OPEN_FILES never seems to exceed 65. I understand that opened_files is cumulative and is meaningless without a time period. ...
0
votes
0
answers
79
views
Postgresql behaviour between diferent stages
I have three Postgresql instances (Postgresql clusters) in three diferent stages.
The instances are the sames with the same datas and the same parameters.
But i do not have the same behaviour between ...
- 361
0
votes
2
answers
1k
views
High "Pending normal aio writes"
During daily batch operations (mass delete/upsert operations), regular throughput is sometimes affected, e.g. operations such as a single lookup on a PK or a small range (7 rows examined, 1 row ...
- 199
0
votes
1
answer
175
views
Configuring Transactional Replication for an OLTP Database
We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader ...
user129291
0
votes
1
answer
182
views
I need to reduce the size of Database file -- I am confused between DBCC Shrinkfile and Data compression options
I need to reduce the size of Database file. I am confused between DBCC Shrinkfile and Data compression option.
DBCC shrinkfile will reduce the actual database file size as far as I know but Data ...
- 181
0
votes
1
answer
2k
views
MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service [duplicate]
I checked and found that InnoDB pluginis active.
By default, Innodb_log_file_size=5MB. As per the MySQL Documentation,
its ideal values range from 1MB to 1/N-th of the size of the buffer pool, where ...
- 2,061
0
votes
1
answer
652
views
After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory
We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM (...
- 211
0
votes
1
answer
1k
views
MySQL uses more than 500% of the CPU
Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU.
I'm getting a lot of these errors:
1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file ...
- 135
-2
votes
1
answer
237
views
when to use technology like hadoop?
I understand that MySQL and others support large enough data for storing and retrieving. I am also aware of the open source projects such as hadoop and mapreduce etc.. (only their purpose and what ...
- 149
-2
votes
1
answer
516
views
Can min_wal_size bigger than max_wal_size?
is it possible if min_wal_size bigger than max_wal_size ? What happen if it does ? What are the circumstances when min_wal is bigger than max_wal ?
I am using PG 12.
Thanks
- 327