An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.

learn more… | top users | synonyms

6
votes
1answer
455 views

Sudden increase in log_file_sync waits

I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion. Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
2
votes
0answers
82 views

Performance of database with transactional data

There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and ...
3
votes
1answer
437 views

How un-clustered is a CLUSTER USING table in PostgreSQL

I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL: # CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE; A maintenance task periodically ...
1
vote
0answers
290 views

Performance Hit when upgrading to Oracle 11gr2

I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer. Original Question We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to ...
0
votes
1answer
109 views

When is it worth it to use Triggers and stored procedures/functions? [closed]

As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post ...
17
votes
2answers
10k 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 ...
2
votes
1answer
233 views

excessive CPU utilization by SQL server

I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one ...
1
vote
3answers
771 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
0
votes
2answers
278 views

Partitioning MySQL for “expired” transactions to improve performance

I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly ...
1
vote
3answers
5k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
3
votes
3answers
151 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
-1
votes
1answer
1k views

Having too many connection problem

We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads. 1) We have 100 connectoin open in mysql. 2) ...
4
votes
1answer
387 views

Where and how to store my weird datamart

I could really use some help here. Here's my situation. I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
2
votes
1answer
505 views

How to ensure that date-range queries involving multiple time zones are sargable

I am building a web analytics application for search engine traffic only. You can see some screenshots here: http://myhappyanalytics.com/ It works similar to Google Analytics but it only saves and ...
4
votes
1answer
90 views

How can I get this query to use it's index?

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime). As I don't really know what I'm doing I tried adding indexes to all columns in ...
4
votes
2answers
2k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
2
votes
2answers
3k views

How to tune mysql to be inmemory-like

I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is). Is it possible to force ...
3
votes
1answer
71 views

CPU column 5x higher on one machine vs another?

We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we ...
4
votes
1answer
166 views

Why do psql queries take longer when inserting many rows. Why is it non linear

So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this: INSERT INTO contacts (numbers) SELECT distinct array[ (random() * 99999999)::integer, ...
8
votes
3answers
988 views

Worse performance on a new server

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
0
votes
1answer
715 views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
6
votes
2answers
4k views

MySQL replication: most important config parameters for performance on slave server?

I'm setting up a mysql master-slave configuration, where slave is located on a much weaker sever. Since, if I understand correctly, slave only works on updates/inserts, what are the most critical ...
3
votes
4answers
10k views

Speed up large clustered index creation?

I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB. And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
22
votes
2answers
16k views

Optimizing bulk update performance in PostgreSQL

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
1
vote
2answers
502 views

How to design database table to store process status?

I have an application (ProcessMonitor) that monitor some constantly running process at fixed interval (like 30 seconds, but can be changed by user) and store the process status to a database table, ...
0
votes
1answer
133 views

Efficient way to perform approximated search?

I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record. As it happens ...
1
vote
2answers
438 views

Is there slowdown inserting into an InnoDB table that has no index set?

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such. Those tables only contain a few thousand rows. Would it be faster to INSERT data ...
5
votes
2answers
8k views

Cannot Utilize Maximum CPU and Memory Usage for MySQL

Good day. I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem. I have a single stored procedure ...
8
votes
1answer
215 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
2
votes
1answer
614 views

How to enhance the SQL query performance for thousands and millions of huge data

Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
4
votes
1answer
1k views

Simple MySQL query randomly takes forever

I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will ...
1
vote
2answers
558 views

How should I store multiple relationships (many posts to many categories)?

I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ? I thought of it this way first: ...
2
votes
1answer
333 views

Switching to NoSQL for native JSON use?

I am rather comfortable writing concrete schemas with explicit primary keys, foreign keys, indexes, views and stored procedures. For queuing I use a NoSQL solution called: Redis. Recently I heard a ...
6
votes
2answers
3k views

Why use separate databases for high traffic/volume tables?

While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things. The first one contains the ...
4
votes
1answer
1k views

.mdf vs .ldf Performance considerations

I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for ...
20
votes
2answers
44k views

How to optimize a query that's running slow on Nested Loops (Inner Join)

TL;DR Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history: JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell ...
6
votes
2answers
3k views

Linked Server Risks

I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are: Use linked ...
7
votes
1answer
3k views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
1
vote
1answer
2k views

Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
0
votes
3answers
1k views

Reclaiming space from a large partitioned database

We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date ...
1
vote
1answer
789 views

Temporarly disable cache in oracle 11g

I want to disable the caching for my session in oracle 11g to see how long a query takes. Is this possible or is there a better way for meassuring the time a query takes to run?
1
vote
0answers
696 views

sql server 2008 execution plan different on two production servers [closed]

We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the ...
0
votes
0answers
89 views

Linked server performace issue?

I have created a linked server in SQL Server 2008 R2 Enterprise Edition for MySQL i.e. MySQL database is accessible under SQL Server environment. I have a created a trigger in SQL Server such that ...
2
votes
3answers
975 views

SQL server disk write queue dramatically grows sometimes

I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time ...
3
votes
2answers
581 views

How to implement posts with “seen by” like facebook?

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url ...
2
votes
1answer
4k views

SQL Server bulk insert performance

I’m running sql server 2012 and the installed ram on that machine is 128 GB. The performance of the bulk insert is very low. The bulk insert of 10 million records takes about 2 hours. We have checked ...
11
votes
1answer
5k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
6
votes
1answer
6k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
0
votes
2answers
169 views

Why does using a variable make a difference in performance?

I have a table that constantly doing insert, and I am trying to run a delete script to remove old record. The delete script looks like this: DECLARE @StarDate DATETIME = '2013-01-13'; DECLARE ...
2
votes
1answer
14k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...