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):
[EMAIL PROTECTED] 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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
