Got a MySQL server which is kicking ass? Post your stats here!
 

mx06# mysqladmin status;w
Uptime: 1297016 Threads: 41 Questions: 43956248 Slow queries: 65 Opens: 6954 Flush tables: 1 Open tables: 64 Queries per second avg: 33.890


Server Load:
last pid: 97012; load averages: 0.10, 0.08, 0.07 up 22+03:16:38 22:58:27
37 processes: 2 running, 35 sleeping
CPU states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Mem: 128M Active, 613M Inact, 196M Wired, 42M Cache, 111M Buf, 17M Free
Swap: 1024M Total, 40K Used, 1024M Free

33 queries average per second is heavy! i assume you're not doing anything mission critical with those tables using mysql :)

how many users a day?

33 / sec isnt that bad, im running at 28 / sec

Uptime: 78741 Threads: 217 Questions: 4893872 Slow queries: 1079 Opens: 41466 Flush tables: 1 Open tables: 64 Queries per second avg: 62.152

This is one I'm working on tonight:

Uptime: 11647 Threads: 46 Questions: 2877835 Slow queries: 1569 Opens: 589 Flush tables: 1 Open tables: 583 Queries per second avg: 247.088

We are currently testing MySQL 4 on this machine.

last pid: 2199; load averages: 0.05, 0.23, 0.43
152 processes: 1 running, 150 sleeping, 1 zombie
CPU states: 1.9% user, 0.0% nice, 0.4% system, 0.0% interrupt, 97.7% idle
Mem: 328M Active, 81M Inact, 67M Wired, 24M Cache, 61M Buf, 1128K Free
Swap: 512M Total, 63M Used, 449M Free, 12% Inuse

Uptime: 123366 Threads: 2 Questions: 5062890 Slow queries: 77 Opens: 19357 Flush tables: 1 Open tables: 64 Queries per second avg: 41.040 10:27PM up 79 days, 22:02, 2 users, load averages: 0.40, 0.32, 0.43

Uptime: 528705 Threads: 18 Questions: 13242906 Slow queries: 78 Opens: 749 Flush tables: 1 Open tables: 207 Queries per second avg: 25.048

Got any tips on optimizing MySQL ?

MrFiction, how's Mysql 4 performing ?

I'm not MrFiction, but anyway.

MySql 4 is performing very well. The query cache is very useful if you have DB driven websites. Be a bit careful with the FULLTEXT searches though, they can use much more cpu than one might expect.

MySQL 4 is great so far. With vBulletin, nearly half of the queries are now coming from the MySQL 4 query cache instead of the database. MySQL 4 also seems to have better support for InnoDB tables, if you use those.

The caching is the main advantage from what we've seen so far. When you do mostly updates, or where your selects are always different, it doesn't seem to make too much difference - but if you do a lot of the same select queries (like vBulletin), it's great.

We have one machine that is acting up right now, but I don't think (though I can't be sure) that it's MySQL 4 related.

very nice!

KD

$ mysqladmin status
Uptime: 767056 Threads: 235 Questions: 185825069 Slow queries: 8 Opens: 95 Flush tables: 1 Open tables: 89 Queries per second avg: 242.258
$ uptime
12:20AM up 8 days, 21:03, 1 user, load averages: 0.69, 0.47, 0.45

only modified to remove the password from the mysqladmin line. :)

Anyone who is a little more serious than "eminem's songs" knows that you have to include several things in these statistics

1. a profile or the queries executed: There are read queries, and update queries. There are one-row/indexed queries and table wide queries there are in-memory queries and cross-table queries
2. hardware profile and CPUs: There ae single P3 and dual Xeon's (or even "worse" :) ), there are 256MB servers and 8GB servers
3. Other things the server has to do: There are DB dedicated machines and sometimes the machine has to do other "stuff"

:2 cents:

Contact me on ICQ (9129246)
I'll give you a hand with that.

holy shit your right batman!

Indeed :)
Many people wonder why their DB server is not performing adequately while all this time they run a php or perl code that is very heavy and uses most of the CPUwhich in turn slows down MySQL into opening more threads which in turn fill up memory which in turn starts using more swap memory which in turn eats the IO and even more memory which in turn eats more CPU and so on and so forth.
So damn ye I?m right :thumbsup

I doubt anyone is going to post very specific information about their servers and databases on a public board, especially not GFY.

True :)
Though I didn't say they would or should, just said its not a serious debate/info without the following.
About MySQL 4.* - I wouldn't recommend moving to that on mission critical servers yet. True that query cache and subqueries are nice addition for read-intensive application, but its not yet marked for release so be patient. If one wants to test it go ahead.

My DVD Store/HostedContent.com runs off that DB server. There are about 4 machines which connect it over an ipsec connection.

The server is a p3 1ghz machine.

I've been testing MySQL4 + InnoDB the past couple of weeks and it blows MySQL3+INNODB out of the water. The data below is on a server running MySQL3+INNODB doing about 500k hits / day. Tuning high load servers is an art.

Uptime: 1761000 Threads: 63 Questions: 116919054 Slow queries: 0 Opens: 232
Flush tables: 1 Open tables: 221 Queries per second avg: 66.394

8:08am up 22 days, 23:08, 2 users, load average: 0.11, 0.19, 0.12

Comparing MySQL4 + InnoDB with MySQL3+INNODB with no more information as to what you do with the DB has little vlaue.
For example, for a same row concurernt-writes intensive application MySQL3+innoDB will kick MySQL4+INNODB out of earth without hickups

You have to understand the difference between the different table handlers and database engines before commenting on those.

innoDB is good for same row writes (solving row-level locking with multi-versioning) or even general write intensive (solving table locking) where myISAM will have hard time performing (even with Delayed inserts)
INNODB is better for read intensive application. disk/memory space consumption of myISAM is much better than innoDB and therefore takes less IO and memory which is crucial in big/huge applications. There are more table handlers such as MERGE tables and HEAP tables an BerkleyDB that should be considered when implementing big projects. Just saying "MySQL4 + InnoDB blows MySQL3+INNODB" should not be taken too seriously by others. No offense meant, but i'm just warning people not to run and turn their tables into innoDB.

Same goes for mySQL 4.*. One should understand the differences before deciding what to do. On one hand, version 4 added query caching, which is ONLY good for read intensive (writ scarse) applications (it can even be a little damaging for write intensive applications if not tuned appropriately). Other features are also depending on what you do - such as sub-queries. If general, mySQL 4 might be a little slower (on some core functions) than version 3. due to added complexity/advancements in code base, but that is marginal. Biggest disadvantage so far is the fact that version 4 is not yet defined as release (still in Gamma). Looking at the change_log you could see alot of bugs are being corrected (showing its still a new and little-buggy system).

As skazzel said, its an a little of an art to fine-tune a high-load server BUT chosing the right table handlers and designing the database/tables and code right is MUCH MUCH MUCH more important. The tunning is the last step. There are good enough rule-of-thumb guide as to how to tune a high-load server with the mysql docs.

:2 cents:

remember to patch up your mysql after this:

http://www.securiteam.com/exploits/5OP0G2A8UG.html

Relevant to MySQL version 3.23.53a and prior
Just upgrade to the latest stable (3.23.55)

Actually, you are wrong. InnoDB is faster than INNODB even when one is just doing selects, especially when doing selects on non-primary keys (see benchmarks on innodb.com if you don't believe me). Really, INNODB tables should be considered deprecated as of 4.0 - there is no reason to use them except for legacy purposes.

MySQL4 using InnoDB tables will be faster than MySQL3 and INNODB in 100% of real world situations. INNODB is just an evolution of the old ISAM format, which real DB's moved past in the 80's.

Though obviously, all of the tuning of DB/server paramaters in the world will not speed up an improperly indexed db.