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.
6
votes
1answer
72 views
Minimizing Indexed Reads with Complex Criteria
I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such:
CREATE TABLE TICKETS (
TICKET_ID id PRIMARY KEY,
JOB_ID id,
ACTION_ID id,
STATUS str256 ...
2
votes
1answer
149 views
Does MySQL have problems with nested insert like with subqueries in where?
Query 1:
INSERT `personal`.`locations`
SELECT DISTINCT `s`.*
FROM `references` `t`
JOIN `locations` `s` ON `first_id` = `s`.`id`
WHERE
`lat` >= 37.3
AND `lat` <= ...
2
votes
1answer
263 views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
1
vote
1answer
81 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
63 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
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 ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
3
votes
0answers
74 views
What utilities can I use to simulate I/O load for a DB restore and DBCC check?
I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped ...
2
votes
0answers
92 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
0answers
32 views
How can one improve the performance of a query that selects on a low cardinality column in MySQL?
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
136 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
2
votes
0answers
135 views
How should I model a binary-tree like data using Adjacency List?
I'm making a system where a user can recruit others, resulting in a binary tree model of users. I have decided to use Adjacency List to model the data but I have some doubts about the number of fields ...
2
votes
0answers
397 views
Oracle high Paging Faults
What are the reasons that lead to terrible high Paging faults.
The following is the topas information and background about the Environment.
OS: AIX 6.1
Softwares: Oracle 11gR2
Paging Space:30720 ...
2
votes
0answers
78 views
How To Analyse Index Scans
I am using merge replication with SQL Server 2012.
During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
0answers
623 views
Table Lock on INSERT - How to optimize
we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
2
votes
0answers
141 views
MySQL database structure: more columns or more rows
I collect how people tag topics with categories in table like:
ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12
This table holds 1 row per topic and dynamically ...
2
votes
0answers
2k views
Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded
Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
2
votes
0answers
124 views
How to Connect R to Oracle?
I need to connect R to oracle and I have been unsuccessful so far. I downloaded two packages: RODBC & RODM.
This is the statement that I've been using: DB <- ...
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
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
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
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
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
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
740 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
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
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 ...
0
votes
0answers
29 views
Do certain string patterns affect query performance on a VARCHAR column?
I am a beginner on MySQL Administration and performance problems. Now this is confusing me.
I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
0
votes
0answers
42 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 ...
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
43 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
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 ...
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?
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 ...
0
votes
0answers
355 views
'Incorrect datetime value: for column 'last_login' at row 1'
i am getting below error:
Last_SQL_Errno: 1292
Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. ...
0
votes
0answers
45 views
Database too slow after CHARSET Conversion
I have an Oracle database which runs on version 9.0.1 with WE8859P1 charset and i'm trying to migrate it to the version 9.2i which has the WE8MSWIN1252 charset.
The conversion is made without any ...
0
votes
0answers
27 views
How Best to Parition Temporal Data for Access By Date
The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an ...
0
votes
0answers
69 views
What is faster - multiple access of table or using temporary table?
I have created two tables
create table one(ref_no int, name varchar(100), design varchar(100));
create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), ...
0
votes
0answers
31 views
Performance While merging two mysql DBs
I have two similar schema DBs which I need to merge. I was thinking about the performance issues which I need to keep in mind while creating the script. These are the ones which I can think of.
...
0
votes
0answers
253 views
Oracle 10g - Sudden change in execution time for specific query
After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went ...
0
votes
0answers
80 views
Please help configure my Mysql for 12GB 4cores server
I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
0
votes
0answers
48 views
Long disk queue when no traffic
I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 ...
0
votes
0answers
47 views
How to store massive quantities of machine generated data?
I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances. ...
0
votes
0answers
142 views
Optimizing MySQL for a shared hosting setup
This is my current setup:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
...
0
votes
0answers
152 views
How to get number of select statements got executed in MySQL server (Simply com_select is going on high)
I want to know how many number of Select statements that got executed in MySQL server. Also I want to exclude the queries that are executing from query cache.
I thought status variable 'com_select' ...
