Performance tuning done by adjusting database parameters or physical layout of the database.

learn more… | top users | synonyms

26
votes
7answers
11k 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 ...
25
votes
2answers
1k 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 ...
14
votes
4answers
6k 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
671 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
3answers
12k 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 ...
10
votes
2answers
1k 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 ...
7
votes
2answers
15k 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 ...
7
votes
2answers
12k 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 ...
6
votes
1answer
490 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 ...
5
votes
1answer
290 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 ...
5
votes
1answer
601 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, ...
5
votes
2answers
3k 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 ...
5
votes
1answer
102 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
7k 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
2k 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
421 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
2answers
1k 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
280 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
319 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
2answers
6k 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 ...
4
votes
3answers
2k 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
1answer
3k 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 ...
4
votes
2answers
1k 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 ...
4
votes
2answers
734 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 ...
3
votes
5answers
195 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 ...
3
votes
2answers
3k 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 ...
3
votes
1answer
320 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
288 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
106 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. ...
3
votes
1answer
1k 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 ...
3
votes
3answers
5k 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
2k 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
2answers
183 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 ...
3
votes
1answer
442 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
808 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
427 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
133 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 ...
2
votes
2answers
751 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
184 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?
2
votes
1answer
236 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
10k 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
2answers
435 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 ...
2
votes
1answer
292 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
2
votes
1answer
105 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
300 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
69 views

What are these files in MongoDB?

I was looking into my server and I found this: [root@host ~]# cd /var/lib/mongo/journal/ [root@host journal]# ls -lh total 3.1G -rw-------. 1 mongod mongod 1.0G Apr 7 01:18 j._0 -rw-------. 1 mongod ...
2
votes
1answer
316 views

Getting the most out of SQL Server with a million rows a day [closed]

I have a windows service that is inserting 1000 rows at 1 minute intervals to a SQL Server 2008 Standard database table that looks like: id, datetime, key_id, value key_id is a foreign key to ...
2
votes
1answer
539 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
2answers
92 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 ...
1
vote
5answers
789 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 ...