#mysql
25 October 2007
Total 27 pages. You are browsing page 1/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Thu Oct 25 00:00:38 2007
--- Day changed Thu Oct 25 2007
00:00 <****> performance question - should I be using more than 2meg of the key_buffer_size for a site that has literally gigs of databases?
00:00 <****> 2MB?
00:01 <****> nils_: mysqlreport shows that I m using 2.10M of 128.00M (I just set it to 128 earlier after reading some stuff that led me to believe that my old setting of 4meg might have been a bit small).
00:01 <****> BrotherJack: yeah that sounds better
00:01 <****> nils_: the querye_cache thing seems to have had a minor impact, but definitely not night and day faster.
00:02 <****> BrotherJack: well sometimes it's just locking or bad indexing...
00:02 <****> I didn't know much about mySQL however after reading, I think it's a very interesting company
00:03 <****> BrotherJack: Depends what engine you use, key_buffer is only used by myisam
00:03 <****> ki77a77: I wonder if that's what i want to do :/. What I've tried so far on my own. Is to pick out the id for my first table which stores personal info. In my other table I store info about each day for whic makes for a whole trip (so the days may vary from ~2-10) between these tables I have a bridged table whick hold personal_id then the day_id.
00:03 <****> i've setup mySQL enough, but didn't realize it's role as a company
00:04 <****> MarkR42: MYI files indiate myisam, do they not?
00:05 <****> Yes, but are your tables exclusively myisam?
00:05 <****> MarkR42: how would I tell?
00:05 <****> MarkR42: all of them are MYI/MYD/etc files....
00:06 <****> are you using indexes?
00:06 <****> Typically you look at your schema create script, and if they say ENGINE=MyISAM, then they're myisam
00:07 <****> chadmaynard: insofar as the various web apps on this server take care of their own business.
00:07 <****> Or if they say ENGINE=Innodb etc, they are something else
00:07 <****> ENGINE=Blackhole;
00:07 <****> :)
00:08 <****> MarkR42: at least the app in question that is awful slow I see ENGINE=MyISAM
00:09 <****> Trifler: you need to do some reading on the concept of normalization. This involves efficient table design. Here's an example of when you might use a third link table. One table for customers, one table for store items, and a 'middle' table for a shopping trip. Your 'middle' table might hold customer id, shopping trip id, and product id.
00:10 <****> start learning normalization with N:N relationships
00:10 <****> BrotherJack: you need to reproduce the performance problems in your development server
00:10 <****> Then look at options to fix it, test your application etc
00:11 <****> MarkR42: it's not my app, it's a third party app.
00:12 <****> Right, well in any case you should get this 3rd party app installed on your performance test environment and generate load fo rit
00:12 <****> To the point where you can reproduce the problem; this will probably involve loading production-size data
00:13 <****> You might have to build some tools, and/or get other third party software to test it
00:13 <****> MarkR42: I don't have those kinds of resources. I have a Linux box that has 1/10th the horsepower of the production server that I use to test/debug stuff.
00:13 <****> ki77a77: The problem atm is that I don't have time :)
00:14 <****> That's really not very adequate, but you might be able to get away with it; can you load production-size data into it?
00:14 <****> MarkR42: I can duplicate the software environment, but not the hardware at all, not even close.
00:14 <****> MarkR42: it is a mirror of the production server, other than the fact it's not getting hammered with 40 queries a second like the production box is..
00:14 <****> What about data size and load? do you have any load generators?
00:15 <****> MarkR42: no
00:15 <****> You can't measure performance unless you hit it with a workload that is as similar as you can easily get
00:15 <****> If the box is below spec, you can generate a smaller load until its utilisation is similar
00:15 <****> MarkR42: how come there is some issue with poking at the production server to see what it thinks of the real load, which is what I'm trying to troubleshoot?
00:16 <****> Poking a production server is an exceptionally bad idea
00:16 <****> Especially when you have an SLA
00:16 <****> MarkR42: I don't have an SLA to meet, and if I did, the production server is currently not making the grade, as page-generation times are in the 20+second range.
00:17 <****> I'd say- start monitoring thigns very carefully - enable the slow log. Enable as much monitoring as you can easily add without creating too much additional load
00:18 <****> MarkR42: in other words, I have no problems working with the real problem in production. I have excellent backups in case I wreck something.
00:18 <****> Having some monitors which do a SHOW STATUS periodically and compare it with the previous one is a Good Thing
00:18 <****> Compare these stats with what you see in your dev env
00:18 <****> BrotherJack 20s+???
00:18 <****> omg!
00:18 <****> I am assuming that an individual query doesn't take long in dev box?
00:19 <****> Normally the culprits are either poor query design or contention (e.g. other threads locking, but that may be due to IO limitations etc)
00:19 <****> MarkR42: no, the queries don't look to be taking all that long, but if each individual query takes 0.4 second, and the wing-nuts who wrote this web app have no respect for what 50 queries per page-load might do to a system, then that's what I think I'm looking at...
00:20 <****> If you can take a look at the slow query log, you can get the "slow" queries off on to the dev box (with production-size data) and EXPLAIN them
00:20 <****> I've got no queries in the slow query log.
00:20 <****> BrotherJack you need to check those queries, log slow queries, explain them, fix them, and cache them
00:20 <****> In which case reduce the slow log time
00:20 <****> If the problem is at the application layer, try to fix it there
00:21 <****> If it is a third party application, take it up with them and try to get them to produce a better version
00:21 <****> I understand that you will have to go through their support channels and get some of their developers to work on it in a future release
00:21 <****> But it's a 3rd party product, they should be fixing it.
00:22 <****> In the meantime, you're probably going to have to throw hardware at it
00:22 <****> HW isnt going to help...
00:22 <****> he's got 40 queries/s
00:22 <****> 40 queries/s isn't that many, our production server has 150
00:22 <****> hardware always helpa!
00:23 <****> If you cannot realistically fix the software (because it's third party), then you're going to have to throw h/w at it
00:23 <****> MarkR42: good luck. These are the same guys who consider it a "bug" in PHP that when you call require(filename) it eventually works out to a flurry of stat() calls. This is an issue, because in their 'backup' routine, they call a require_once(filename) literally several million times due to the way their loop runs..
00:23 <****> Surely require() should only be issued a few times per page, and never in a loop
00:24 <****> I have written some large(ish) PHP applications, and it might require 20 files, but only once each.
00:24 <****> MarkR42: this is a quad Xeon, many gigs of ram, UW-SCSI raid, etc...
00:25 <****> Yet the obvious way forward is to split it out into a web farm, if it's currently on a single box
00:25 <****> MarkR42: I'm with you 100%. It's just an example of the coding practices of the guys who wrote the app in question.
00:25 <****> Right, but if you're unable to get it fixed...
00:25 <****> I wonder why you're paying for such a shoddy app, or is it just legacy?
00:25 <****> 40/s is nothing!
00:26 <****> MarkR42: open-source. It's one of those niche things for which we have found no better alternative, commercial or otherwise.
00:26 <****> if it's open source, if you've got in-house resources, you should be fixing it yourself, and ideally trying to get your patches accepted by the maintainers
00:27 <****> MarkR42: but still, this slowdown is a progressive thing that has gotten worse of late - and I find myself restarting MySQL to get things back online about twice a week now.
00:27 <****> Do these 20sec requests take 20sec on your dev box?
00:27 <****> MarkR42: which is why I'm looking into what might be done with MySQL to make it perform at least as best as possible under the circumstances.
00:27 <****> does mysql have a log besides binary log?
00:27 <****> Have you worked out if it's contention, poor queries etc?
00:28 <****> MarkR42: yeah, but dev box is an old PII 800mhz, and production is, as mentioned, a pretty serious piece of hardware.
00:28 <****> Get your boss to buy you some decent development hardware
00:28 <****> MarkR42: and it's not individual queries that are taking that long, it's the big pile of queries that get called per page-load that are collectively taking a long time.
00:28 <****> Right, but WHY are they taking so long? Is it that they're tricky queries, or is it contention?
00:29 <****> have you EXPLAINed them all?
00:29 <****> its because your hardware is garbage
00:29 <****> MarkR42: how would I answer that question?
00:29 <****> Using EXPLAIN, also looking at the stats in SHOW STATUS, using profiling tools on your dev box, etc
00:29 <****> MarkR42: especially the contention part.
00:29 <****> and you're not caching them
00:29 <****> Contention is tricky - you can of course repeatedly type "SHOW PROCESSLIST" or use a tool which does so
00:30 <****> You can also look at the locks_waited in "SHOW STATUS"
00:30 <****> MarkR42: man, I'm just an old systems geek, I know enough about MySQL to create/drop/grant/etc - but I am an no way a serious database junkie. I
00:30 <****> MarkR42: you're proposing stuff that looks like it would take me days and weeks just to figure out how to get started on.
00:30 <****> Have a look at the system's load pattern - is it 100% on CPU? All maxed out on IO? what are the threads doing?
00:30 <****> threading.
00:31 <****> needles
Total 27 pages. You are browsing page 1/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last