[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Memory Problems on a G5/OSX/MySql4.0.17
From:       Adam Goldstein <whitewlf () whitewlf ! net>
Date:       2004-01-31 6:09:14
Message-ID: FECA9CEE-53B3-11D8-AC75-000A95773752 () whitewlf ! net
[Download RAW message or body]

On Jan 30, 2004, at 10:25 AM, Bruce Dembecki wrote:

>> On Jan 28, 2004, at 12:01 PM, Bruce Dembecki wrote this wonderful 
>> stuff:
>>>
>>> So.. My tips for you:
>>>
>>> 1) Consider a switch to InnoDB, the performance hit was dramatic, 
>>> and it's
>>> about SO much more than transactions (which we still don't do)!
>>>
>> Consider it switched! as soon as I find the way to do so :)
>> Are there any changes necessary to his code/queries to use innodb?
>>
> No changes needed to code/queries, except probably the daily table
> optimize/repair can go away... As far as how to do it...
<SNIP SNIP>

>>  Unfortunately, while we would love to have similar down times to 
>> that,
>> we obviously have overloads on a daily basis, bringing the machine 
>> down, or
>> bogging it out, for vast periods of the day... This is only recently 
>> (past
>> 2-3 months) before that, it bogged out onlya couple time a day, and 
>> the
>> slowness was, at least, functional- though, thats a very relative 
>> term,
>> especially when dealing with users who may have shorter attention 
>> spans.
>>
>> How are you spanning your queries over multiple DB servers? and are 
>> all
>> writes  being done on one master server, or have you found a way to 
>> do 2 way
>> replication? Currently we have only the main appserver 
>> apache2/php/thttpd,
>> and are trying a secondary apache2/php server (dual PIII/850/2G) which
>> doesn't appear to be handling the load well at all... We're looking,
>> obviously, at adding several front end appservers, though we want to 
>> ensure
>> the G5 will be able to handle it's job if there are 5-6+ frontends on 
>> it.
>>
>> Here's a few stats from today. If you see anything oddball, please 
>> let me
>> know. I see the Qcache_free_memory is awfully large, I assume that 
>> means
>> it's being tremendously wasted.
>>
> The stats say that the Query Cache is WAY big, but I predicted that. 
> As for
> the slowness, I can't address the application side, but MySQL 
> shouldn't be
> the cause of problems with this sort of hardware and server load. On 
> the
> rest of the stats my comments are the number of queries aren't that 
> high,
> this server should be more than enough properly tuned....
>
> Number of connections seems high, that's an Application thing, I can 
> only
> assume it's efficient in how it manages it's database connections etc? 
> My
> stats show 47K connections on an uptime of a week, yours is at 242K
> connections on less than a day. My server typically has a little less 
> than
> 400 connections actually present at any time and if I have 10 threads
> running (including replication etc) I start to get nervous and look 
> for a
> problem (and pagers go off if we get over 20 threads running for more 
> than
> 30 seconds). The number of times your connections change database 
> seems high
> too, there are as many change database commands as selects. Again this 
> is
> likely an Application thing, but might be an area that could be 
> improved.
>
<SNIP>

  While we prepare for the innodb switch, I would like to see if there 
is some idea as to why we are seeing so many DB changes and 
connections.  Apache is set to allow unlimited persistant connections 
(prefork mpm... worker is still apparently unable to be used with php) 
and php.ini is set to allow unlimited persistent connections.  I am 
unsure, yet, what calls are made exactly in the client's code, but, I 
do know his mysql module uses pconnects.  Is there a timeout setting I 
should be looking for or something else?  I will include some more 
important settings to apache/php/sysctrl below. The G5 has no odd 
settings, though it is OSX not OSXS (10.3.2).

I have felt that the app server is a primary cause of the slowdowns... 
every page has debug info which report the timing of every sql call on 
the page, if the debug bit is set in the url when requested, and it 
doesn't match the page load times all the time. The pages may load 
slowly (time to data starting to be sent to browser, not time browser 
takes to completely receive it and images, etc) but the sql info says 
the queries took no time to complete.

The overall generation time of the page is also calculated/reported for 
every page, and show the same discrepancies at times. In general, 
however, the sql times added together form 85-95% of the total page 
generation time, and that time is roughly the correct amount of time it 
takes for the page to start rendering. But the larger select queries 
which form the big/heavy portions of the pages (item lists within their 
categories) can have just plain wild times, with seemingly no reason. 
The same query done over and over will sometimes jump from >1sec to 5, 
30,60 sec. and then back down again. The mysql server may not appear 
heavily loaded at these times, either- they will appear mostly normal.

While we have nice SystemStats on our front end boxes (load/process 
counts/bandwidth/file sizes, etc) we do not, yet, have anything like it 
on the G5. The same stats calls do not run ont he G5, or, their output 
is unrecognized. I am trying to find a good stats grapher for OSX, 
particularly a non-console app (I use SystemStats2 from de-neef.net.. a 
simple, php/rrd poller that generates nice graphs in web page formats. 
My client has created a page load time graph very recently which I will 
be correlating to our other graphs shortly to see if i can find any 
points to look at.

(Only odd/important settings included, all on app servers)
php.ini: output_buffering = Off
zlib.output_compression = Off
max_execution_time = 120
max_input_time = 90
memory_limit = 40M (was 16M then 24M... )
register_globals = On
default_socket_timeout = 60
sql.safe_mode = Off
mysql.allow_persistent = On
mysql.max_persistent = -1 (was 230, apache max clients was set to 230 
once, changed now)
mysql.max_links = 1200  (not -1, to prevent runaway)
mysql.connect_timeout = 60 (try higher... or -1 ? )
mysql.trace_mode = Off
session.save_handler = files
session.use_cookies = 1
session.auto_start = 0

from apach2 conf:
Timeout 160
KeepAlive On
MaxKeepAliveRequests 0
KeepAliveTimeout	10
ListenBacklog		150
SendBufferSize	1024
StartServers		40
MinSpareServers	40
MaxSpareServers	100
ServerLimit		512
MaxClients		450
MaxRequestsPerChild	1000

 From sysctl:
net.ipv4.tcp_ecn = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.tcp_fin_timeout = 20
net.ipv4.tcp_keepalive_time = 2000
net.ipv4.tcp_sack = 0
net.ipv4.tcp_timestamps = 0
vm.bdflush = 50 1000 64 256 1000 3000 60 20 0
net.ipv4.tcp_wmem = 4096 16384 262143
vm.max-readahead = 512
vm.min-readahead = 10


-- 
Adam Goldstein
White Wolf Networks
http://whitewlf.net



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]