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.
1
vote
1answer
540 views
Oracle 11g: “With Clause” very slow
I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all ...
1
vote
1answer
477 views
InnoDB Performance Tuning
I'm new to database administration so please excuse any omissions of what would seem like basic info to provide for this problem.
I'm a RoR developer and our team is currently short a DBA, so we're ...
1
vote
1answer
121 views
Database design with different user roles connected by different relationships to one organizaiton table
An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee.
One employee can only be a part of one organization and if he is an employee then he can't be ...
1
vote
1answer
96 views
What would be a optimal index for this query in mysql
I'm trying to optimize this query.
SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, ...
1
vote
1answer
441 views
High MySQL CPU usage, queries seem fine, not running ntp
I've had about 48 hours of extremely high CPU usage on my main MySQL database for no apparent reason.
I realize this question is quite similar to the one posed here: High CPU usage from MySQL with no ...
1
vote
2answers
288 views
Slow queries on SQL Server [closed]
We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the ...
1
vote
1answer
418 views
Oracle listagg forces SORT (GROUP BY) execution plan
I have the following query
SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
, listagg(type, ', ') within group (order by null) types
FROM test
group by id
Both type and id are short strings.
...
1
vote
1answer
185 views
Database inserts/updates slow only in Production
We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 ...
1
vote
2answers
141 views
Explicitly update a table after sp_tableoption “text in row” advices wanted
I have an extremely slow sql server 2000 DB with 8GB, and the cause of this is the 3 ntext columns which are in the more queried table. Table has indexes set and also the full-text ssearch index set ...
1
vote
1answer
376 views
PostgreSQL vs MySQL benchmarks when a 2nd level cache is included
Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
1
vote
1answer
206 views
What can cause an UPDATE statement to execute endless?
I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night.
Today it started but never endet.
So i've tried to run ...
1
vote
1answer
112 views
MySQL replication risks for the master
I have a production MySQL server, fairly heavily loaded with an INSERT-only load on one of its databases. I'd like to attach a non-production (sitting on my desk, more or less) replication slave to ...
1
vote
1answer
141 views
Slow enumeration of schemas and tables in a largish database
We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence ...
1
vote
2answers
817 views
How to keep one query from slowing down entire database?
Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's ...
1
vote
2answers
117 views
Improving MySQL efficiency
I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
1
vote
1answer
781 views
What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?
In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows ...
1
vote
2answers
117 views
Restore backup in order to write datafile sequentially in disk
I have just come to a new company and we have one database which was created with standard initial size and autogrowth parameters for data and log files (1 MB autogrowth for data / 10% for log).
The ...
1
vote
1answer
489 views
Set statistics time showing different elapsed_time than dm_exec_query_stats view
When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output:
DBCC execution completed. If DBCC printed error ...
1
vote
1answer
155 views
Appropriate Windows version for Mysql
I have a HP ProLiant DL380 G5 server and I would like to install mysql on it .
My questions are:
Which MySQL version should I install from this link for better performance, 32 bit or 64 bit?
Which ...
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
27 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
0answers
50 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 ...
1
vote
0answers
54 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
2answers
83 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 ...
1
vote
1answer
80 views
Mysql DB server hits 400% CPU
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.
- load average 40 to 50
- CPU % - 400%
- idle % - 45%
- wait % - ...
1
vote
1answer
62 views
Why does that query cause lock wait timeouts?
From time to time, I find a lot of these errors in my PHP error log:
MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL
The problem persists for about 2 or 3 minutes. ...
1
vote
0answers
31 views
postgresql 8.3 possible checkpoint and memory misconfiguration
I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application.
At ...
1
vote
0answers
55 views
How to make Greenplum 4.2.3 only scan the intended partition?
When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...
1
vote
1answer
47 views
Most efficient way to sort data fields into SQL
I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives:
Device X sends a text string "name=value&name2=value&name=value"
On arrival that ...
1
vote
0answers
162 views
Efficiently retrieve big amount of data based on date range
Table EVENTS
TimeStamp |O.Name |Location |Source |
2008-11-12 |Pretorian|Európe |England |
2009-12-24 |Lex Rosa |Európe |Italy |
2010-01-01 |Nasdaq |USA |Whasington |
...
1
vote
0answers
130 views
How to calculate running total based on dynamic values?
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
1
vote
1answer
290 views
MySQL performance tuning + queries stuck on “Copying to tmp table”
The latter part of the question's title (queries stuck on "Copying to tmp table") has been addressed many times, and I have spent a fair amount of time researching this. I would appreciate it if you ...
1
vote
2answers
220 views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
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
89 views
Why is this table structure so slow to CRUD?
I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records.
-- Table "mail_queue" DDL
CREATE TABLE ...
1
vote
1answer
100 views
Deriving formulas for input/output
I'm currently enrolled in a DBS class and am having problem with an assignment. I've searched around and have been unable to understand what it is I'm meant to be doing with this derivation formula.
...
1
vote
0answers
157 views
Finding the bottleneck by analyzing Munin graphs
We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
1
vote
2answers
170 views
PHP-MYSQL messaging system performance problems
I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?
I think, ...
1
vote
2answers
347 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
1
vote
0answers
62 views
need a sql query to find out the Database wait time ratio for MySQL
Oracle supports sql queries to SYS.V_$SYSMETRIC to find out Database Wait Time Ratio (% of wait or bottlenecks experienced in time spent on user-level calls) ...
1
vote
0answers
133 views
Multi-Table Join to Single Destination
I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.
To ...
1
vote
0answers
232 views
DBMS_LOB.READ performance and Oracle BLOB chunk size
I have a script that reads a lot of BLOBs using DBMS_LOB.READ, in the process of converting them to files. While looking to improve the read speed, I looked at GETCHUNKSIZE.
The documentation on ...
1
vote
1answer
269 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
1
vote
0answers
739 views
mysql taking too long to send data
I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".
The table
CREATE TABLE IF NOT EXISTS details (
id int(11) NOT NULL,
...
1
vote
0answers
66 views
Permon counters on SQL 2005 mirroring (user defined counter log file is empty)
I've been attempting to gather performance counters for mirroring using perfmon, over a period of time and saving this to a log file. I am able to do this without problem for general SQL Server ...
1
vote
0answers
237 views
Best database for INSERT 2 millions of records around 3 hours and query data at the same time [closed]
I have a voting application. This voting process takes about 3 hours to complete. After 3 hours there will be 2 millions of records in the database. At the same time application needs to query the ...
1
vote
0answers
149 views
SQL Server and Perfmon [duplicate]
Possible Duplicate:
SQL 2008 Server agent cant see perfmon counters
I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3. I'd like to run ...
1
vote
0answers
343 views
Which database technology for big structured data? [closed]
Scenario: Think you have got
100TB of text in 200 tables. This is structured related data. compareable to dbpedia only more data. Any really relational and distributed and performant database would do ...
1
vote
1answer
51 views
tuning feed item's table query with multiple condition
I have a query like this:
SELECT *
FROM `rss_item`
WHERE `feed_id` = 1 and `timestamp` < 1350698635
ORDER BY `rss_item`.`timestamp` ASC
MySQL: 5.1; table engine: innodb
Explain result:
...
1
vote
5answers
556 views
How should I save apache logs into a mysql table?
This is a php script to view apache logs.
I want to save apache logs into a mysql database.
Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
