Tag Info

I'm not sure how your previous .my.cnf used to work, and I actually have never used these files before (mainly because I didn't know about them). So after a bit of research, I found this link and came up with the following ~/.my.cnf that worked for me: [clientdbid] password = mypass database = dbname host = server.location.com and the command that reads ...

Here's another alternative formula in sproc form: DELIMITER // CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4)) BEGIN SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + ...

Going to post this as an answer, with the relevant information. The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM - Global Buffers) / Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE '%buffer%'; Here's a list of the buffers and whether ...

Benchmarking or Tuning tool? IMHO, there's no tool that will be specific to the latter unless you have a super generic usage. You need to identify your usage pattern and tune your database hosts to accommodate. If you're write-heavy, you will have a different configuration than a read-heavy scenario. Bottom line, your tuning follows your applications usage. ...

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

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

I think MONyog can handle some of your request. MONyog MySQL Monitor and Advisor is a "MySQL DBA in a box" that helps MySQL DBAs manage more MySQL servers, tune their MySQL servers and fix problems with MySQL database applications. MONyog not only finds problem SQL it has 200+ monitors and advisors as well which suggests what parameter you ...

While I will never contradict nor intentionally dis MrDenny in any way (too much respect for his knowledge and contributions to the community) I've noticed the MS documentation recommending turning auto update stats off. At Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) MS recommends setting it off. I've also read the ...

In the days of old (SQL Server 2000) having the auto-update stats setting on could result in large "pauses" in OLTP applications when SQL decided to do a statistics update. From SQL Server 2005 onwards there is an asynchronous option which will not result in the "pause" when stats are out of date and subsequently recompiled. The stats will be recompiled ...

How active are the systems? If they are mostly read systems you might get by without updating, if they happen to do that manually when they are changing data (insert, update, delete). However for best practice it is advised to be kept on cause I think it would be rare for it to be a performance issue. I would probably send them articles and blog post ...

I would try lowering your buffer sizes. Making them as large as you have them is going to cause problems. How much memory do you have available to run these values: query_cache_size=1024M myisam_max_sort_file_size=100G myisam_sort_buffer_size=10G key_buffer_size=5000M bulk_insert_buffer_size = 4000M read_buffer_size=8000M read_rnd_buffer_size=8000M ...

Given this is a Windows installation, @DTest still provided the initial proper direction. Apply the following formula: Most people use this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections I prefer this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + ...

A quick way to determine how much memory MySQL thinks it could allocate is as follows: wget mysqltuner.pl perl mysqltuner.pl When you run this script, it will tell you what percentage of the installed RAM MySQL thinks it can safely allocate. If the answer given is over 100%, you definitely need to lower your buffer sizes. The main one to focus on are: ...

Quest has a database benchmarking tool. You can run a synthetic TPC-C, H, E test against your MySQL database. It will not suggest configuration changes, but it does make testing those changes a bit easier. Benchmark Factory Community

You can use Spotlight a Toad application Spotlight® on MySQL diagnoses MySQL problems in real time by graphically displaying all database activity in an intuitive user interface, enabling you to respond quickly to issues that need attention.

High CPU --> queries/indexes/schema needs fixing. Period. Tuning won't help. Period. Also, the SHOW STATUS is of little use, since it applies to the SESSION. Do SHOW GLOBAL STATUS instead. table_cache = 150000 That's excessive (as I said on forums.mysql.com)

mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status. These links can be helpful to you Running multiple instances on the same host http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html

MONyog proactively monitors all MySQL servers using a set of predefined expert advisors, to identify and alert DBAs of problems, security vulnerabilities and tuning opportunities so they can be acted upon well in advance of a problem or outage occurring. Also, MONyog finds problem SQL by these methods Querying MySQL Proxy Analysing General Query Log ...