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.

learn more… | top users | synonyms

3
votes
1answer
374 views

Can I use mysql to serve 100's of millions of small image files?

I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
3
votes
1answer
7k 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 ...
3
votes
1answer
127 views

which design better when use foreign key instead of a string to store a list of id [duplicate]

Possible Duplicate: Is using multiple foreign keys seperated by commas wrong, and if so, why? I'm building online examination system. I have designed to table, Question and GeneralExam. The ...
3
votes
2answers
213 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 ...
3
votes
1answer
241 views

In-memory SQL Server log file

I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is ...
3
votes
1answer
272 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 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
3
votes
1answer
352 views

PostgreSQL memory spike upgrade from 8.2 to 9.1

We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, ...
3
votes
1answer
230 views

PostgreSQL synchronized commit performance

I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can ...
3
votes
1answer
649 views

PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues?

Using PostgreSQL 8.4/CentOS 6: if I set the archive_mode parameter to on in postgresql.conf, but don't actually store the generated WAL files anywhere via the archive_command parameter, is there any ...
3
votes
1answer
2k views

How to find the detail cpu high process name which occupying most resource in the SQL Server database?

I'm running into a problem when using the following SQL to identify CPU usage in the production SQL Server 2005 env. DECLARE @ts_now bigint; SELECT @ts_now = cpu_ticks / CONVERT(float, ...
3
votes
1answer
556 views

MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

I have a mysql insert on update query like so insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars'; col1, col2 ...
3
votes
1answer
2k views

Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
3
votes
0answers
64 views

Creating MSSQL Performance Baseline Monitoring

In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive sql instances. My thoughts are: I want to ...
3
votes
0answers
52 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently?

This answer motivated think again my following tables but I am still uncertain how to store statistic data for quick selection. I think it is well done with partial indexes because only a subset of ...
3
votes
0answers
84 views

Why the stored procedure is much slower than a temporary table? [closed]

I want to do the updating of my database every day and I will like to use stored procedures but the time of execution of the temporary table is faster than the stored procedure. How can I optimize my ...
3
votes
0answers
40 views

SQL Server Disk IO Performace appears different between otherwise identical servers [closed]

I have two identical servers each running Microsoft SQL Server and performing a batch processing job. Server #2 keeps timing out while processing while server #1 appears to be running smoothly. I ...
3
votes
0answers
59 views

MS SQL Server IO read increase appear randomly

The number of read (IOPS) in my MS SQL Server database increased randomly even during the night (low activities) from 0.3 MB/SEC (normal) to 25-30 MB/SEC. After like 30 minutes, it's capped to 6 ...
3
votes
1answer
265 views

Biggest wait WriteLog

I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where ...
3
votes
0answers
209 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
3
votes
0answers
99 views

Optimizing postgres row overlap constraints involving UUIDs and GIST

For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window. The only way I ...
3
votes
1answer
174 views

PostgreSQL performance worse when adding CPUs

I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this: Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs. Ubuntu 14.04, standard PostgreSQL 9.3 ...
3
votes
0answers
464 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
3
votes
2answers
157 views

Enhance performance of update query

I have a query that is running against a table with 50 million rows. The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values. We are seeing ...
3
votes
0answers
409 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
3
votes
0answers
2k views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
3
votes
2answers
662 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
3
votes
0answers
40 views

High CPU usage on SQL server - Slow queries [duplicate]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
3
votes
0answers
2k 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 ...
3
votes
2answers
308 views

Why is my CPU usage increase after setting database to read-only on SQL Server 2008 R2 [closed]

I administrate a reporting database. We run all the de-normalization process on a database DB1 daily, then we copy all the data to an another database DB2 on the same server which is queried by web ...
3
votes
3answers
1k views

Increase Oracle buffer cache gets slower

So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
3
votes
1answer
584 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: ...
2
votes
2answers
4k views

MS SQL Server 2008: How to improve performance after deleting transaction log file

Due to the size of transaction log file (it grew up to 200 GB) I had to delete it because the disk was running out of space. I know this is not a good practice but anyway after that the performance is ...
2
votes
4answers
889 views

Advice on choosing a really fast DBMS

I am working on a project that does real-time monitoring of data being transmitted, and logs if there are any of the various type of errors, etc. The CRUD operations are real-time. We initially ...
2
votes
5answers
3k views

How to determine why a MySQL server has become slow and requests sometimes time out?

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the ...
2
votes
3answers
151 views

Performance difference between searching table with 350 columns and searching table with 25 columns

I am currently doing some hibernate performance test. My requirement is to search for data in a particular column of a table containing 8 million rows and 350 columns. Currently, I don't have the ...
2
votes
4answers
482 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
2
votes
3answers
826 views

SQL server disk write queue dramatically grows sometimes

I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time ...
2
votes
1answer
436 views

Query takes hours

Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
2
votes
5answers
2k views

Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment. However, none of us are formal DBA's so a lot of what we are doing is ...
2
votes
1answer
2k views

Does SQL Server allow INSERTs into tables and indexes to be written in parallel?

If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?
2
votes
2answers
67 views

query plan caching (sql server)

Is there a measure of time frame as to which the execution plan of inline queries will cached?
2
votes
3answers
302 views

What affects the speed of a count(*) besides the number of records

I have a query like SELECT COUNT(*) FROM Foo Where Bar = 1 AND Baz = 2 the table has 12934600 records of which 1000001 match that predicate Looking at the query statistics I see (1 row(s) ...
2
votes
2answers
890 views

How can you tell when a query plan reuse is causing performance problems

When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot. This ...
2
votes
2answers
2k views

How to tune mysql to be inmemory-like

I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is). Is it possible to force ...
2
votes
2answers
947 views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
2
votes
2answers
8k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
2
votes
3answers
3k views

Improve `Update` performance (rows locking issue)

I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database. When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
2
votes
1answer
653 views

Disk Latency vs Throughput

I've searched the web for a little while and haven't found an answer I'm confortable with. I am testing a SQL Server on a VM that was presented new disc. If disk latency is reduced, shouldn't ...
2
votes
2answers
216 views

Large number of VLF and shrinking the log

If there are lot number of VLF then it will effect the performance of the queries. I saw that solution is to shrink the log file. But shrinking the file is also bad. So how to take control of whole ...
2
votes
2answers
10k views

Splitting a large table to improve performance

This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table. The table is 100+ million ...