Stack Overflow
Stack Overflow

Questions tagged [database-tuning]

deals with optimizing the performance of a database. It can be about file design, and selection of the DBMS, OS and platform.

Database tuning is about maximizing the performance of a database. It can include hardware, software, file system, OS and DBMS selection. See the wikipedia article for more.

158 questions
249
votes
2 answers

Optimise PostgreSQL for fast testing

I am switching to PostgreSQL from SQLite for a typical Rails application. The problem is that running specs became slow with PG. On SQLite it took ~34 seconds, on PG it's ~76 seconds which is more than 2x slower. So now I want to apply some…
Dmytrii Nagirniak
  • 23,696
  • 13
  • 75
  • 130
239
votes
9 answers

How can I time a code segment for testing performance with Pythons timeit?

I've a python script which works just as it should, but I need to write the execution time. I've googled that I should use timeit but I can't seem to get it to work. My Python script looks like this: import sys import getopt import timeit import…
Emil Devantie Brockdorff
  • 4,724
  • 12
  • 59
  • 76
102
votes
1 answer

How to configure MongoDB Java driver MongoOptions for production use?

I've been searching the web looking for best practices for configuring MongoOptions for the MongoDB Java driver and I haven't come up with much other than the API. This search started after I ran into the "com.mongodb.DBPortPool$SemaphoresOut: Out…
Dan Polites
  • 6,750
  • 10
  • 50
  • 56
29
votes
5 answers

How to profile MySQL

How do I profile a MySQL database. I want to see all the SQL being run against a database. I know you can do this: set profiling=1; Run your slow query (eg SELECT * FROM messages WHERE fromaddress='xxx'; SHOW PROFILES; But this seem to only apply…
Dan
  • 29,100
  • 43
  • 148
  • 207
27
votes
1 answer

SQL Server audit logout creates huge number of reads

I'm using SQL Server Profiler to figure out what process are consuming SQL process and I found that the event class Audit Logout is causing a huge number of reads and consume cpu process. Is it normal? Or do I have something wrong in the SQL Server…
AutoCiudad
  • 735
  • 1
  • 10
  • 14
13
votes
1 answer

Speed up Oracle Text indexing or let the indexer work only on low load times

We're using a Oracle Text CTXSYS.CONTEXT index to index about half a million rows containing metainformation. The information is spread over two tables that are combined by a procedure that the indexer calls at runtime (functional index). When I run…
Stefan
  • 3,382
  • 4
  • 23
  • 27
10
votes
2 answers

Optimal JVM settings for Cassandra

I have a 4 node cluster with 16 core CPU and 100 GB RAM on each box (2 nodes on each rack). As of now, all are running with default JVM settings of Cassandra (v2.1.4). With this setting, each node uses 13GB RAM and 30% CPU. It is a write heavy…
dvl
  • 741
  • 2
  • 8
  • 19
9
votes
3 answers

Avoid sort operator in index plan

I have two tables [LogTable] and [LogTable_Cross]. Below is the schema and script to populate them: --Main Table CREATE TABLE [dbo].[LogTable] ( [LogID] [int] NOT NULL IDENTITY(1, 1) , [DateSent] [datetime]…
Raghav
  • 8,772
  • 6
  • 82
  • 106
9
votes
1 answer

Amazon RDS (PostgreSQL): Optimize memory usage

There is something i did not really understand with Amazon RDS (the PostrgreSQL version). Some queries takes a lot of time to show their results. I have set all relevant indexes (as shown with EXPLAIN). So I think it's not due to my schema design. I…
Adagyo
  • 422
  • 1
  • 4
  • 16
7
votes
4 answers

Can "exec sp_reset_connection" be removed from SQL Server Profiler trace data before running the tuning advisor?

Based on information I have read about the "exec sp_reset_connection" which appears in SQL Server Profiler trace data when connection pooling exists, it seems safe--or logical, rather--to remove/exclude it from trace data to be considered by the…
Nate Cook
  • 8,395
  • 5
  • 46
  • 37
7
votes
2 answers

How to view history of queries (all OR over a long period) performed on database which is hosted on Azure?

For a database hosted on Azure, i can view the recent history of the queries performed on it. This is through the Azure portal > Database > Manage > Administration > Query Performance. Unfortunately, the history found there, only covers a small time…
user2629743
  • 71
  • 1
  • 1
  • 3
6
votes
1 answer

MySQL Query Tuning - Why is using a value from a variable so much slower than using a literal?

UPDATE: I've answered this myself below. I'm trying to fix a performance issue in a MySQL query. What I think I'm seeing, is that assigning the result of a function to a variable, and then running a SELECT with a compare against that variable is…
gbro3n
  • 6,729
  • 9
  • 59
  • 100
5
votes
1 answer

Database Tuning Advisor - Why does it give me no results?

I am trying to use Database Tuning Advisor for the first time. I believe I have resolved all permissions-related issues - needing SHOWPLAN, needing SELECT permission on 'DTA_tuninglog', etc. Yet under 'Recommendations' it shows nothing. It…
jwd
  • 10,837
  • 3
  • 43
  • 67
4
votes
2 answers

Why SQL Server Tuning Advisor proposes to add PRIMARY KEY to included columns of index?

I have seen this a number of times, when I run some analysis in SQL Server DataBase Engine Tuning Advisor it suggest me to create indexes like: CREATE NONCLUSTERED INDEX [_index] ON [dbo].[SomeTable] ( [Column1] ASC, [Column2] ASC, …
Andrey Gulaev
  • 83
  • 8
4
votes
1 answer

postgres aggregate performance

I've been noticing some problems with simple aggregate performance in Postgres (8.3). The issue is that if I have a table (say 200M rows) that is unique by (customer_id,order_id), then the query select customer_id,max(order_id) from larger_table…
ryan
  • 85
  • 4
1
2 3
…
10 11