At 20:54 -0500 02/04/2002, I whispered:
>Hi all,
>
> A couple of weeks back you may remember that I was asking
>about setting up a new client on our server that ran around 35GB in
>bandwidth per month mostly from MySQL queries. Well, we have put
>them on our server and have good and bad news. The good news is that
>they are now pulling around 45GB in bandwidth per month. Overall the
>performance is acceptable and the machine is happy.
>
Hmm. I guess no one has any suggestions on tweaking our mysqld server
to run better. This is an important MySQL issue for us. If no one can
suggest a tip or two, perhaps someone can suggest a place to look on
the internet or a good book. I have read most of the pertinent
documentation from MySQL and have done my share of searching through
google for pertinent information. I have New Riders book on MySQL
which has been read cover to cover. I am stil stuck.
I will include my last email below in case anyone can offer some advice.
Oh, and i forgot last time - we are using MySQL 3.23.37.
here is the old email
--------------------------
A couple of weeks back you may remember that I was asking
about setting up a new client on our server that ran around 35GB in
bandwidth per month mostly from MySQL queries. Well, we have put them
on our server and have good and bad news. The good news is that they
are now pulling around 45GB in bandwidth per month. Overall the
performance is acceptable and the machine is happy.
Now, the bad news, which is why I am emailing the list for I
am out of ideas. During peak assaults on our server, which is noon
eastern time and noon pacific time, or there-abouts, our server does
not do so well. Apart from getting CPU warning from the server about
high load averages going up to 15 for a 15 minute period, we also had
the sendmail server shutdown for a couple of minutes from lack of
resources.
A few specs:
MySQL extended-status shows some of the following
| Aborted_clients | 1315 |
| Aborted_connects | 20 |
| Bytes_received | 568116057 |
| Bytes_sent | 113998812 | <---this should be
several Gig by now, is there a rollover?
| Connections | 166586 |
| Created_tmp_disk_tables | 25944 |
| Created_tmp_tables | 96834 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 37034 |
| Handler_read_first | 360285 |
| Handler_read_key | 17394405 |
| Handler_read_next | 28441290 |
| Handler_read_prev | 4808 |
| Handler_read_rnd | 21530499 |
| Handler_read_rnd_next | 1185471281 |
| Handler_update | 690269 |
| Handler_write | 1808922 |
| Key_blocks_used | 46376 |
| Key_read_requests | 81926178 |
| Key_reads | 17962 |
| Key_write_requests | 712426 |
| Key_writes | 234236 |
| Max_used_connections | 49 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 420 |
| Open_streams | 0 |
| Opened_tables | 5745 |
| Questions | 5080399 |
| Select_full_join | 102 |
| Select_full_range_join | 1 |
| Select_range | 370613 |
| Select_range_check | 0 |
| Select_scan | 952200 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 84 | <---This is not good is it?
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 381972 |
| Sort_rows | 36789069 |
| Sort_scan | 659404 |
| Table_locks_immediate | 4181394 |
| Table_locks_waited | 2958 |
| Threads_cached | 7 |
| Threads_created | 1678 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1141490 |
+--------------------------+------------+
I have basically used the large-my.cnf configuration with
modifications to allow up to 150 simultanous connections. Though I am
wondering what else I can modify, or perhaps I should not have use
that configuration.
System specs:
Cobalt RaQ4i
450MHz
512MB of RAM <--Which is why I am thinking that large-my.cnf is too big
Apache, PHP and MySQL
Application is GeekLog 1.2.5 which albeit is not very efficient in its queries.
Hmm, I think I have included most of my information.
Now, can anyone give some suggestions on how to enhance MySQL? The
other processes are definitely not causing the problems. From random
'top' viewings, I see that I have on average 5-10 mysqld running
around 50-75% CPU usage (total) and about 56MB of RAM each. Here is a
typical view of the top of my 'top', so to speak:
8754 mysql 11 0 62456 60M 1684 R 0 23.3 12.0 0:09 mysqld
8453 mysql 19 0 62456 60M 1684 R 0 18.3 12.0 0:35 mysqld
7618 mysql 10 0 62456 60M 1684 R 0 6.7 12.0 1:32 mysqld
7619 mysql 12 0 62456 60M 1684 R 0 6.3 12.0 1:25 mysqld
8299 mysql 1 0 62456 60M 1684 S 0 4.3 12.0 0:42 mysqld
8298 mysql 0 0 62456 60M 1684 S 0 4.1 12.0 0:29 mysqld
12991 httpd 2 0 15284 14M 11680 S 0 2.7 2.8 1:14 httpd
12987 httpd 0 0 15544 14M 11660 S 0 2.0 2.9 1:14 httpd
7833 mysql 0 0 62456 60M 1684 S 0 1.8 12.0 1:17 mysqld
12853 httpd 0 0 14780 14M 11624 S 0 1.6 2.7 1:20 httpd
13042 httpd 0 0 14804 14M 11672 S 0 1.6 2.7 1:20 httpd
12665 httpd 0 0 14716 14M 11668 S 0 1.5 2.7 1:11 httpd
There, that is about it for now. I have done my homework and have
spent many many hours study the list, the documentation, searching
the 'Net' for more information, and I have not come up with a way to
make our machine more efficient. So, if anyone can suggest even the
most obvious of things to look at then I would be forever grateful.
James Riordon
SysAdmin (at least for now...)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
