Performance tuning done by adjusting database parameters or physical layout of the database.
23
votes
2answers
769 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 ...
22
votes
7answers
4k 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 ...
13
votes
4answers
2k 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 ...
10
votes
3answers
435 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],
...
10
votes
2answers
652 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 ...
9
votes
3answers
7k 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 ...
7
votes
2answers
6k 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 ...
5
votes
1answer
56 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 ...
5
votes
1answer
3k 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 ...
5
votes
2answers
952 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.
5
votes
1answer
248 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, ...
4
votes
1answer
309 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, ...
4
votes
2answers
415 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 ...
4
votes
1answer
132 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 ...
4
votes
1answer
251 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 ...
4
votes
3answers
820 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 ...
4
votes
2answers
346 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 ...
3
votes
2answers
218 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 ...
3
votes
1answer
74 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 ...
3
votes
2answers
568 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 ...
3
votes
1answer
447 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 ...
3
votes
3answers
4k 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 ...
3
votes
2answers
1k 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 ...
3
votes
1answer
349 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 ...
3
votes
2answers
529 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 ...
3
votes
1answer
236 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 - ...
3
votes
1answer
79 views
What are some key configuration settings for using an InnoDb table for session storage?
I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper ...
3
votes
1answer
85 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 ...
2
votes
2answers
279 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 ...
2
votes
1answer
113 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 ...
2
votes
2answers
6k views
SGA_MAX_SIZE and SGA_TARGET
We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our ...
2
votes
1answer
71 views
Slow Query Gets Even Slower After Indexing
I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:
select * from my_table
where
...
2
votes
1answer
169 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, ...
2
votes
1answer
157 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
2
votes
1answer
837 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 ...
1
vote
2answers
444 views
Restore data from remote server using “.sql” file
I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...
1
vote
1answer
104 views
Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?
I have this existing table for IP storage:
CREATE TABLE [dbo].[IPAddresses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IPv4Address] [varchar](15) NULL,
[IPv6Address] [varchar](45) NULL,
...
1
vote
1answer
470 views
Planning simple CMS: How much relation?
I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
1
vote
1answer
174 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 ...
1
vote
1answer
184 views
Database Tuning Advisor can't find tables across two databases
I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
1
vote
2answers
446 views
Performance tuning OLTP query
I have following sqls executed million times a day, single execution isnt an issue but executing million times causing some performance issues..Thanks in advance for your help!
NOTE: data can change ...
1
vote
0answers
49 views
Configuring PostgreSQL for read performance
Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow.
The primary key (constraint) structure is ...
1
vote
1answer
80 views
sp_setapprole taking 100 - 200 ms [closed]
SQL Server 2008 SP1 here.
We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application.
The developer says that in his setup, ...
1
vote
0answers
922 views
Could Oracle ORA-08103 'object no longer exists' error be caused by size of undo_retention parameter?
Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time. Logs were showing ORA-08103 'object no longer exists'.
ORA-12012: error on auto ...
1
vote
0answers
275 views
SQL Server Database Engine Tuning Advisor vs DMV queries
What is the best way to tune the indexes on my production database? I normally use the following query :
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
...
0
votes
3answers
315 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 ...
0
votes
3answers
200 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 ...
0
votes
2answers
659 views
How fast should I expect to load data on a high performance computer?
I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
0
votes
0answers
33 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 ...
0
votes
0answers
51 views
PostgreSQL can't start after tuning some options
I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this
...
