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.
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
