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.
3
votes
0answers
77 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 ...
17
votes
1answer
557 views
Get and Put performance testing on Google BigTables (and other integrated DBs)
What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools?
For example, ...
1
vote
1answer
97 views
How and what is the most efficient way to join two tables, retaining a particular field from both?
I have two tables, with the following columns:
Table 1
ID
Component
Data1
Data2
Table 2
ID
Component
Data3
Data4
If I have rows in these tables, with items Component1, Component2, Component3, ...
0
votes
1answer
741 views
mysql closing tables taking double the time of process
I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table.
It is taking a long time to close tables.
ex: I run a query and the total time ...
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
1answer
657 views
Connection to localhost dramatically slower than to network server
I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB.
When I'm on the network at work, the connection is almost instantaneous. When I am at home working with ...
2
votes
0answers
139 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 ...
6
votes
3answers
527 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
4
votes
1answer
125 views
Stored procedure performance metrics
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by ...
9
votes
2answers
1k views
Updating 700 million rows to same value
I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.
I don't have admin access, or access to an admin, so this needs to be accomplished with ...
3
votes
2answers
115 views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
1
vote
2answers
136 views
MongoDB - Should I separate the journal and the data to different drives?
I have seen and heard from multiple sources that it can be a good idea, performance-wise, to write your db journal and your data files to separate disks.
What's the recommended way to do this (e.g. ...
2
votes
0answers
139 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 ...
4
votes
1answer
148 views
One CPU in a 32-core system is getting to 100% usage and hurting performance
I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and ...
3
votes
1answer
183 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
1
vote
1answer
551 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
113 views
SSD for production database
I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides ...
5
votes
2answers
2k views
SQL query slow-down from 1 second to 11 minutes - why?
Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql.
WITH Fkeys AS (
SELECT DISTINCT
OnTable = OnTable.name
,AgainstTable = ...
3
votes
3answers
3k views
How to avoid index scans in SQL Server 2005?
I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.
Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.
...
5
votes
3answers
272 views
Reducing the size of a database
SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
1
vote
3answers
191 views
Good, Bad or Indifferent: WHERE 1=1
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...
1
vote
5answers
571 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 ...
0
votes
1answer
86 views
Which is better: many join conditions or many where conditions?
I am trying to compare two queries:
Query 1:
SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c AND tableA.d=tableB.d AND ...
0
votes
1answer
174 views
MongoDB Master and Slave Replication - Windows or Linux
I have recently moved my MS SQL Server Database to MongoDB (2.0) on Windows Server 2008. Its containing 2 databases with following stats. (both databases get around 100 reads/writes per second). These ...
2
votes
1answer
99 views
Which is more efficient, trigger or PHP code?
My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking ...
0
votes
1answer
159 views
Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes
I just started dabbling with query cache for our CMS.
Can anyone tell me why (or at least a good guess) I get alot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?
...
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, ...
2
votes
1answer
171 views
optimal table design mysql with primay key and varchar value
I have a table:
id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)
which currently holds over 50 millions rows (and growing fast) and is hit hard:
process 1 queries this table (>200/sec) with a ...
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 ...
7
votes
2answers
1k views
INSERT performance difference between temporary tables and table variable
I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.
This is the code to insert ...
2
votes
1answer
297 views
What's the difference between POINT(X,Y) and GeomFromText(“POINT(X Y)”)?
I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.
...
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 ...
6
votes
1answer
224 views
Backup to three drives suddenly slow
The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
5
votes
1answer
631 views
Oracle's Merge v. Select->Update/Insert
What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?
I'm trying to optimize a database, but I haven't found any speed tests comparing the two.
I found ...
0
votes
0answers
71 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), ...
2
votes
1answer
242 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
3
votes
1answer
73 views
Is there a standard way to replace codes with values from a lookup table for reporting or analytics?
Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
1answer
166 views
MySQL 5.5 - determining correct write[read]_io_threads on high-end system?
Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server.
Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write.
Currently we are running on the default ...
3
votes
2answers
122 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
1
vote
1answer
96 views
cloud computer to speed up sql big data queries?
i have been doing a lot of data mining at my new job.
we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis.
we're currently ...
4
votes
5answers
1k views
Improve sort performance in PostgreSQL?
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
1
vote
1answer
52 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
3answers
129 views
SQL Server 2000 performance
I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by ...
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.
...
2
votes
1answer
105 views
How much overhead is involved with querying a DB on the same server?
History
The company I work for had two databases, DB A and DB B, on two different servers. A contains information pulled from a COTS application database in addition to information from our own ...
3
votes
3answers
316 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
1
vote
3answers
1k views
which mysql version to use - 5.1 or 5.5?
I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second.
Which MySQL version is recommended?
I came across a post on ...
1
vote
4answers
303 views
Update many-to-many relationship table
I have three tables: task, taskmembers, members. Each task may have up to five people associated to it. Currently, when someone updates a task I delete all the current members and insert them as a ...
1
vote
1answer
99 views
How does disabling a network protocol affect SQL server performance
The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if:
disabling Named pipes - or - ...
1
vote
1answer
97 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, ...
