Hot answers tagged

8

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable. With a filter on increment >= 200 CREATE EVENT SESSION [locks_lock_waits] ON SERVER ADD EVENT sqlserver.locks_lock_waits( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[is_system] = 0 ...


6

Every time you scan a big index (or a big table, if you prefer) that data has to be brought from disk to memory. When this happens, if the buffer pool is holding data from other objects and does not have enough free space to accomodate the pages you are reading from disk, something has to be discarded. Low PLE means that this process happens too often, ...


5

If you are interested in locking, there are several extended events available: lock_acquired lock_released lock_escalation The first two events have a duration column in (microseconds) which you could filter on for your thresholds. They also have a resource_description action which will give you some detail on the resources involved. The lock_escalation ...


4

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


4

Idea 1 Judging by their names, the columns "denormalizedData" and "hugeText" seem to be comparatively big, probably many times as big as the columns involved in your query. Size matters for big queries like this. Very big values (> 2kb) for text or jsonb get "toasted", which can avert the worst. But even the remainder or smaller values stored inline can be ...


3

even though I have 100gb of ram memory. What version and edition of SQL Server are you using? 2012 (and earlier) standard edition will only use up to 64Gb so if you are using those there is little point adding more memory in this situation. Even in later releases the limit is 128Gb for standard edition. Of course if you are running enterprise then such ...


2

Since batch inserts and LOAD DATA are much more efficient than lots of single-row inserts, your request is backward. Rethink the real goal, and how to express it.


2

You are correct that PostgreSQL cannot currently use one index to provide selectivity and another index to provide order, in the way you want. Adding this feature has been discussed, but I don't think anyone considers it a high priority. Creating a multicolumn index will not help in any way. From my testing Postgres will not change its query plan at all ...


2

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.) Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


2

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. Do nullable columns occupy additional space in PostgreSQL? A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns ...


2

In your case I think you could consider the use of a Bit String Type data type. For instance, something like: CREATE TABLE yourtable ( booleans bit[5] default B'00000', ... other fields ... ) It is efficient in terms of memory and does not require the use of a complex type like a PostgreSQL array (actually it is a bit array), and more, you do not ...


2

What makes you think that there is a viewed table like yours on stackoverflow's database? Raw logs are very costly to maintain on a relational database, and on high-traffic websites, caching has a very important role, and it is almost impossible to conserve full logs of everything (they are summarized). For Wikipedia, for example, we maintain a complete ...


2

Indexes with the columns in the order given: actions: INDEX(user_id_to, user_id_from) -- 'covers' first query actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query Either index will be useful for 2nd query. DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions. For various reasons, it may be better to ...


2

Apart from Existing answers,you will need to know how RAM is utilised by SQL Server. Suppose you have a box with 100 GB RAM dedicated for SQL Server only.SQL Server will utilise all this RAM until you constrain it and lets say you constrain SQL Server maximum memory to 94 GB... Lets understand how this 94GB memory is used and what are the main components ...


1

It's always a tough thing to performance test production systems, companies typically have far lesser systems/hardware for QA/Staging/Dev environments. One method I use is to baseline performance of our QA system before implementing SQL changes, and then check performance after. I am not concerned with the actual times, but the delta of before and after. ...


1

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect Create an init_connect script that writes the user's session info to a table. A better way would be to configure MySQL to log user activity to a table, as in Rolando's answer to this question: Audit logins on MySQL database


1

There's a lot of choices out there: Percona Monitoring plugins for Zabbix, Cacti, Nagios Percona Monitoring and Management Monyog Severalnines Clustercontrol Equally capable but not on-premise (afaik) is Vividcortex.


1

Get MonYog through MariaDB. https://www.webyog.com/product/monyog Note: anything that works with MySQL, MariaDB, or Percona, will probably work well with the others. I looked at lots of tools, but came away thinking that most graphs were measuring either stuff that never changes. My favorite tool metric is the number of slowlog entries. If that goes up, ...


1

I think the answer will vary depending upon what you want to achieve. As you have mentioned that these things need to be acted on analysed, reported & displayed then I would consider that having the data in tables would be advantageous. The decision to calculate summary data vs calc on the fly depends upoon size volume and scalability and accuracy ...


1

It's better to write such preventive measures in Application layer, database layer may give you short term benefits. But it's Application layer which can cater your all demands of new logic in future.


1

Case 1: An extra table or database will eventually be pushed out of cache. It is harmless. Cure (but not worth doing): restart mysqld. Case 2: The 'system' is building thousands of tables and/or databases. Each table/database involves at least one entry in the containing OS directory. As this directory gets fuller, the OS operations to access files/...


1

innodb_log_buffer_size should not be set to 4G. The default of 8M is usually sufficient; let's compromise on 100M, ok? case when exists ( select ... ) then ( select ... ) else 0 end --> coalesce( ( select ... ), 0 ) "Creation time" comes out either as YMD format or DMY; did you want that inconsistency? Indexes needed: INDEX(classDesc, LookupId, ...


1

Like @djb mentioned RMAN is the safest bet, but at times it won't be the fastest solution. There are other ways, copying database manually comes to mind. It definitely isn't the easiest way and won't be usable if You can't put source database offline for some time. Pros: might be faster than RMAN Duplicate - it depends how much time has passed since your ...


1

Another option is to use ANY. SELECT * FROM twitter_personas WHERE twitter_user_id ANY (VALUES ($1), ($2), ($3), ..., ($25000)) I ran a query with 1000 parameters and it took 4 minutes using IN and 1 second using ANY. You can also use ANY ARRAY Rather than ANY VALUES but from my reading this is slower than ANY VALUES.



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