#mysql
25 October 2007
Total 27 pages. You are browsing page 2/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
00:31 <****> MarkR42: No, system load is pretty light usually. However, MySQL is typically hammer 40% or so of one of the four CPU's.
00:31 <****> So is the IO very high?
00:31 <****> is io-wait quite a lot?
00:32 <****> typical average 141kB traffic, 30-40 queries per second, and about 33% connection usage (33 out of 100).
00:32 <****> But is IO wait on the box high?
00:32 <****> If you mean disk I/O, it's very snappy. If you mean MySQL I/O, I don't know how to check that.
00:33 <****> run iostat -dkx and check %util
00:33 <****> I mean, what % of the machine's time is spent io-wait ?
00:33 <****> err iostat -kdx 1
00:33 <****> Are your data too big to fit in ram, BrotherJack ?
00:33 <****> MarkR42: shouldn't be - I've got less than 4 gigs of total databases (all combined for the whole system), and I've got more than that of RAM.
00:34 <****> But other tasks are running on the server? It's not a dedicated db server?
00:34 <****> How would I check time spent in io-wait?
00:34 <****> Badly configured Apache can use loads especially with PHP etc.
00:34 <****> BrotherJack you have no slow queries? and a page takes 20s to load?
00:34 <****> MarkR42: no, it runs a variety of other things - but everything else combined uses less CPU than MySQL.
00:34 <****> But they could push its DBs out of disc cache
00:34 <****> Trengo: no, no slow queries.
00:35 <****> BrotherJack i got mine to log at 1s
00:35 <****> are you sure you have no slow queries?
00:35 <****> My guess is that either the IO is maxed out (which is limiting mysql's performance), or that there is contention between threads for access to a few tables whcih are used very often and updated a lot
00:35 <****> Trengo: positive. I'm pretty ignorant, but I do know what 'STATUS' does. :)
00:36 <****> ~aprox how many lines of code is the core mysql engine?
00:36 <****> I also have the GUI tools installed, and am watching the "Health" page in "MySQL Administrator"
00:36 <****> i got older HW than that serving millions of hits on a badly coded php app
00:36 <****> Look at the output of the tool "mytop" - do you see a lot of threads locked?
00:37 <****> one sec, gotta figure out where to download said tool..
00:37 <****> If your Apaches are using too much ram due to having lots of children with huge PHPs in them, they are pushing all of mysql's nice disc cache out and limiting its performance by causing the IO to be maxed out needlessly
00:38 <****> mysql is currently using about 400 megs of VSZ, and 83 megs of RSS - does that tell us anything?
00:39 <****> best to use mod_php rather than PHP as external CGI
00:39 <****> probably that too much is swapped out
00:39 <****> disk swap on the box is essentialy unused. There is still plenty of free RAM...
00:39 <****> How big are the Apaches (approx) and what is MaxCLients set to? Are you using prefork?
00:39 <****> that would greatly reduce memory consumption and swapping, i'm sure
00:40 <****> using mod_php does not hugely reduce PHP's memory consumption, largely it just makes it start faster
00:40 <****> In fact, if anything it increases it
00:41 <****> maybe a dozen apaches in 'ps', looks like about 50 meg each (though I presume a good deal of that is shared memory).
00:41 <****> Hmm, right, and MaxClients is not set too high?
00:41 <****> setting MaxClients too high can kill your box in the event of a load spike
00:41 <****> Almost everyone has it too high, with PHP
00:42 <****> Because the default recommendations don't take into account the extra per-child ram that PHP uses
00:42 <****> and no, sadly, much of it is NOT shared
00:42 <****> Hrmm.. wierd the way the did the config file... one sec
00:43 <****> is there a way to create a field that will auto datestamp?
00:43 <****> either 150, or 256, depending on which of these modules is loading... still tracing through various config files...
00:43 <****> BrotherJack: Are there a lot of tables being opened and closed all the time?
00:43 <****> MarkR42: how would I check that?
00:43 <****> Look at varios things in SHOW STATUS
00:44 <****> Do you have a lot of small tables which is more than the table_cache ?
00:44 <****> Led-Hed: search the manual for the timestamp data type, and its limitations.
00:45 <****> If opened_tables in "SHOW STATUS" keeps increasing, that means that tables have to be kept being reopened
00:45 <****> ki77a77, timestamp Thanks
00:45 <****> MarkR42: like what things?
00:46 <****> open_tables = 64, opened_tables = 0
00:47 <****> How many tables are in your database(s) in total?
00:47 <****> opened_tables = 0 suggests it's pretty idle
00:48 <****> MarkR42: not a huge number I don't think. I coudl go digging, but less than 500 for sure, probably less than 200...
00:48 <****> Ahh actually, do "show global status like 'Opened_tables'
00:49 <****> global status, not "show status"
00:49 <****> ah..
00:49 <****> If that number keeps going up a lot, you have problems
00:49 <****> open tables, 64, opened tables = 43412
00:50 <****> Which I presume would be since last time I restarted MySQL which would have been yesterday afternoon.
00:50 <****> Under Linux, the table cache size can usually be safely increased to 512 (But check this on your dev server)
00:50 <****> That sounds like a lot anyway
00:51 <****> cranked up to 256
00:52 <****> I don't know if that counts temporray tables
00:52 <****> should I increase the max_tmp_tables to something greater than 32?
00:53 <****> Probably not
00:53 <****> that is per thread; if one thread needs more than 32 temp tables, I question its algorithm
00:53 <****> gotcha
00:54 <****> I have personally never written anything I can remember which used more than two temp tables
00:54 <****> I may have more tables than I thought - looks like about 1400 total, all databases combined.
00:55 <****> Well, there is some point at which you can increase the table cache too much, and run out of file descriptors, that would be bad, dont' do that
00:55 <****> As far as I'm aware, each open table requires two file descriptors
00:55 <****> (find /path/to/mysql/lib/ | grep MYI | wc -l)
00:55 <****> (But I may be wrong)
00:56 <****> The main things that you can increase to improve myisam performance are the key buffer and sort buffer
00:56 <****> But do so carefully - do not run out of address space on a 32bit system
00:57 <****> Is your web site a public one?
00:57 <****> If so, may I see it please?
00:57 <****> what are some sane vaules for those two on a 32 bit system, as described? (quad xeon, Linux, 4gig of ram, etc)
00:58 <****> It is absolutely essential that your mysql does not allocate more than about 1.9Gb of RAM
00:58 <****> Regardless of how much is available
00:58 <****> depends on the OS.
00:58 <****> I mean, on a typical 32-bit Linux
00:58 <****> MarkR42 - public yes, but you need an account to see anyting other than the login page. If you want, I can make you one.
00:58 <****> you can check with a simple script, on my host 2.7G is possible, Linux 2.6.21 with PAE
01:00 <****> BrotherJack: I'd set the sort buffer to something fairly small, maybe 16M
01:00 <****> And the key buffer to something nice and big like 500M
01:00 <****> MarkR42: I think it's currently set for 2M
01:00 <****> but not in any danger of running out of address-space
01:01 <****> that's quite a large sort buffer
01:01 <****> Anyway, if max_connections * sort_buffer + key_buffer are still <1.9G, you're safe
01:01 <****> sort buffers are only allocated on-demand I think
01:01 <****> yes
01:01 <****> Key buffer is currently 128 meg ( but I just changed that - it was like 8 meg before I started on this project today)
01:02 <****> Are you definitely using myisam exclusively? If so, disable innodb.
01:02 <****> http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/
01:03 <****> one sec, let me zgrep my backup files for "Innodb", and I'll verify that.
Total 27 pages. You are browsing page 2/27.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last