Questions tagged [database-tuning]
Performance tuning done by adjusting database parameters or physical layout of the database.
137 questions
43
votes
7
answers
48k
views
Is it better to store images in a BLOB or just the URL? [duplicate]
Possible Duplicate:
Files - in the database or not?
I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch of ...
- 1,874
33
votes
3
answers
108k
views
MySQL creates temporary tables on disk. How do I stop it?
We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
- 463
32
votes
2
answers
3k
views
Where can I find database tuning exercises for learning?
As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.
I'm looking ...
- 421
30
votes
5
answers
86k
views
How can I tell WHY an insert on a certain table is slow?
I know that an INSERT on a SQL table can be slow for any number of reasons:
Existence of INSERT TRIGGERs on the table
Lots of enforced constraints that have to be checked (usually foreign keys)
Page ...
- 10k
19
votes
2
answers
37k
views
Designing a user authenication (Roles & Rights) module
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
- 315
15
votes
3
answers
31k
views
MySQL table_cache and Opened_tables
I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
- 281
13
votes
1
answer
22k
views
Can I safely kill OPTIMIZE TABLE on an InnoDB table?
MySQL's documentation for kill warns:
Warning
Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to ...
- 279
13
votes
2
answers
2k
views
SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?
In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
- 583
12
votes
1
answer
43k
views
Optimize PostgreSQL for a lot of INSERTS and bytea updates
What we have (software):
PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf)
Windows 7 64 bit
Hardware:
Intel Core i7-3770 3.9 Ghz
32 Gb RAM
WDC WD10EZRX-00L4HBAta Drive (1000Gb, ...
- 557
12
votes
3
answers
2k
views
Executing same request from C# VS SSMS give different execution time
I have a request like this one
SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
[...
- 1,062
12
votes
2
answers
3k
views
SQL Server: Has anyone used the large-page allocations tuning option?
Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any ...
- 5,982
10
votes
2
answers
25k
views
Could not continue scan with NOLOCK due to data movement
We run SQL Server 2000 and we get a few of these errors every night.
Could not continue scan with NOLOCK due to data movement
The query that throws this error is a large complex query that joins ...
- 385
9
votes
1
answer
6k
views
How can a fill factor of 0 or 100 be the same?
As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth.
I can't correlate how fill factor of 0 and 100 can be the same!
Am I ...
- 97
8
votes
1
answer
2k
views
Optimizing PostgreSQL for transient data
I have several tables with 100-300 columns of integer types each, that hold highly volatile data. The datasets are keyed by one or two primary keys, and when refresh occurs, the whole dataset is ...
- 183
8
votes
1
answer
11k
views
Why does SQL Server run a subquery for each row of the table it's qualifying?
This query runs in ~21 seconds (execution plan):
select
a.month
, count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month
When the subquery ...
- 464
7
votes
5
answers
50k
views
How to determine why a MySQL server has become slow and requests sometimes time out?
I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having.
Most of the time, responses are returned quickly from the ...
- 201
7
votes
1
answer
9k
views
PostgreSQL doesn't use all the RAM it could
I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config:
# <snip> the default config ...
- 173
7
votes
1
answer
14k
views
How do you increase MySQL performance using current server resources?
I have a MySQL database and some of my queries became slow. Query time is not stable. Most of the queries are fast, but some of them (may be that reads and returns less data) take long time.
I know ...
- 465
7
votes
2
answers
8k
views
I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer
When trying to use the SQL Tuning Advisor from SQL Developer I get this error.
Tuning advisor was working some days before.
- 12.3k
6
votes
2
answers
4k
views
PostgreSQL performance degradation over time on a write intensive db
I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
- 191
6
votes
2
answers
651
views
Tuning Postgres for Single connection use? Or is postgres wrong tool?
Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions?
I'm a social ...
- 213
5
votes
1
answer
3k
views
Does creating View cause an overhead to DB?
As my subject suggests, I would like to know does creating views cause an overhead to DB in terms of space, storage, performance?
- 179
5
votes
1
answer
1k
views
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
- 163
5
votes
1
answer
1k
views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
- 153
5
votes
2
answers
4k
views
Creating partitions on a production database
I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.
I have two tables that involve a ...
- 1,425
5
votes
1
answer
192
views
How to tell if database tuning is fruitful (PostgreSQL)
As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the ...
- 988
5
votes
3
answers
7k
views
Dropped tables are not going to Recycle Bin
To start, I am a student and just getting my feet wet with Oracle.
Quick history
Virtual Box XP running Oracle express with SQL Developer.
Created a user and tablespace for myself. (Key here is I'...
- 123
5
votes
1
answer
2k
views
Postgres 4x Slower Than It Was
Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why.
We have two machines with identical hardware (let's call them A and B):
Intel(R) Xeon(R) CPU E5-4640 0 @ 2....
- 297
5
votes
1
answer
842
views
Tuning queries and indexes using index analysis tool
I've been pointed to this script from several places which apparently helps with designing indexes in Microsoft SQL Server: http://indexanalysis.codeplex.com/
The problem is I'm using Sybase ASA 11, ...
- 151
4
votes
4
answers
429
views
Does DBA need (a read) access to virtualization infrastructure? And why?
I am in charge of monitoring, troubleshooting, tuning and optimizing a few dozens production SQL Servers 2008 R2/2012 in my company that are being run inside VMWare virtual machines.
I've requested ...
4
votes
1
answer
1k
views
Find least recently used mysql table index
I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.
While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
- 143
4
votes
1
answer
829
views
Is there a way to make SQL Server's Database Tuning Advisor use more than one core?
I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.
For the "consuming workload" step, it appears to use all ...
- 143
4
votes
1
answer
546
views
Proper Database Partitioning
I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
- 440
4
votes
2
answers
3k
views
Connection Pooling for PHP based website using Oracle 11g database
I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I ...
- 143
4
votes
2
answers
812
views
Tuning advisor with Extended events?
With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...).
Now, with SQL 2012 and Extended ...
- 355
4
votes
2
answers
13k
views
Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?
My Hardware Spec
Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 ...
- 141
4
votes
2
answers
3k
views
Website is too slow when number of records increases?
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
- 173
4
votes
1
answer
547
views
NuoDB with Amazon S3
I think about developing my new mobile and web app against NuoDB. I believe in their technology. The thing that I care the most is backup and restore in case of data corruption and read / write speed ...
- 265
4
votes
2
answers
697
views
Tuning Mysql and Monitoring
I would like to know if anybody can point me towards a tool/way for MySQL database tuning.
Actually the tuning of my.cnf is like below:
My database engine is MyISAM | Mysql 5.1.73
key_buffer = 500M
...
4
votes
1
answer
1k
views
Need help with long running query
UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.
Thanks for taking the time to read this post - it'...
- 387
4
votes
2
answers
3k
views
Could running ANALYZE on all databases on large InnoDB tables show some performance increase?
So we have an old database that was fragmented until we ran OPTIMIZE on all tables.
The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity.
There are so many ...
- 43
4
votes
2
answers
2k
views
Slow SSRS Report in production
I have an SSRS report which gets its data by firing a series of stored procedures.
Now the report is timing out big time when run in production, yet when I pull down the prod database and restore to ...
- 215
3
votes
2
answers
2k
views
What are the options of reducing tempdb latency?
Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed.
All of them are running inside virtual machines. One SQL Server per a ...
3
votes
2
answers
370
views
Is it important to release memory back to the server?
I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server ...
- 75
3
votes
2
answers
22k
views
SGA_MAX_SIZE and SGA_TARGET
We recently increased sga_max_size from 2g to 4g but didn't increase sga_target. Is it OK not to increase sga_target? The reason we wanted to increase sga is to reduce wait time on sequential reads in ...
- 269
3
votes
1
answer
1k
views
what does SHOW /*!50000 GLOBAL */ STATUS do ?
What is the difference between
SHOW /*!50000 GLOBAL */ STATUS
and
SHOW GLOBAL STATUS
I saw that in few scripts and tuning presentations the first command is being used. Is there a difference, ...
- 395
3
votes
1
answer
1k
views
Use of integer instead of interval (of one type)
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.
...
- 180
3
votes
4
answers
10k
views
Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?
I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it:
Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
- 131
3
votes
1
answer
338
views
Maintenance plan using external tool to execute query and stored procedures giving off no results
My situation is as follows:
I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers.
The stored procedures are from http://ola.hallengren.com/ ...
- 2,631
3
votes
3
answers
6k
views
AWR Report - Tuning Database
We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait ...
- 269