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
1answer
363 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
1
vote
3answers
133 views
SQL Server xml performance
I have a table with 21 columns. The majority are numerical and the rest are nvarchar.
I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from ...
1
vote
0answers
126 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 ...
2
votes
0answers
30 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
1answer
157 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
9
votes
4answers
1k views
SQL Server 2012 slower than 2008
I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
0
votes
1answer
69 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
0
votes
1answer
74 views
Maintenance in MYSQL when innodb_file_per_table disabled
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.
But what if I ...
13
votes
4answers
435 views
Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
3
votes
0answers
73 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
1answer
92 views
Log all than queries taking more than X seconds
I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages.
I would like to log all the queries that take more than X seconds.
I guess that the I ...
3
votes
4answers
310 views
SQL Server - Separating data, log, and TempDB files on a SAN
I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
1
vote
1answer
94 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, ...
2
votes
0answers
134 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
521 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
124 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 ...
1
vote
2answers
218 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
278 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 ...
2
votes
0answers
133 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
147 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 ...
1
vote
1answer
106 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 ...
0
votes
1answer
84 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 ...
1
vote
3answers
190 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 ...
2
votes
1answer
96 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
142 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
2answers
127 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. ...
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, ...
0
votes
1answer
170 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 ...
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 ...
2
votes
1answer
269 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.
...
2
votes
1answer
237 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 ...
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), ...
1
vote
1answer
156 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 ...
2
votes
1answer
146 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` <= ...
1
vote
1answer
95 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 ...
3
votes
2answers
121 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
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:
...
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
3answers
968 views
MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine
I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.
I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
2
votes
1answer
104 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 ...
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 ...
1
vote
1answer
96 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
4answers
300 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 ...
3
votes
3answers
311 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 ...
4
votes
1answer
137 views
PostgreSQL: Recommendation size for shared_buffers
I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.
I am looking for help about the proper memory values for the configuration file.
Currently, I ...
1
vote
1answer
318 views
What is the best and most performant MySQL cloud solution?
I'm looking for a solid solution (~1GB of DB space). Speed is crucial and I'm happy to spend up to $100 a month on a decent solution. The database will be used by a series of desktop applications ...
0
votes
3answers
171 views
INNER JOIN Giving time out on large database
Getting time out on this script
UPDATE
uk_data AS ud
INNER JOIN
uk_pc AS up
ON ud.cat10 = up.WardCode
SET
ud.cat8 = up.Latitude,
ud.cat9 = up.Longitude;
uk_pc Table ...
5
votes
2answers
559 views
SQL Server gets slow over time until we have to restart it
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
