Tag Info

Hot answers tagged

8

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember them being effective was in SQL Server 2000, so it is not terribly surprising that you find they are ignored in SQL Server 2012. For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is ...


5

Even if you have plenty of DRAM, tempdb may still be used. This happens in a few situations: Snapshot isolation: Using this feature can create a lot of tempdb activity. Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is ...


4

Databases require certain guarantees from the storage it runs on to implement ACID. Unfortunately, it is not always possible for the database to determine if those guarantees are met. Here are some requirements that the storage (no matter if it is NAS, direct attached or SAN) must meet: Write Ordering: Writes my happen in the order they are confirmed to ...


3

As a general rule of thumb, you should give the database as much information as possible about the task that you're implementing. How does this apply to your scenarios? Scenario 1 (INSERT .. SELECT) The database knows that you're about to bulk-move a whole set of data from one table or from one derived table to another. It can optimise the execution given: ...


2

Performance It depends A LOT on the actual data (are there many people having read a few books each, or a few people having read many books), skewing (are there some power-readers?) and the books which you query - like ypercube mentioned the bible. And this is before the optimizer really sets in and decides to completely rewrite your query because it ...


2

Give your example, this will do it: select distinct on (coalesce(t2_id, t1_id)) t1_id, t2_id from t0 order by coalesce(t2_id, t1_id); It essentially says "do a distinct on t2_id, but if that is null use t1_id instead".


2

I'm a proud owner of a Samsung 850 Pro for my desktop machine. It is a great disk, but it is not server-grade, and it is far from the PCI Flashcards sold by Virident and FusionIO (to put examples of some known brands). On the specialized hardware, a recent version of MySQL is almost a must, and some configuration tuning is needed to get most of them (change ...


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


2

mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method. There are several things that you can do in the MySQL configuration that will speed ...


2

Have you looked at stats from \s? I wasn't sure by 'transaction' you simply mean queries.


1

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why: !This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs! Standards - each database is setup the same ...


1

You might also check the frequency / size of your commits: I ran into an issue recently in which I was trying to update > 1 million records in a single transaction. I got log messages similar to those described by OP, but the transaction could not complete even after several hours. When I broke the write into several smaller transactions (10,000 records or ...


1

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring ...


1

From the online manual http://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html The number of table locks or lock requests there are for the table. For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block ...


1

Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving ...


1

I wrote these posts Mar 28, 2014 : MySQL not releasing memory Apr 24, 2012 : How costly is opening and closing of a DB connection? May 24, 2011 : What are user connections - when are the created and destroyed? and discussed the many buffers that are allocated for each connection. They are part of RAM and not encapsulated within the cache. Since the ...


1

For your specific example scenario 1 keeps all the data on the server. Scenario 2 will require the data to be packaged, sent over the wire to the client, where is must be buffered (and, perhaps, spilled to disk) then un-buffered, re-packaged and sent back to the server, where it will finally be processed. This network time adds up. Do this often enough ...


1

If you stay with MySQL 5.1 Since you cannot upgrade at this time, you need to install the InnoDB Plugin. It was available since MySQL 5.1.38 (See the Release Notes). I wrote a post about it : MySQL - Installing InnoDB Plugin Surprising fact: Percona already ahd the InnoDB in Percona Server 5.0.45 long before Once you do, you need to set the following ...


1

Remember, MongoDB has a dynamic schema. So it is perfectly ok to store this document: { "JobNumber" : "50001-01", "CustomerId" : "joe", "IdentifierNumber" : NumberLong(8812739), "TimesPrinted" : 0, "Packaging" : {"bundle":1200,"box":120,"pallet":3} } and this document { "JobNumber" : "50001-02", "CustomerId" : "jane", "IdentifierNumber" : ...


1

Since we don't have the hardware, of course, I can't benchmark it While you can't simulate the exact hardware you might be able to get some estimates by comparing simpler local hardware with similar relative differences. If you are upgrading from spinning rust in a similar RAID config then you could benchmark a single traditional drive against a single ...


1

If you want to condense it into a single SELECT, this would work: SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id) t1_id, t2_id FROM t0 ORDER BY coalesce(t2_id, t1_id), t2_id, t1_id; Equivalent, except for sort order. SQL Fiddle. If you want this to be fast, I'd try a functional index: CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), ...


1

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints. For instance, if the app uses a 0 in a key column instead of NULL to ...


1

For the simple case, I can only think of minor improvements to the query: ( SELECT DISTINCT ON (t2_id) t1_id, t2_id FROM t0 WHERE t2_id IS NOT NULL ORDER BY t2_id, t1_id -- to get consistent results ) UNION ALL ( SELECT DISTINCT ON (t1_id) t1_id, NULL -- cheaper FROM t0 WHERE t2_id IS NULL -- if you retrieve ...


1

My diagnosis from the information that is currently available: Sounds like your tables are for whatever reason mostly uncached. The first run brings all data into cache. The plans probably have tons of random IO. This is extremely slow to run on a disk, and almost unnoticeable in memory. Therefore, I guess that the new edition does not provide you as much ...



Only top voted, non community-wiki answers of a minimum length are eligible