Hot answers tagged parallelism
10
It is mostly a license issue. These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business. Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all ...
8
I can see two reasons:
1) historically, PostgreSQL had better query planner and statistics analyzer. This might be not true now, but few years ago PostgreSQL was much better then MySQL on complex queries, which is OLAP ones.
2) PostgreSQL have better functions/triggers/etc programming support.
7
There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that ...
6
You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in !
Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ ...
6
You usually don't want to disable parallelism as that will also disable it for admin tasks. Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes.
Based on your updated questions...
Some people will change MAXDOP to 1 for vendor built applications because they can't ...
6
It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.
When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly ...
5
CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of ...
5
I wrote about six potential issues with parallelism here:
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/03/21/six-reasons-you-should-be-nervous-about-parallelism.aspx
That was three years ago, and I'm sure other equally dangerous bugs have come up since I wrote that. I haven't gone through and validated any that have been reported as fixed, but the ...
5
You may find this surprising, but you should set the innodb_thread_concurrency to 0 (which is infinite concurrency). This will allow the InnoDB Storage Engine to decide how many concurrency tickets to issue.
I wrote a post about InnoDB's multicore engagement (MySQL 5.5, also MySQL 5.1.38 InnoDB Plugin) back on May 26, 2011.
According to the MySQL ...
4
This looks like a failed assertion in SQL Server code. Basically, you've uncovered a bug in SQL Server. You could submit it to Microsoft.
A quick solution is to add option (maxdop 1) at the end of your query; this instructs SQL Server not to spread the query over multiple processors (aka parallelizing.)
4
Two reasons can cause improvement of the same query over time:
The data is changing
The query plan is changing
The data you can't do much about.
To verify that the query plan is improving, try running the query manually during a time when you know the query is running at it's fastest.
SAVE the fast query plan
Run the same query during a time when you ...
4
You should find explanations for parallelism in SQL Server in these two questions:
A query submitted from different applications has differing DOP and
What is the meaning of DOP in the context of sql server?
To enable the use of parallelism in first server you have two options:
enable it at query level ( use option OPTION (MAXDOP 8) to enable the ...
4
SQL Server makes this decision based upon cost. If you execute 'sp_configure' (make sure advanced options are enabled) you will see an entry for 'cost threshold for parallelism'. If the optimizer estimates the cost to be greater than the run_value then your query will be executed in parallel.
If the ORDER BY clause in your example results in an increased ...
4
As Peter Eisentraut correctly pointed out, first and foremost it's a licensing issue. Postgres is licensed under a BSD-like agreement, which makes it essentially a "free for all", so long as you credit the original developers in your derivative work.
The MVCC vs. locking scheduler debate has been the subject of more than a few 'holy wars' online. The ...
3
Chances are the CXPACKET waits are actually disproportionately represented if you have a lot of threads waiting on something that is I/O bound. You can check this by setting MAXDOP=1 and re-running the query. See if the proportion of wait time from PAGEIOLATCH waits increases significantly.
If your PAGEIOLATCH waits are a large proportion of the wait time ...
3
Being able to commit more CPU cores to the processing of a request is a good thing, until the time it takes to reassemble the results exceeds the gain obtained from distributing the workload.
Check to see if you have high CXPACKET wait time. If you don't, then no worries. That article gives you some things to try if you want to gain more understanding on ...
3
Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":
Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:
SELECT
StatMan([SC0])
FROM
...
3
If you wish to see the actual execution plan of a query that is running.
SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = [YourSPID]
First then enter the result into this query.
SELECT query_plan FROM sys.dm_exec_query_plan (Enter the result here.)
That will show you actual execution plan that sql used for that query. You could use that ...
3
I built an MPP system on MySQL and I discarded the system for two reasons:
1) is Oracle
2) is the lack of hash joins - nested loop and index joins do not scale to the level required in a MPP system - again because Oracle inhibited the promised delivery of hash joins in the 5.x code line after it took ownership.
MPP big data systems must have joins that ...
3
You should consider Table-Valued Parameters and a new stored procedure that can take 1000s of invoices and deal with them as a set rather than the singleton insert you have now. Knowing how to write the procedure to deal with all invoices as a set would require first-hand knowledge of what the procedure does now with a single invoice.
I wrote up a quick ...
2
I've never seen the need to switch off or modify any parallelism settings in all my time with SQL Server (last millennium, SQL Sever 6.5)
Following on from @StanleyJohns answer...
An OLTP system with short, sharp queries should never hit the cost threshold ("cost threshold for parallelism") so it shouldn't matter. If you have some queries that do go ...
2
What causes parallelism?: There is a setting called cost threshold for parallelism. Once this threshold is exceeded then parallelism is used (if prerequisites are met).
The nature of an OLTP systems is to have a large number of quick and short transactions. Using parallelism sometimes increases the query processing time, as the query will be split up to be ...
2
This should not be a problem as long as the data in the two dump files involves different tables... however, it may or may not be as helpful as you might hope, because if the single restore is only using 10% cpu, that suggests that your disk, as opposed to cpu, may be the more significant determining factor in limiting the speed of the restore.
1
Suspended status doesn't tell you much; the wait type tells you the story. I would;
Download and Install sp_whoIsActive execute the below command while the processes are suspended to get the leading blocker, it's wait type along the lock graph. This should show the lock areas. (please upload)
exec sp_WhoIsActive
@show_own_spid=1,
...
1
There is pl/proxy, which provides a basic federation capability, but it's not an engine optimised for this sort of work. Greenplum is a commercial product that does have a shared nothing engine that might do what you want. However, it's not cheap - less expensive than Oracle but more expensive than SQL Server.
There are no pure open-source engines of that ...
1
Can we control for MAXDOP and choose a known table, e.g., AdventureWorks.Production.TransactionHistory?
When I repeat your setup using
--#1
SELECT MIN(TransactionDate)
FROM AdventureWorks.Production.TransactionHistory
WHERE TransactionID = 100001
OPTION( MAXDOP 1) ;
--#2
SELECT MIN(TransactionDate)
FROM AdventureWorks.Production.TransactionHistory
...
1
MySQL, any version, does not have any code to use multiple cores in a single connection.
Percona does a better job of using multiple cores across multiple connections in its Xtradb. InnoDB runs out of steam at about 8 cores; Xtradb flattens out at 32 or so.
A "large query" could be locking the table (or rows of the table) that some other connection needs. ...
1
It would be a very strange thing if parallel_max_servers is set to a value below cpu_count * parallel_threads_per_cpu. I would call that a mistake by the DBA even. Let me bring to your attention a new feature of 11g (you should always mention your version, by the way), called Automatic DOP, that I have introduced in a posting here:
...
1
I have seen a complex query split into multiple processes that takes hours to execute - you can usually see this in sp_who2 as multiple entries with the same spid.
Changing it to maxdop 1 and the query executed in less than a minute.
The lesson here is that the engine doesn't always get it right when it comes to parallelism.
Only top voted, non community-wiki answers of a minimum length are eligible
