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
4answers
449 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
109 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
158 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
540 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
139 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 ...
2
votes
2answers
255 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 ...
2
votes
1answer
407 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
163 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
152 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 ...
2
votes
1answer
140 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
99 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
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 ...
2
votes
1answer
109 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
206 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
182 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
88 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
187 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
30 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
382 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
272 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
78 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
78 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
205 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
152 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
102 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
2answers
131 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
55 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
33 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
1k 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
107 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
108 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
321 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
337 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
2k 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
155 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
355 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
180 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
727 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 ...
2
votes
3answers
163 views
Performance impact of Splitting Table across files
EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:
If I have a SQL Server 2005 database using a single .mdf file and I change ...
0
votes
1answer
973 views
Mysql slowly uses memory until it starts to use swap
I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
1
vote
3answers
709 views
How much do foreign keys affect performance?
I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign ...
3
votes
2answers
118 views
Inner Join or direct data save in SQL Server
I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
1
vote
2answers
208 views
mysql_select_db() very slow on a busy server
I have a MySQL Server with about 30 Databases and 1500 Tables. Overall size of the data is 2 GB. The Server has 16 GB RAM and an AMD FX-6100 CPU. Most tables are myisam.
On average the server answers ...
5
votes
3answers
276 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 ...
5
votes
2answers
204 views
Index fragmentation and SSD disks
I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation.
Has anyone experienced this?
I'm thinking of changing my hardware ...
3
votes
1answer
107 views
What is the right way to manage users data?
I'm trying to create a simple register login script with user profiles, I created a table with the following structure:
+------------+---------------+------+-----+---------+----------------+
| Field ...
2
votes
2answers
159 views
Localhost vs LAN Database Performance [closed]
Normally, what some of us do is to just host the database and web-server inside 1 machine. Meanwhile we also may focus a machine with high RAM just for the remote database on LAN and another machine ...
4
votes
1answer
308 views
need suggestions to improve view performance
I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
2
votes
1answer
108 views
Improve the performance and stability of a MySQL server
We provide a B2B solution, which is doing quite a lot of UPDATE, INSERT an DELETE operations. Our infrastructure at the moment looks like this:
Home-Server ( MySQL (INNODB) + website )
5 worker ...
