| Posted by hypebeast, 06-25-2009, 11:11 PM | | What would be the best settings for an InnoDB with 10gb of data and almost every second, everything is being selected, updated, inserted and much more?
Here is the currently one I have.
How can I improve this?
thanks
|
| Posted by mattle, 06-26-2009, 06:54 AM | | Start by reading the comments in the file itself. A lot will depend on your available memory on the machine:
I would start by setting the innodb_buffer_pool_size to 80% of your physical memory as recommended I would also jack up the query_cache size. If you're having that much access, I imagine there's a lot of redundant queries. You can see how many query_cache hits you're getting opposed to select statements with 'SHOW STATISTICS'. I'd also recommend this patch for more advanced statistics tracking-- http://www.extsql.com/store/down.php...h=x86&conf=5.3
Last edited by mattle; 06-26-2009 at 06:57 AM.
|
| Posted by hypebeast, 06-26-2009, 12:32 PM | | so say i have a 4gb machine.
i can bring it up to say 3000M?
|
| Posted by AquariusStorage, 06-26-2009, 12:38 PM | | http://wiki.mysqltuner.com/MySQLTuner
|
| Posted by hypebeast, 06-26-2009, 02:31 PM | | does mysqltuner work for windows server?
|
| Posted by cselzer, 06-26-2009, 03:19 PM | | I followed the link, and saw another link for "system requirements"
http://wiki.mysqltuner.com/MySQLTune...m_Requirements
|
| Posted by hypebeast, 06-26-2009, 05:53 PM | | guess it doesn't work for windows 2003.
|
| Posted by fog, 06-26-2009, 07:04 PM | | As has already been said, there is no "best" number. On my personal machine, I set MySQL up to use a 4MB query cache. It's too big but it has an excellent hit rate. On a machine at work, we gave the innodb_buffer_pool cache 15GB.
As an extremely high-level overview, what you want to do is let MySQL run for a while and use a tool to look at how full the various caches and buffers are, and what the hitrate is. The MySQL Tuner script mentioned is great at that. (Do you have access to a local Linux machine? I think, but wouldn't swear, that you can run MySQL Tuner on a Linux machine against a remote MySQL server, in which case the MySQL server's OS is a moot point.)
You can run "SHOW VARIABLES" and "SHOW STATISTICS" and do math by hand to compute various ratios. I found MonYOG to be a really handy tool; it has a 30-day trial. It, too, will connect to a MySQL server (don't install it on the server itself) and calculate various statistics. It can give very good insight into what you might do to tune it.
I'm curious about why you're using Windows. If you're an all-Windows shop, or you're way more comfortable with Windows, that's fine. Don't run Linux just for the sake of doing what most other people do. But if there's not a compelling reason, MySQL on Linux is a much more popular pairing, and I've found that running what other people run makes getting help far easier.
What is meant by "almost every second, everything is being selected, updated, inserted and much more?" Are you literally selecting 10GB of data every second, or were you just trying to communicate that the server handles lots of queries?
Do you have any sorts of monitoring? I have Cacti set up to monitor all of my MySQL boxes, so I can view query volume, connections, etc. over time. This can be extremely valuable in quantifying what is happening.
How did you arrive at the numbers currently in your configuration file? They all seem very bizarre to me. Sometimes that points to someone with incredible brilliance setting things to unconventional, but perfect for your use, values. Other times it means that someone just made up random numbers. A few specific thoughts, though:
At least on Linux, MySQL 5.0 has a limit of 16,384 connections. "SHOW VARIABLES LIKE 'max_connections';" ought to confirm whether this is the case. But I want to note that, when I ran into that 16,384 limit, it was because things were horribly broken. 16,384 concurrent connections to a database server is not normal. In many cases, each instance of your application will just keep one connection open (see "persistent connections").
On mine, with default values, I'm at 1024. It seems odd that someone would lower this, unless there was a very specific reason to do so?
Similarly,
Mine, out of the box, is 2M. It can make sense to drop this number in some cases (like if you have lots of concurrent connections), but how did you arrive at 703K for a number?
I'd highly recommend you try something like MonYOG or MySQL Tuner (on a Linux machine) and follow its recommendations. The "right" settings are those that are optimized for your use, and none of us can tell you that.
|
| Posted by Nex7, 07-01-2009, 12:01 AM | | Ah MySQL Tuner; I remember when this was new and we were using it mostly internally (was working at Rackspace at the time), when it was like tuning-primer or something like that, written by Monty.
If it's still like it was then, bear in mind MySQL Tuner ITSELF has to be run FROM a Linux machine, but AFAIK it is gathering all of its information via mysql; it should be able to run against a non-local MySQL server. You just need access to a Linux box that has network access and a full user account on the MySQL server.
|
|
 Add to Favourites
 Print this Article
|
