I'm on a dual xeon 2.4 with 2gb ram and 2 scsi 80gb hds and ht disabled!

Please, ANY help would be great!

Just running a mysql server and here's my loads at peak time

00:22:01 up 9:09, 1 user, load average: 32.27, 38.71, 30.05


Tue Jul 20 00:31:41 CDT 2004


00:32:25 up 9:19, 1 user, load average: 51.95, 41.51, 33.91
274 processes: 242 sleeping, 32 running, 0 zombie, 0 stopped
total 75.0% 0.0% 25.0% 0.0% 0.0% 0.0% 0.0%
cpu00 76.1% 0.0% 23.8% 0.0% 0.0% 0.0% 0.0%
cpu01 73.8% 0.0% 26.1% 0.0% 0.0% 0.0% 0.0%
Mem: 2061576k av, 287456k used, 1774120k free, 0k shrd, 42516k buff
189848k active, 44380k inactive
Swap: 2048276k av, 0k used, 2048276k free 145520k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3990 mysql 16 0 31672 30M 1348 R 19.6 1.5 0:08 0 mysqld
4027 mysql 15 0 37024 36M 1348 R 15.6 1.7 0:11 0 mysqld
4059 mysql 15 0 35592 34M 1348 R 15.6 1.7 0:07 0 mysqld
3952 mysql 15 0 31672 30M 1348 R 13.9 1.5 0:08 0 mysqld
3994 mysql 16 0 31672 30M 1348 R 13.9 1.5 0:09 0 mysqld
3936 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:07 0 mysqld
3943 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:09 0 mysqld
4008 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:08 0 mysqld
4025 mysql 15 0 32948 32M 1348 R 13.3 1.5 0:07 0 mysqld
4096 mysql 15 0 35592 34M 1348 R 13.0 1.7 0:04 0 mysqld
3970 mysql 15 0 31672 30M 1348 R 12.5 1.5 0:10 0 mysqld
4051 mysql 15 0 35592 34M 1348 R 12.2 1.7 0:07 0 mysqld
4172 mysql 18 0 35592 34M 1348 R 12.2 1.7 0:00 0 mysqld
3974 mysql 15 0 31672 30M 1348 R 5.6 1.5 0:08 0 mysqld
4122 mysql 16 0 35592 34M 1348 R 5.6 1.7 0:00 0 mysqld
4175 mysql 18 0 35592 34M 1348 R 3.7 1.7 0:00 1 mysqld
4171 mysql 17 0 35592 34M 1348 R 2.2 1.7 0:00 0 mysqld
4067 mysql 16 0 35592 34M 1348 R 0.8 1.7 0:06 0 mysqld
3972 mysql 15 0 31672 30M 1348 R 0.2 1.5 0:07 0 mysqld
4109 mysql 15 0 35592 34M 1348 R 0.2 1.7 0:05 0 mysqld
3996 mysql 15 0 31672 30M 1348 R 0.0 1.5 0:09 0 mysqld
4041 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:06 0 mysqld
4056 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:06 0 mysqld
4092 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:02 0 mysqld
4116 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4163 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4181 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4182 mysql 17 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4183 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4184 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4185 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 1 mysqld


Http processes currently running = 11
Mysql processes currently running = 222

Netstat information summary
3 FIN_WAIT2
19 LISTEN
218 ESTABLISHED

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 460 |
| Aborted_connects | 0 |
| Bytes_received | 1228740 |
| Bytes_sent | 9936311 |
| Com_admin_commands | 3252 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 4121 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 13 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 344 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 12366 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 2 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1696 |
| Connections | 474 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 12 |
| Handler_read_first | 1 |
| Handler_read_key | 2577 |
| Handler_read_next | 0 |
| Handler_read_prev | 5623714 |
| Handler_read_rnd | 37007 |
| Handler_read_rnd_next | 524266213 |
| Handler_rollback | 0 |
| Handler_update | 1621 |
| Handler_write | 344 |
| Key_blocks_used | 502 |
| Key_read_requests | 234824 |
| Key_reads | 498 |
| Key_write_requests | 364 |
| Key_writes | 340 |
| Max_used_connections | 217 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 369 | 72% of table_cache in use
| Open_files | 379 |
| Open_streams | 0 |
| Opened_tables | 375 |
| Questions | 19566 |
| Qcache_queries_in_cache | 328 |
| Qcache_inserts | 10388 |
| Qcache_hits | 987 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1975 |
| Qcache_free_memory | 33117768 |
| Qcache_free_blocks | 38 |
| Qcache_total_blocks | 624 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 9806 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 202 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 37007 |
| Sort_scan | 2756 |
| Table_locks_immediate | 7583 |
| Table_locks_waited | 6812 |
| Threads_cached | 0 |
| Threads_created | 218 |
| Threads_connected | 218 |
| Threads_running | 115 |
| Uptime | 654 | 10 mins 54 secs
+--------------------------+-----------+


Key Reads/Key Read Requests = 0.002121 (Cache hit = 99.997879%)
Key Writes/Key Write Requests = 0.934066
Connections/second = 0.725 (/hour = 2609.174)
KB received/second = 1.833 (/hour = 6600.000)
KB sent/second = 14.836 (/hour = 53411.009)
Temporary Tables Created/second = 0.000 (/hour = 0.000)
Opened Tables/second = 0.573 (/hour = 2064.220)
Slow Queries/second = 0.309 (/hour = 1111.927)
% of slow queries = 1.032%
Queries/second = 29.917 (/hour = 107702.752)

Here's my.cnf


code:--------------------------------------------------------------------------------
[mysqld]
max_connections = 768
interactive_timeout=100
key_buffer = 384M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 512
record_buffer = 2M
thread_cache_size = 128
wait_timeout = 100
tmp_table_size= 8M
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
skip-locking
thread_cache_size=128
thread_concurrency=4

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
--------------------------------------------------------------------------------

what kind of queries are running... it seems like they must be extremely cpu intensive and is MAYBE where you should look to solve your problem.

PS: I am no expert :-)

It's a fotolog site.

90% of the queries is for the coments, just like a forum...but with about 1000 users online

Try rebuild MYSQL from source with expert optimization.

where i can find a mysql expert?!

You could pay for one....

Originally posted by webcluster
where i can find a mysql expert?!

Perhaps Employment Offers and Requests (http://www.webhostingtalk.com/forumdisplay.php?forumid=33)?

What does


mysqladmin status


return.

You also have a bunch of slow querys.

Hello,
Yes there are lot of things u can do .
First of all I can notice this server u are using for mysql purpose only.Then optimise ur my.cnf file with my-huge.cnf file.Now I guess u must be know what i am speaking about? Use my-huge.cnf and restart the mysql service.
Now slow queries number is also quite ok but see the slow queries logs.They will give u great idea which queries are going slow then try to optimise that table.
Have u optimized the tables aleady?BEfore doing this u need to perform indexing etc on all tables so that search will be fast .Kepp in mind this is very important to optimize the tables.
U can get help from mysql manual for this.There is one function optimize (don't remember the name) see that too.
Also try reducing the max connection value to 500 and see .. I think u should do this ...
There is one last way ..but let it be ...
Hope this helps ...
If not then u are always welcome for help from me ...
And more thing hiring a mysql expert is good option if u can afford him.Basically they are Database Admin ..and u know the real fact ...they (Database Admin)can only solve by tial and error method only...
Let U know the great fact that In Mysql trial and error will give u much idea than else...
So Best of luck .. and let me know if it works...

Hey any help for me:

Server is: DUAL XEON 2.8GHz 1GB RAM

mysqladmin status command returns this:

Threads: 46 Questions: 24178 Slow queries: 0 Opens: 610 Flush tables: 1 Open tables: 512 Queries per second avg: 13.462

Recently i start having same problem, in peek hours many clients get errors like too many connections.

Dual Xeon can handle that mysql hits for sure, you might have to check out your settings.

Originally posted by RaviSharma
Hey any help for me:

Server is: DUAL XEON 2.8GHz 1GB RAM

mysqladmin status command returns this:

Threads: 46 Questions: 24178 Slow queries: 0 Opens: 610 Flush tables: 1 Open tables: 512 Queries per second avg: 13.462

Recently i start having same problem, in peek hours many clients get errors like too many connections.


What is the os? What kernel version do you have?

Its RedHat Enterprise 3.0 Kernel is: 2.4.21-15.0.3.ELsmp (is it secure, or do i need to update)

btw i did "mysqladmin status" every 30 seconds, and it shows higher value for "questions" & "opens" everytime than the previous one.

Like i did killall -9 mysqld, and than run "mysqladmin every 20-30 seconds" and "open" jumps from 0 to 700 within 10-15 minutes.

Uptime: 4 Threads: 1 Questions: 18 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables: 15 Queries per second avg: 4.500

Uptime: 54 Threads: 2 Questions: 341 Slow queries: 0 Opens: 61 Flush tables: 1 Open tables: 55 Queries per second avg: 6.315

Uptime: 390 Threads: 10 Questions: 101245 Slow queries: 0 Opens: 290 Flush tables: 1 Open tables: 272 Queries per second avg: 25.960

Uptime: 428 Threads: 9 Questions: 101552 Slow queries: 0 Opens: 298 Flush tables: 1 Open tables: 278 Queries per second avg: 23.271

Uptime: 1176 Threads: 40 Questions: 116521 Slow queries: 0 Opens: 591 Flush tables: 1 Open tables: 512 Queries per second avg: 9.908

Uptime: 1409 Threads: 35 Questions: 120146 Slow queries: 0 Opens: 702 Flush tables: 1 Open tables: 512 Queries per second avg: 25.270


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

Originally posted by UltraUnixNET
Dual Xeon can handle that mysql hits for sure, you might have to check out your settings.

Hi,

Server is running very smooth, load is like this in peek hours: load average: 1.14, 0.77, 0.86 and in non peek hours it is like this: load average: 0.24, 0.14, 0.20


Only problem is, that some people are getting "too many connections" error when they access client's website/forum which are php/mysql based.

i'm running 160-200 simple queries per second on P4 2.8.