New answers tagged

-1

C Drive in Azure is mapped to a blob .. hence its slow and should never be used for a DB / app just OS load. Options 1. USe D series note the D drive is now an SSD , you will need to configure the DB to use it. Option 2: Use DS series this allows you to buy premium storage and make the C drive SSD.


0

Your configuration will depend on the data you are serving, the usage patterns, the types of queries being run and of course ultimately the resources you have available. When tuning your my.cnf you want to ensure you don't allow your mysql/mariadb instance to use more memory than you have available or it will start swapping. If your server is not a ...


2

That is a new feature, part of the Galera cluster support. Galera replication is for InnoDB tables (MyISAM replication can be enabled but it is experimetal) and as it uses row-based replication, the tables should have primary keys to work right and fast. But you cannot just add them if the tables and applications were not designed to have them. You will ...


-3

You need to add more memory to this server and increase you max memory.


1

Yes, you are right. Memory is used by dormant databases I mentioned this in the following posts over the years Nov 13, 2015 : #1041 - Out of memory issue in mysql Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory? Apr 21, 2014 : Adding new tables -- memory usage increases Lots of tables ? Lots of columns ? Of course, that is less memory for ...


14

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? No. Service accounts do not affect performance in any way. Its all about security ! From the SQL Server 2012 security best practice whitepaper ...


7

The real problem with using one Service Account for all of your servers is: How secure do you want to be? If someone is able to hack that one account, then all 35 servers are exposed in one swoop. I much prefer at least a Service Account per server. And having several Service Accounts for different uses is also recommended for security. See: Configure ...


4

There is no negative performance impact to using the same account on all your servers. If a change happens to that account, however, it will impact all your services using that account. You may want to consider different accounts for different environments (e.g. DEV, TEST, PROD).


0

OK, after experimenting a bit further with queries and a set statistics time on, here's what I found: On a small IN-list (4-12 items) - ALMOST identical The performance is almost identical, the hardcoded variant uses a little bit more CPU, the overall "elapsed time" is either same or a little bit longer. On a bigger IN-list (150+ items) - subquery wins BY ...


1

I can think of a perfect case for it, and we have tested thoroughly and run it in production...I call it the "fast lane" clustering strategy: If you do read-write splitting with a proxy like MaxScale, or your application is capable, you can send some of the reads for those seldom invalidated tables only to slaves that have the query cache turned on, and the ...


3

Have you considered: Ensuring your database isn't auto-growing. The fact that it is slowing down after 30-40 million rows could be caused by auto-grow. For loading, try bcp in/out instead, and set database to bulk-logged recovery model. Just be aware of the pitfalls with this recovery model, in the case something bad happens to the db during this time. ...


1

InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it ...


0

I recently wrote the article about this and maybe someone can find it useful. How to handle excessive SOS_SCHEDULER_YIELD wait type values in SQL Server I tried to provide not only multiple aspects of troubleshooting SOS_SCHEDULER_YIELD wait type but also to give some details on SQL OS Scheduler functionality, as it is crucial for understanding ...


0

Note: I posted this answer to a related question on stackoverflow. This solution is Linux and Systemd-specific, but in fact, can be adapted to any system that properly supports memlock calls and provides the capability to do so for processes that don't stay root. Update: This solution might not, in fact, work that well. See note at end. There exist a class ...


0

Apart of the very good advice given by Rolando, you can, on the system side, activate a swap-less setting using sysctl. I usually set vm.swappiness=10 on MySQL machine in /etc/sysctl.conf. It gives restricted access to the swap, but permits it if required. The default value of vm.swappiness is 60, which is very permissive.


0

Rolando, Thanks a lot for your explanation. I followed disc size and file access during this ordeal (46 hours, completed this morning) and yes .. even in MySql 5.6 this is what happens. My statements were: CREATE INDEX idx1 ....; CREATE INDEX idx2 ....; CREATE INDEX idx3 ....; ... and I saw in the "show processlist" how all the data was first copied to a ...


0

You must be doing something odd with your DDL or you have a very, very old version of MySQL. MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago. Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum) May 12, 2011 : Does MySQL still handle indexes in this way? (...


3

Performance issues can stem from a wide variety of issues - the size of the table is only one factor. Things such as indexes and the design of the query are just as, if not more important than, how well something performs. For example, take a look at the advice on this Stack Exchange question: Slow Performance Inserting Few Rows Into Huge Table Once your ...


24

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored. Physical storage is identical whether the column is ...


7

This is going to read like a paranoid's answer, but there aren't only storage and performance considerations. The database itself doesn't control its clients, and clients can't be assumed to always securely insert user input - even if a database is designed to be used only with a .net application that uses Entity Framework to encapsulate transactions and ...


0

"Millions of columns"? Or millions of rows? For single user applications and just a few megabytes of data, sqlite can be perfectly sufficient, so it will really depend on how many lines of code/rows of data you'll be dealing with, and the complexity of the queries that you'll require. You may want to consider postgresql or mysql from the get go, to avoid ...


0

What makes you think your session is waiting? When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting. V$SESSION SECONDS_IN_WAIT If the session is ...


0

You can query the dynamic performance view V$SESSION_WAIT Like the following: select sid, seq#, EVENT, WAIT_CLASS, SECONDS_IN_WAIT from v$session_wait where sid=<SESSION_SID> Take a look at the 'EVENT' field.


0

You should denormalize the tables as such a normalization is leading to too many inner joins and multiple nested select statements. If normalization is very necessary try maintaining another table. As the number of images is low (~700), you might just skip the image_to_tag table. Otherwise, if the number of tags per image is very low (3-4), try keeping ...


1

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.


0

How many unique values do you have for crawled_name? How feasible it is that you'll hit an apartment_id value greater than 2 billion (in the next 5-10 years)? Note that COUNT(id) is the same as COUNT(*) (because id is not nullable), and that COUNT(apartment_id) > 10000 is effectively COUNT(*) > 10000 along with WHERE apartment_id IS NOT NULL. What you'...


1

(post_type, post_modified_gmt, post_status) is 'optimal' because it is (1) covering, and (2) ordered in perhaps the best way, and (3) the table is MyISAM, yet this index is much smaller. MyISAM is a bad choice for a table with a lot of bulky (TEXT) columns. InnoDB would probably run faster. MyISAM is prone to fragmentation. If there has been a lot of ...


1

There are many open ends in your question, but partitioning by customer could to be the way to go - especially if: you expect many customers, each of them could have tons of data ("tons" means much more than RAM cache size), most of their datasets will be mutually exclusive (each customer sees different subset of data). RULEs or triggers are a ...


1

I confirm that a full or incremental backup during business hours can make your database inaccessible to end users and web services. Factors that can make things worse: allocating too many channels to RMAN using an out of support version like Oracle 10 having a database that is CPU or memory or network IO bound running Dataguard at the same time ...


3

That depends on your disk subsystem. A full backup is going to involve a metric crud-load of I/O (that is a technical term). That is going to put a huge load on your I/O subsystem. Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a ...


0

I had a very similar issue in Oracle 12c. In experimenting, the PRECOMPUTE_SUBQUERY optimizer hint 'forced' the subquery to execute first, thus making use of the defined indexes. In your snippet, that would look like this: ... where t.ObjectId = 12345 or t.Id in ( select /*+ PRECOMPUTE_SUBQUERY */ TaskId from ObjectAffectingTasks where ObjectId=...


0

I know of no such limit, although I have never had more than tens of materialized views, not thousands. However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column. That way, Postgres will be able to pick out the relevant parts of the view very quickly. You ...



Top 50 recent answers are included