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.
5
votes
0answers
40 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 ...
0
votes
0answers
23 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 ...
1
vote
1answer
21 views
How un-clustered is a CLUSTER USING table in Postgres
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres. Data accessed at the same time is "defragmented" into a small number of disk blocks:
# CLUSTER table USING ...
1
vote
0answers
46 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
51 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 ...
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 ...
2
votes
1answer
59 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
65 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
17 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 ...
0
votes
2answers
46 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
71 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 ...
0
votes
1answer
33 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) ...
2
votes
0answers
87 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
36 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 ...
0
votes
1answer
41 views
MySQL DB Design
I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, ...
0
votes
0answers
41 views
Hierarchical data best practices
I'm looking for a best practicies\databases to solve our problem:
Currently we are using SQL Server 2008 R2 with HierarchyId. It works ok, but we have a lot of performance penalties due to this ...
3
votes
1answer
57 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 ...
2
votes
2answers
102 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 ...
0
votes
2answers
178 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
54 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 ...
3
votes
1answer
35 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,
...
0
votes
1answer
56 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 ...
2
votes
2answers
115 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 ...
1
vote
4answers
126 views
How to speed up creating clustered index on large table on SQL Server 2008 R2?
I have a large SQL Server table, the row count of the table is more than 3 billion, the data space for this table is about 120G.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, ...
2
votes
2answers
87 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 = ...
0
votes
2answers
34 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
48 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 ...
0
votes
0answers
18 views
Table partionning problem
I need your help for a partionning problem. We are using a "log_event" table which log a lot of events sent by our players (subscription, connection, play, share, etc...). We insert 5,000 row / s in ...
0
votes
0answers
42 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
0
votes
2answers
36 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 ...
0
votes
1answer
51 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 ...
5
votes
1answer
96 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 ...
1
vote
1answer
70 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 ...
3
votes
1answer
90 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
55 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:
...
1
vote
1answer
90 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 ...
3
votes
2answers
130 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
88 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 ...
0
votes
0answers
14 views
MySQL shared server regular lockup
I have a web/ database server with 5 websites on it, all of which execute fairly complex queries regularly.
Some of these queries appear in the slow log and take around 0.6-0.8 seconds. However I ...
5
votes
3answers
131 views
How to optimize a query that's running slow on Nested Loops (Inner Join)
Consider the following simple query (only 3 tables involved)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
...
1
vote
2answers
60 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 ...
2
votes
1answer
59 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
66 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
2answers
49 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 ...
0
votes
0answers
33 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
2answers
81 views
sql server 2008 execution plan different on two production servers
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 ...
2
votes
3answers
121 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 ...
2
votes
2answers
86 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 ...
1
vote
1answer
75 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 ...
0
votes
0answers
31 views
The best approach to index dynamic query in MongoDB
I am working on log managment system where user will be able to upload logs from file. I have 'event' collection where I store all events from all sources (each source can have different log format ...
