Very slow mysql

[Remi]

I have a dedicated server with duall PIII 850 and 1GB RAM

But the mysql is still very slow some times
----------------------
12:25pm up 20 days, 3:38, 2 users, load average: 0.04, 0.08, 0.08
89 processes: 88 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 41.0% user, 0.1% system, 0.0% nice, 57.0% idle
CPU1 states: 29.0% user, 5.0% system, 0.0% nice, 65.0% idle
Mem: 1028604K av, 859756K used, 168848K free, 0K shrd, 12116K buff
Swap: 546080K av, 10800K used, 535280K free 667976K cached


Wed Aug 8 12:25:10 GMT 2001
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 1225910 |
| Bytes_sent | 18515265 |
| Connections | 250 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 227 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 50604 |
| Handler_read_next | 234736 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 61235 |
| Handler_read_rnd_next | 101300 |
| Handler_update | 321 |
| Handler_write | 4870 |
| Key_blocks_used | 3585 |
| Key_read_requests | 233374 |
| Key_reads | 3550 |
| Key_write_requests | 2179 |
| Key_writes | 1812 |
| Max_used_connections | 23 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 29 | 4% of table_cache in use
| Open_files | 55 |
| Open_streams | 0 |
| Opened_tables | 35 |
| Questions | 6260 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 1678 |
| Select_range_check | 0 |
| Select_scan | 1276 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 1584 |
| Sort_rows | 61235 |
| Sort_scan | 772 |
| Table_locks_immediate | 6681 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 249 |
| Threads_connected | 22 |
| Threads_running | 1 |
| Uptime | 637 | 10 mins 37 secs
+--------------------------+----------+

Key Reads/Key Read Requests = 0.015212 (Cache hit = 99.984788%)
Key Writes/Key Write Requests = 0.831574
Connections/second = 0.392 (/hour = 1412.873)
KB received/second = 1.879 (/hour = 6764.835)
KB sent/second = 28.385 (/hour = 102184.615)
Temporary Tables Created/second = 0.356 (/hour = 1282.889)
Opened Tables/second = 0.055 (/hour = 197.802)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 9.827 (/hour = 35378.336)

----------------------------------
this is the my.cnf

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
set-variable= max_connections=100
skip-locking
set-variable= key_buffer=32M
set-variable= max_allowed_packet=6M
set-variable= join_buffer=6M
set-variable= record_buffer=6M
set-variable= sort_buffer=10M
set-variable= table_cache=800

[mysql]
no-auto-rehash

---------------------

Can some one help me please

[Remi]

I asked the same question and I have been wating for a single answer for more than aweek!

http://www.vbulletin.com/forum/showthread.php?s=&threadid=23850

At least tell me if the question is not clear!

[eva2000]

okay can you post the full output of mysqladmin variables command here and also try and install the mysql extended-status script here so i can see the real time mysql stats - http://vbulletin.com/forum/showthread.php?threadid=3477

it could be just that your max_connections setting needs increase from 100... how many online users max do you get when the server is slow?

[Remi]

Thank you very much eva for your reply, I never had more than 60-70 users online.

This is the output of mysqladmin variables, It is much better now, but alot of times it it is very slow.
----------------------------------------

9:14pm up 20 days, 12:28, 2 users, load average: 0.07, 0.07, 0.01
72 processes: 71 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU1 states: 0.1% user, 0.1% system, 0.0% nice, 98.0% idle
Mem: 1028604K av, 896576K used, 132028K free, 0K shrd, 12304K buff
Swap: 546080K av, 10800K used, 535280K free 722260K cached


Wed Aug 8 21:14:36 GMT 2001
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 20 |
| Aborted_connects | 0 |
| Bytes_received | 40792653 |
| Bytes_sent | 686413190 |
| Connections | 9559 |
| Created_tmp_disk_tables | 6 |
| Created_tmp_tables | 7572 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 1608 |
| Handler_read_first | 86 |
| Handler_read_key | 1707068 |
| Handler_read_next | 7787223 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 2080311 |
| Handler_read_rnd_next | 12872467 |
| Handler_update | 10814 |
| Handler_write | 97365 |
| Key_blocks_used | 8030 |
| Key_read_requests | 7317979 |
| Key_reads | 7927 |
| Key_write_requests | 10769 |
| Key_writes | 8420 |
| Max_used_connections | 23 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 52 | 7% of table_cache in use
| Open_files | 89 |
| Open_streams | 0 |
| Opened_tables | 58 |
| Questions | 221078 |
| Select_full_join | 6 |
| Select_full_range_join | 0 |
| Select_range | 57766 |
| Select_range_check | 0 |
| Select_scan | 43932 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 55564 |
| Sort_rows | 2084191 |
| Sort_scan | 26419 |
| Table_locks_immediate | 235363 |
| Table_locks_waited | 8 |
| Threads_cached | 0 |
| Threads_created | 9558 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 32403 | 9 hrs 3 secs
+--------------------------+-----------+

Key Reads/Key Read Requests = 0.001083 (Cache hit = 99.998917%)
Key Writes/Key Write Requests = 0.781874
Connections/second = 0.295 (/hour = 1062.013)
KB received/second = 1.229 (/hour = 4425.812)
KB sent/second = 20.687 (/hour = 74473.660)
Temporary Tables Created/second = 0.234 (/hour = 841.255)
Opened Tables/second = 0.002 (/hour = 6.444)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 6.823 (/hour = 24561.948)