Tag Info

Hot answers tagged

15

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] 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


10

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 ...


9

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 ...


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

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

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 ...


8

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

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

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

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

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 ...


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 ...


6

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+ ...


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

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 ...


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

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 ...


5

In general, no. A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file. Depending on the situation, it might be easiest ...


5

Update As Aaron points out, there is the chance the copy-and-truncate can miss some entries. So the safer method is to move and FLUSH. Original This article has the basic principle to rotating the slow query log that I use. Basically you need to copy the slow log to a new file, then truncate the contents of the slow.log: cp log/slow.log log/slow.log.`date ...


5

Knowing this is 64-bit SQL Server 2008 Enterprise simplifies the discussion, so thanks for that. The first thing to say is that it is almost never right to leave the max server memory configuration option at the default of 2147483647. The max server memory option only limits the size of the buffer pool (used for all single-8KB allocations pre-2012). SQL ...


5

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 ...


5

Speaking from experience which is humble but I think worth sharing, the major bottleneck with SQL databases (Sybase and SQL server here) is storage. But I think it's only fair that someone first benchmarks their setup before making any wrong assumptions. In my case, CPU usage has never risen high enough to justify upgrading the CPU any time soon. Instead, I ...


5

If you concern is about performance and you don't care about loose a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables: innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb) innodb_log_file_size - choise a good value in here to optimize the i/o in your slave ...



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