Tag Info

New answers tagged

0

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


0

You want to use bulk write operations instead of individual updates and inserts. // BEFORE your iteration loop var bulk = db.another_collection.initializeUnorderedBulkOp(); // INSIDE your iteration loop // instead of db.another_collection.update(…) bulk.find(query).update(yourUpdateDocumentHere); // instead of db.another_collection.insert(…) ...


2

(This is very similar to a question recently posted on S.O.: Time trigger database updating) There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...


0

It depends from the distribution of your inserts if moves going to occur and how many. 1) you start with one chunk {minkey,maxkey} which lives on primary (lets say shard000) 2) as you insert data a split will occur. Might be 2.1) {minkey, US} {US,maxkey} -> moves the second chunk to shar001 2.2) {minkey, TR} {TR,maxkey} -> moves first chunk to shard001 ...


4

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other? The ratios, however, are similar with: 162509 / (162509 + 33073) ~= 83% 44648032 / ...


0

The problem (from what I can see) From what I can see from your output, you have a monotonically increasing shard key like ÒbjectIdor aDate` or something similar. MongoDB sharding is done with key ranges over the selected shard key. Put simply, it works like "shard0000 is supposed to hold the rang from -infinity to $i, shard0001 the keys from $h to $p and ...


0

no, pgstrom cannot do that. it only able to scan relations - it is not able to join. what you an do is pack an operator into a GPU routine but = is not a good candidate for that.


1

Table layout It would be more efficient to reverse the column order in nodes: fixed length NOT NULL columns first. This is just a tiny optimization. It's only the first item because table layout comes first. CREATE TABLE nodes( id bigint PRIMARY KEY , type text NOT NULL , name text ); Index Replace substring(name,1,2700) with left(name, 2700) ...


1

Yes, it is possible to split this 100 million row table into any number of tables, even without it having any indexes. Now, you might could do it purely in T-SQL but that would require loading each of the split tables via individual insert statements in a transaction, and that can be a bit heavy on the tran log, especially if splitting to tables of 2 million ...


2

This is clear example of Many-to-Many relation (M:N). Many messages can have (be translated into) many languages. Date structure for that has three tables: create table Message ( ID int primary key, message text ); create table Language ( ID int primary key, language varchar(50) ); create table Message_Language ( message_id int, language_id ...


0

one thing you got to keep in mind is: if you materialize the count, you might have some fun on the concurrency side. however, a materialized column is of course always a lot faster.


0

Option a is much more likely to return the correct value. Option b requires a new table and implementing code to maintain it. This would definitely be a sign of premature optimization. I would expect you want a query something like. SELECT attribute, COUNT(attribute) FROM mytable GROUP BY attribute If the attribute column is ...


1

If you are primarily concerned with the complexity of getting the 20 most recent records, you could either write a view that does a UNION ALL of the tables, or create an empty parent table with all the common columns and have all the other table inherit using the PostgreSQL-specific inheritance feature, to hide the visual complexity of the query. Although ...


1

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index. This will probably lower the query execution ...


0

I have also gone thru this issue for at least 2 weeks without any robust resolution rather than confusing one issue with the other. Finally the resolution as follows:- I have reset the compatibility from 010 to 011 Reset the compatibility of master database too. By default sql will retain the old compatibility setting. That we need to change manually. ...


1

You can get the approximate CPU consumption with one of the connection properties: ApproximateCPUTime Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor ...


0

I conducted a series of performance tests against the new service tiers (as mentioned by another poster above). I tested I/O rates back in July and have now also tested the memory (i.e. max buffer pool size) in each tier: http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/ (I was going to add this as a comment on ...


0

rman is the tool for this. https://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm There is a learning curve, but once configured, it's much faster than export/import.


1

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON ...


0

Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas ...


0

I will update my progress and other stuff I discovered below. Checking /etc/init.d/mysql mySQL starts with this su - mysql -s /bin/sh -c "/usr/bin/mysqld_safe > /dev/null 2>&1 &" The error log showed that 65535 open_files_limit directive was not successful. Simply, mysql user could not set its own limits. Raising ulimit nofiles for all ...


1

What about using a union? SELECT * FROM table WHERE (col1 = '123' AND col2 = '456') UNION ALL SELECT * FROM table WHERE (col1 = '456' AND col2 = '123')


1

SELECT * FROM table1 INNER JOIN table2 ON table1.id = '123' AND table1.id = table2.col; or SELECT * FROM (SELECT * FROM table1 WHERE id = '123') A INNER JOIN (SELECT * FROM table2 WHERE col = '123') B ON B.col = A.id;


1

The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id. For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you ...


0

Look at http://stackoverflow.com/questions/11442191/parallelizing-a-numpy-vector-operation Essentially, you could do this in numpy relatively easily, with numexpr to take advantage of multi core. I haven't tried it yet, so YMMV.


2

You should put everything on a level playing field. How ? Without proper tuning, it is possible for older versions of MySQL to outrun and outgun new versions. Sep 25, 2013 : Why should I use InnoDB and MySql instead of XtraDB and MariaDB? Mar 26, 2012 : Percona vs MySQL Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) Oct 05, 2011 : ...


2

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem. Try running the following query replacing DBName and SPName as appropriate. USE DBName GO SELECT SUBSTRING(text, statement_start_offset/2, CASE WHEN statement_end_offset = -1 THEN 999999 ELSE ...


1

Starting with the highest and moving down: SOS_SCHEDULER_YIELD This wait type is usually caused when you have queries performing huge scans. I'd suggest checking to make sure you have the proper indexes in place and you don't have any out-of-date statistics resulting in an inefficient query plan. OLEDB OLEDB waits indicate that your query has to reach ...


0

http://www.brentozar.com/archive/2012/06/sql-server-max-memory-myths-video/ has some background why MAX memory should be set. 2012 starts to get covered in the middle of the video.


2

Some details to clear things up Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic. No, its not written its a good practice its said its recommended and I am sure MS books online cannot write it as good this because of fact that its always good to have ...


2

The MIN value is useful when you have a server with multiple instances. You can use it to prioritise memory usage per instance. But mainly it is to guarantee that SQL with have at least that much memory to work with. This stops people running something on the server and stealing all the memory. The default setting basically leaves memory allocation to SQL ...


1

Your code looks mature, mostly, but see below! Once you fixed that, I don't see much that could go wrong with it. I share the doubts that it will improve performance much, though. And there may be better alternatives, depending on exact requirements. General advice For simple functions as displayed: Use simple SQL function (LANGUAGE sql) which can be ...


0

This approach is not something I would recommend. There is way too much that can go wrong and lots of operations in the database that are unnecessary. Have you benchmarked the slowness you claim? Chances are, it's not slow at all. I'll demonstrate: create table fmt (k text, f text, constraint pk_fmt primary key (k)); insert into fmt values ('myfmt', ...


7

Are there any reasons for periodic/cyclic slow-down of insert performance? Yes. check point events. With a write intensive workload, big RAM server, as you describe, a large number of 'dirty' pages accumulate in memory. At the predetermined checkpoint interval all these dirty pages get written to disk, causing a spike of IO requests. This in turn slows ...


2

There is a situation in your current setup that would/could cause some slowdown related to an auto-incrementing key (IDENTITY, GETDATE(), NEWSEQUENTIALID()): under high-concurrency INSERT operations, there can be contention related to placing rows on the same page. This is called a "hotspot" and is one of the few drawbacks to auto-incrementing values as ...


7

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree. In a DML heavy workload there can be many small tree adjustments called page splits. These ...


0

for security, you want your data duplicated on many servers as possible, so if one falls, the others can share the load. "classic" oltp databases performance issues raise from concurrent transactions. Due mainly to locks, time/transaction grows exponentially. Hence, a simple way to mitigate this issue is to have multiple databases, as there will be less ...


0

about the transaction log I personally initially set it to 10% of the database size, and keep monitoring to get to the right usable amount (you want to avoid autogrowth). according to this article by Kimberly L. Tripp, you should grow the log in multiples of (not greater than) 8 GB and keep an eye on the number of VLFs. this is the way I generally get this ...


0

No auto shrink can't permanently damage a database. It may lead to highly fragmented tables and indexes which may hurt performance. The fragmentation could be difficult to remove completely, but minor fragmentation itself shouldn't be a concern. You need to assess whether this is really an issue for you worth spending time and effort to fix.


0

There is nothing wrong with setting the initial log size to 70GB, if you need that large of a transaction log. There is a problem, however, with it set to grow in 10MB increments. Such a small increment of growth can result in numerous VLFs (Virtual Log Files) which can result in long recovery of the database during startup and lengthen restore times. You ...


0

Performance considerations say you should have related tables in a single database. Security says you should separate concerns by need, but within a single database you can still maintain that separation via schemas. Separating data for the sake of some unknown reason that is not tested makes no sense. High availability probably says one should try to ...


2

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions. The reasons why InnoDB is ...


0

This query can expressed less awkwardly as a join. We join the two tables social_objects and social_permissions on their IDs (that is, match them up with IDs) and simplify the WHERE clause. SELECT so.* FROM social_objects so, social_permissions sp WHERE so.id = sp.id AND ((sp.model = 'User' AND sp.foreign_key = 1) OR (sp.model = 'SocialList' AND ...


5

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include: There were 124,000 updates but did they take 10 times longer than they used to? Did they block users? Is there more activity because people can do more because it's faster, or just a natural ...


0

Yes, all this tuning variables are described in MySQL Documentation


1

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.


1

it is important to know, which queries you are running. otherwise it is close to impossible to give you any real advise. however, there are two things you should know: UPDATE benefits nicely from a thing called FILLFACTOR. it can speed up things a lot. the order of columns does have an impact on the size of the table ...


1

The 3rd method does not have performance advantages. There are two reasons I can think of that one would be doing this: You plan to spread files to multiple disks later (move to option 1) You would have allocation contention with just one file. This is a common problem with high-load tempdb's. It is rare with user databases. Multiple files on the same ...



Top 50 recent answers are included