Tag Info

Hot answers tagged

21

As I answered here, you can add a section for each user/host/db you connect to using the syntax in your ~/.my.cnf: [clienthost1] # Note: client + host1 user=myuser password=mypass database=dbname host=server.location.com Once this is in your user's .my.cnf, you can utilize it by doing this on a command line: $ mysql --defaults-group-suffix=host1


17

The best way to do is -- use coreinfo (utility by sysinternals) as this will give you a. Logical to Physical Processor Map b. Logical Processor to Socket Map c. Logical Processor to NUMA Node Map as below : Logical to Physical Processor Map: **---------------------- Physical Processor 0 (Hyperthreaded) --**-------------------- Physical Processor 1 ...


11

My advice: Leave 'auto update stats' on (until you run into a very good reason not to) - you don't want a big delete in middle of a day to throw off query plans until the next time you run maintenance. However, schedule index maintenance/update statistics at a quiet time. sp_updatestats will update all stats for all tables in a database for you, but that ...


11

You can cycle the database by doing: alter database YourDatabase set offline with rollback immediate; -- this line will rollback ongoing transactions go And then to bring the database back online you can do: alter database YourDatabase set online; go This is a way to "restart" the database itself directly within SQL Server, without bouncing either the ...


10

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...


9

When setting MAXDOP you typically want to limit it to the number of cores in a NUMA node. That way schedules aren't trying to access memory across numa nodes.


8

In a read-heavy, low-write environment on a RAID5, I would just leave that to one's budget, tolerance, and blood pressure. In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB. Think of an InnoDB's table interaction. InnoDB If you do not use innodb_file_per_table, OMG ...


8

This is not a technical problem, it's a contract management problem. Don't frig with the turnkey application. It gives the vendor a get-out-of-jail-free card that allows them to ignore their service level agreement. Either, Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or Talk to whomever manages the relationship with your vendor. ...


8

You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach ...


8

MyISAM Key Cache You said you have key_buffer_size at 8GB. Question: Do you really have 8GB of MyISAM indexes? Please run this query SELECT KBS/power(1024,0) KBS_BB, KBS/power(1024,1) KBS_KB, KBS/power(1024,2) KBS_MB, KBS/power(1024,3) KBS_GB FROM ( SELECT SUM(index_length) KBS FROM information_schema.tables WHERE ...


8

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more ...


8

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck. SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks. If you're getting SSDs for a DB ...


7

The only time I've turned off auto-stats was when I used some undocumented commands to create some fake stats which I didn't want the system to wipe out as data was being loaded. This was a VERY edge case.


7

I've only seen it once with an bad app that had badly indexed heaps and had heavy ETL.This was rubbish and luckily not mine. Otherwise, there is no reason. If you are getting statistics updates at inappropriate times then it means you are doing incorrect index/stats maintenance or have massive deletes/loads that hit the threshold. With SQL Server 2005+ ...


7

Conventional wisdom about RAID doesn't apply well to SSDs. They don't really need striping (RAID0). They are prone to failures by-design, but RAID-1 is usually not the right answer for SSD for two reasons: a) is wasteful, halves the capacity of the SSD array (and they are pricey) and 2) SSDs failure characteristics leads towards both drives in the mirror to ...


7

The standard approach of separating the random IO patterns for data from the sequential of logs simply doesn't apply on SSDs, so I'd choose your option 1 with caveats: Backups MUST be to a separate machine. There is little point in having backups that you can't access in the event of the server going up in smoke. There is some value in separating the data ...


7

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by: it did not make the actual MySQL setting changed I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine): The global variable change ...


7

The obvious risk from making this change is that vendor queries that previously ran correctly could start to throw errors, or return incorrect results. The ARITHABORT setting partly controls whether arithmetic overflow and divide-by-zero errors return a NULL result, terminate the statement with an error, or terminate the batch with an error. How the vendor ...


7

Do: netstat -an|grep 3306 | grep LISTEN If something similar to the following line is returned: tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN .. it means that it's listening on all interfaces. If something similar to the following line is returned, and no other lines: tcp 0 0 127.0.0.1:3306 ...


7

As a general rule, use higher DOP for an OLAP system, and lower (or no) DOP for an OLTP system. Many systems are somewhere in between, so find a happy medium that allows the occasional large workload to get enough CPU to complete quickly, without strangling your OLTP workloads. Also, be careful about using the cpu_count column to get a core count. If ...


7

The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied... which leaves you with a 0 that's equivalent to 0. mysql> select 'foo' = 0; +-----------+ | 'foo' = 0 | +-----------+ | 1 | +-----------+ 1 row in ...


7

Under Windows this information is stored in the Registry under the key: HKEY_CURRENT_USER\Software\pgAdmin III In Linux systems I would expect some file in the user home directory.


6

At least three times on the sp_configure page, you have "Setting Server Configuration Options". In the table for the row "config_value" Under "Advanced Options" At the bottom This page also shows what happens if you run sp_configure with a blank @configname If not specified, the complete list of options is returned. This applies from SQL Server ...


6

We've all been there. Those that haven't are going to run into this situation one day! There is a text book approach to dealing with third party horror software: Prove there is a problem. Identify and document crazy queries and data access patterns. Approach the vendor with your evidence of a problem. If you can show that a re-write of a query shows ...


6

By setting innodb_flush_log_at_trx_commit, you risk confusion with mysqld/OS interoperability. I say this because the OS is being trusted to perform the flush. Note the caution in from the MySQL Documentation Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though ...


6

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event ...


6

There is no log of when this was changed. By default, and for 99.9% of systems this should be set to the default value of 0 so that SQL Server can manage this by itself. Set it back to 0, restart the SQL Server instance and be done with it.


6

The log vs. data separation is sound advice, but the devil is always in the details. A write intensive workload that commits frequently requires the log stream to flush to disk as fast as possible. This in turn requires a sequential write pattern, as the log does, undisturbed by any other operation. Hence, isolate the data writes (and reads!) from the log ...


5

What do you need to take into account when migrating SQL Server to another domain? The steps below presume 1) IP address will also change 2) SQL Server is NOT clustered A. Backup: BEFORE: backup the datases off-machine B. Services: BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL ...


5

You say that everything is fine, then after a couple of weeks, performance drops. (Usually, people claim that performance drops quickly, or at specific times, or at seemingly random intervals. That could mean bad I/O performance or lock storms or cpu-intensive queries running at wierd times, or a heavyweight scheduled job or lack of indexing or bad stats ...



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