Yes, I saw this port before... I am not sure why I cannot allocate more
ram on this box- It is a clean 10.3 install, with 10.3.2 update. I got
this box as I love OSX, and have always loved apple, but, this is not
working out great. Much less powerful (and less expensive) units can do
a better job of this (the entire site was run on ONE dual athlon box
with 3G ram, and it seems to have made -NO- difference moving the mysql
to the dedicated G5.)
Obviously, there is something wrong somewhere- And, I need to find
where. My client (site creator) is depending on me to help him boost
the ability of the site to handle more users, but we've always been
able to do it on a light budget. I need to know where to look first, as
we are running out of time... His users are a fickle bunch, and will
likely migrate off to other sites if this slowness continues (it has
been degrading for past 3-4 months from slow at peak, to dead for all
peak hours).
These are example queries from the heavier pages:
1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS
fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204
LIMIT 0,1
Time: 0.0004551410675 sec / Type: Buffered
2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3,
e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut,
e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere
e WHERE e.id_categorie IN (4204) AND (e.date_fin >= '2004-01-26
14:41:59') ORDER BY date_fin ASC LIMIT 0, 80
Time: 37.60733294 sec / Type: Buffered
3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE
e.id_categorie IN (4204) AND (e.date_fin >= '2004-01-26 14:41:59')
Time: 0.9267110825 sec / Type: Buffered
4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query =
'language=E&cat=4204&sql_log=Y', fdt_date = '2004-01-26', fdt_time =
'14:42:38', fnb_seconds = 39.22
Time: 0.005410909653 sec / Type: Buffered
making the page take > 40 seconds to load.
A few minutes later it can take only 1 second to load the same page...
Strangely enough, even after shutting off apache on the primary server,
it still took 33sec to execute query #2 above directly on the G5/mysql
server. A few moments before shutting off apache, my client informed me
a page just took >220sec to load. The apache/php server had a load >8
at that time, and there were numerous apache connections/children
running at that time.
Now, a few minutes later:
Page generated in 33.96 seconds.
1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS
fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204
LIMIT 0,1
Time: 0.0008599758148 sec / Type: Buffered
2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3,
e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut,
e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM enchere
e WHERE e.id_categorie IN (4204) AND (e.date_fin >= '2004-01-26
15:18:47') ORDER BY date_fin ASC LIMIT 0, 80
Time: 1.710601091 sec / Type: Buffered
3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE
e.id_categorie IN (4204) AND (e.date_fin >= '2004-01-26 15:18:47')
Time: 30.83186793 sec / Type: Buffered
4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query =
'language=E&cat=4204&sql_log=Y', fdt_date = '2004-01-26', fdt_time =
'15:19:21', fnb_seconds = 33.96
Time: 0.01498007774 sec / Type: Buffered
Query 2 is fast, and 3 is slow. Completely reversed.
Here is a snapshot of stats (my own script) at the time of the first,
larger, slowdown (with explanations):
[root@akiak root]# webstat
14:54:57 up 22:48, 2 users, load average: 8.62, 7.39, 6.82
Page Connections: 408 <--- avg of several netstat outputs on
webIP:80 (apache/php)
Image Connections: 538 <--- avg of several netstat outputs on
imageIP:80 (thttpd)
Mysql Connections: 386 <--- avg of several netstat outputs on :3306
Mail Connections: 28 <--- avg of several netstat outputs on :25
Apache Processes: 402 <--- apache child process count
Mysql Processes: 0
php Processes: 2 <--- number of currently running cron'd
php/shell scripts
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
1 3 2 18 55 1326 0 0 143 79 240 211 10
6 84 0
1 1 2 15 54 1330 0 0 4448 2784 1351 2753 31
27 42 0
3 1 2 9 54 1331 0 0 3332 2960 1275 3146 28
29 43 0
4 2 2 9 53 1332 0 0 2564 2892 1722 3213 25
25 50 0
We also have lots of good graph info from SystemStats php/rrd grapher,
but only from the main apache server. I am adding it to the G5/sql
server now. The main thing on the graph is the inverse reaction of
Load, Processes, and Bandwidth usage. The bandwidth drops from
~2-3Mbits, to 50-80% less then slowly rises again, creating vivid dips.
It is hard to determine the exact causes (many theories, no good
solutions).
--
Adam Goldstein
White Wolf Networks
http://whitewlf.net
On Jan 26, 2004, at 11:49 AM, Gabriel Ricard wrote:
> 2GB was the per-process memory limit in Mac OS X 10.2 and earlier.
> 10.3 increased this to 4GB per-process. I've gotten MySQL running with
> 3GB of RAM on the G5 previously.
>
> This is an excerpt from a prior email to the list from back in October
> when I was first testing MySQL on the G5:
>
> > query_cache_size=1024M
> > bulk_insert_buffer_size=256M
> > tmp_table_size=128M
> > sort_buffer=8M
> > read_rnd_buffer_size=8M
> > key_buffer=768M
> > record_buffer=32M
> > myisam_sort_buffer_size=512M
> > innodb_buffer_pool_size=1024M
> > innodb_additional_mem_pool_size=32M
>
> > However, for some reason, when I swapped the values key_buffer and
> query_cache_size to try and give
> > key_buffer 1GB, it failed. I swapped the values back and it worked
> fine... odd.
>
> - Gabriel
>
> On Jan 26, 2004, at 11:16 AM, Brent Baisley wrote:
>
>> Yes, MySQL is capable of using more than 2GB, but it still must obey
>> the limits of the underlying OS. This means file sizes, memory
>> allocation and whatever else. Have you heard of anybody allocating
>> more the 2GB using OSX? I've heard of quite a bit more using Linux or
>> other Unix flavors, but not OSX.
>>
>> As for optimizing settings, you need to profile you work load. You
>> may actually run into I/O, CPU or Network bottleneck before you hit a
>> memory bottleneck. You need to run things and find where the
>> bottleneck is to optimize performance.
>>
>> On Jan 26, 2004, at 11:09 AM, Adam Goldstein wrote:
>>
>>> Others on this list have claimed to be able to set over 3G, and my
>>> failure is with even less than 2G (though, I am unsure if there is a
>>> combination of other memory settings working together to create an
>>> >2GB situation combined)
>>>
>> --
>> Brent Baisley
>> Systems Architect
>> Landover Associates, Inc.
>> Search & Advisory Services for Advanced Technology Environments
>> p: 212.759.6400/800.759.0577
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
