Full Version: HOWTO: Optimize MySQL 4.0.12 after installation 2-Parts
Going to ask something silly -- Did this happen just recently , or after you installed a mod or something else ? I see that your Mysql has been up only 4 minutes. So did this happen after you used this guide ? Also what is the specs ? Because some of the values are reallllly big.
QUOTE
Originally posted by NAPPA
Going to ask something silly -- Did this happen just recently , or after you installed a mod or something else ? I see that your Mysql has been up only 4 minutes. So did this happen after you used this guide ? Also what is the specs ? Because some of the values are reallllly big.
Going to ask something silly -- Did this happen just recently , or after you installed a mod or something else ? I see that your Mysql has been up only 4 minutes. So did this happen after you used this guide ? Also what is the specs ? Because some of the values are reallllly big.
i did a sub-category modification on my phpbb forum.
it worked fine for maybe 30 mins then suddenly the site became slow and the prob lasted till now.
i tried with a default phpbb forum files (unmodded) and the accessing speed is slow too.
Could it be the database itself having prob? or 1 of the script causing the mysql to slow down?
i tried copying the entire database's content to another database and optimized them via phpmyadmin. Ain't helping.
help?
Need to know what the server is -- specs. Processor speed, and Ram. AND MOST IMPORTANTLY -- if you did the changes described in this thread, and then this had happened -- or BEFORE (ie, you didn't use this guide at all ?)
QUOTE
Originally posted by NAPPA
Need to know what the server is -- specs. Processor speed, and Ram. AND MOST IMPORTANTLY -- if you did the changes described in this thread, and then this had happened -- or BEFORE (ie, you didn't use this guide at all ?)
Need to know what the server is -- specs. Processor speed, and Ram. AND MOST IMPORTANTLY -- if you did the changes described in this thread, and then this had happened -- or BEFORE (ie, you didn't use this guide at all ?)
xp1800
1GB pc133 ram
i didnt use this guide here at all, the settings was configured by my hosting company tech.
Can anyone suggest a good my.cnf for my server?
It's a p4 2ghz 512MB DDR Ram On Cpanel
Thank you.
[Tried Aussie's and mysql failed to start:
Starting mysqld daemon with databases from /var/lib/mysql
040104 19:30:43 mysqld ended]
It's a p4 2ghz 512MB DDR Ram On Cpanel
Thank you.
[Tried Aussie's and mysql failed to start:
Starting mysqld daemon with databases from /var/lib/mysql
040104 19:30:43 mysqld ended]
hi
1st) how can i install the secrits , step by step.
2nd) any suggest a good my.cnf for my server :
dual xeon 2400 with 2 GB RAM and 2*73 SCSI .
tnx.
1st) how can i install the secrits , step by step.
2nd) any suggest a good my.cnf for my server :
dual xeon 2400 with 2 GB RAM and 2*73 SCSI .
tnx.
hmmm, how'd you get a dual 2.4 ghz xeon?
hi.. if any of you guys would help me optimize my mysql server, i'd apreciate it greatly... I posted all my info on a new thread.
http://forum.ev1servers.net/showthread.php...&threadid=39699
thanks.
http://forum.ev1servers.net/showthread.php...&threadid=39699
thanks.
These things present themselves to taunt me.
Before my.cnf optimisation
And after...
Edit: I would like to thank everybody who has contributed to this thread. You have spent time to help others, and that is a nice thing to see.
By the way, my overall server load has been cut in half.
My contribution:
If you recieve the error "Fatal error: Can't find messagefile ...." try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it.
Before my.cnf optimisation
And after...
Edit: I would like to thank everybody who has contributed to this thread. You have spent time to help others, and that is a nice thing to see.
By the way, my overall server load has been cut in half.
My contribution:
If you recieve the error "Fatal error: Can't find messagefile ...." try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it.
QUOTE
Originally posted by Intimidated
These things present themselves to taunt me.
[B]Before my.cnf optimisation
And after...
Edit: I would like to thank everybody who has contributed to this thread. You have spent time to help others, and that is a nice thing to see.
By the way, my overall server load has been cut in half.
My contribution:
If you recieve the error "Fatal error: Can't find messagefile ...." try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it. [/B]
These things present themselves to taunt me.
[B]Before my.cnf optimisation
And after...
Edit: I would like to thank everybody who has contributed to this thread. You have spent time to help others, and that is a nice thing to see.
By the way, my overall server load has been cut in half.
My contribution:
If you recieve the error "Fatal error: Can't find messagefile ...." try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it. [/B]
can you share your my.cnf file with us ?
what is your server specification and what is your my.cnf ?
Thanks.
My server spec is the Dual Xeon w/1gb ram.
Please note i *could* optimise this further if I used both the drives, I have everything runnin on hda1. I just can't be arsed
I'll get you my my.cnf when i get my FTP server up again
Please note i *could* optimise this further if I used both the drives, I have everything runnin on hda1. I just can't be arsed
I'll get you my my.cnf when i get my FTP server up again
Here y'are
Also, here are my server stats for your information:
Key Reads/Key Read Requests = 0.002197 (Cache hit = 99.997803%)
Key Writes/Key Write Requests = 0.455471
Connections/second = 0.234 (/hour = 840.688)
KB received/second = 12.669 (/hour = 45608.596)
KB sent/second = 2003.705 (/hour = 7213337.536)
Temporary Tables Created/second = 0.287 (/hour = 1031.519)
Opened Tables/second = 0.109 (/hour = 391.977)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 254.037 (/hour = 914534.097)
MySQL Query Cache hits = 155250/157137(99%)
CODE
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
thread_concurrency=4
max_connections=500
key_buffer=150M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysql.server]
user=mysql
[mysqld_safe]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
thread_concurrency=4
max_connections=500
key_buffer=150M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysql.server]
user=mysql
[mysqld_safe]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
Also, here are my server stats for your information:
Key Reads/Key Read Requests = 0.002197 (Cache hit = 99.997803%)
Key Writes/Key Write Requests = 0.455471
Connections/second = 0.234 (/hour = 840.688)
KB received/second = 12.669 (/hour = 45608.596)
KB sent/second = 2003.705 (/hour = 7213337.536)
Temporary Tables Created/second = 0.287 (/hour = 1031.519)
Opened Tables/second = 0.109 (/hour = 391.977)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 254.037 (/hour = 914534.097)
MySQL Query Cache hits = 155250/157137(99%)
I tried some of these lines such as :
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
and Mysql refuses to start. Any idea why?
As a note this is all my server will start with. Anything else and it refuses
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
max_connections=400
interactive_timeout=100
wait_timeout=100
connect_timeout=10
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
and Mysql refuses to start. Any idea why?
As a note this is all my server will start with. Anything else and it refuses
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
max_connections=400
interactive_timeout=100
wait_timeout=100
connect_timeout=10
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Well, what error does it give?
QUOTE
Originally posted by Intimidated
Well, what error does it give?
Well, what error does it give?
Nothing in the pannel but in the log files it basicaly says:
/usr/libexec/mysqld: unrecognized option `--query_cache_limit=1M'
other than that i see nothing listd. Thing is i copied and pasted everything fine and it does not always give the same error.
The only possible reason that I can come up with is that your MySQL server is older than 4.0, but from your my.cnf this doesn't seem likely.
Try removing your basedir= directive.
Try removing your basedir= directive.
is it possible that i do mysql_fix_privledge_tables with another user instead? How would i do that? I'm using plesk so the so called root is being called admin.
Also...can someone help me optimize these settings.
AMD Athlon XP 2400+
1 gig ddr
I would say I get about 200-300 people during peak hours. Tons of usage for php and mysql. Please help. Thanks. PS this forum has a total of 10,000 members.
Also...can someone help me optimize these settings.
AMD Athlon XP 2400+
1 gig ddr
I would say I get about 200-300 people during peak hours. Tons of usage for php and mysql. Please help. Thanks. PS this forum has a total of 10,000 members.
I just wanted to say thanks to Dave, Aussie, etc. everyone who has contributed to this post. Very helpful information and I appreciate the willingness to help.
I used Aussie's my.cnf settings and ran Dave's php script.
I'm running a dual 2 GHZ machine with 1 GB RAM.
What I'm wondering is if anything you guys can see here in mysql that would be hurting my load average. I am running a ton of people on this server, so I'm not expecting miracles here, and even though the load is high it still runs ok. Just could probably run better.
Most of my users run OSCommerce sites (Probably over 700). Probaby no bulletin board sites, and a couple of PHPNuke sites. Beyond that are a few normal static sites.
The thing that worries me is the queries per second Probably can't be helped?
Anyways, thanks again for any help previous or future.
Here's the results:
Mon Jan 26 12:44:46 CST 2004
12:44pm up 11 days, 12 min, 2 users, load average: 5.02, 5.01, 5.09
123 processes: 117 sleeping, 3 running, 1 zombie, 2 stopped
CPU0 states: 14.0% user, 22.0% system, 0.0% nice, 63.0% idle
CPU1 states: 14.0% user, 9.0% system, 0.0% nice, 76.0% idle
Mem: 1033240K av, 1009964K used, 23276K free, 0K shrd, 118580K buff
Swap: 2048276K av, 76608K used, 1971668K free 490036K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
31199 nobody 19 0 940 940 716 R 7.0 0.0 0:00 top
30939 mysql 11 0 47096 45M 1508 R 3.9 4.5 0:01 mysqld
Http processes currently running = 37
Mysql processes currently running = 16
Netstat information summary
1 SYN_SENT
2 CLOSE_WAIT
10 ESTABLISHED
17 FIN_WAIT2
63 LISTEN
116 TIME_WAIT
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 12 |
| Aborted_connects | 1 |
| Bytes_received | 60202011 |
| Bytes_sent | 58881180 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2194 |
| 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 | 1189 |
| 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 | 425 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 9 |
| Com_optimize | 1 |
| 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 | 44839 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 7 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 9 |
| Com_update | 2399 |
| Connections | 2185 |
| Created_tmp_disk_tables | 23 |
| Created_tmp_tables | 1341 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 622 |
| Handler_read_first | 10101 |
| Handler_read_key | 944774 |
| Handler_read_next | 1473171 |
| Handler_read_prev | 6934 |
| Handler_read_rnd | 36227 |
| Handler_read_rnd_next | 3804662 |
| Handler_rollback | 0 |
| Handler_update | 309527 |
| Handler_write | 41253 |
| Key_blocks_used | 4100 |
| Key_read_requests | 1096271 |
| Key_reads | 4100 |
| Key_write_requests | 242 |
| Key_writes | 124 |
| Max_used_connections | 9 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Open_files | 2023 |
| Open_streams | 0 |
| Opened_tables | 2984 |
| Questions | 337518 |
| Qcache_queries_in_cache | 26963 |
| Qcache_inserts | 42675 |
| Qcache_hits | 284259 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2163 |
| Qcache_free_memory | 3345560 |
| Qcache_free_blocks | 21 |
| Qcache_total_blocks | 56233 |
| Rpl_status | NULL |
| Select_full_join | 475 |
| Select_full_range_join | 0 |
| Select_range | 43 |
| Select_range_check | 0 |
| Select_scan | 7895 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 134 |
| Sort_rows | 40089 |
| Sort_scan | 1769 |
| Table_locks_immediate | 73012 |
| Table_locks_waited | 4 |
| Threads_cached | 9 |
| Threads_created | 10 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1084 | 18 mins 4 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.003740 (Cache hit = 99.99626%)
Key Writes/Key Write Requests = 0.512397
Connections/second = 2.016 (/hour = 7256.458)
KB received/second = 54.235 (/hour = 195246.863)
KB sent/second = 53.045 (/hour = 190962.731)
Temporary Tables Created/second = 1.237 (/hour = 4453.506)
Opened Tables/second = 2.753 (/hour = 9909.963)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 311.363 (/hour = 1120908.487)
MySQL Query Cache hits = 284259/329097(86%)
I used Aussie's my.cnf settings and ran Dave's php script.
I'm running a dual 2 GHZ machine with 1 GB RAM.
What I'm wondering is if anything you guys can see here in mysql that would be hurting my load average. I am running a ton of people on this server, so I'm not expecting miracles here, and even though the load is high it still runs ok. Just could probably run better.
Most of my users run OSCommerce sites (Probably over 700). Probaby no bulletin board sites, and a couple of PHPNuke sites. Beyond that are a few normal static sites.
The thing that worries me is the queries per second
Probably can't be helped?
Anyways, thanks again for any help previous or future.
Here's the results:
Mon Jan 26 12:44:46 CST 2004
12:44pm up 11 days, 12 min, 2 users, load average: 5.02, 5.01, 5.09
123 processes: 117 sleeping, 3 running, 1 zombie, 2 stopped
CPU0 states: 14.0% user, 22.0% system, 0.0% nice, 63.0% idle
CPU1 states: 14.0% user, 9.0% system, 0.0% nice, 76.0% idle
Mem: 1033240K av, 1009964K used, 23276K free, 0K shrd, 118580K buff
Swap: 2048276K av, 76608K used, 1971668K free 490036K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
31199 nobody 19 0 940 940 716 R 7.0 0.0 0:00 top
30939 mysql 11 0 47096 45M 1508 R 3.9 4.5 0:01 mysqld
Http processes currently running = 37
Mysql processes currently running = 16
Netstat information summary
1 SYN_SENT
2 CLOSE_WAIT
10 ESTABLISHED
17 FIN_WAIT2
63 LISTEN
116 TIME_WAIT
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 12 |
| Aborted_connects | 1 |
| Bytes_received | 60202011 |
| Bytes_sent | 58881180 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2194 |
| 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 | 1189 |
| 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 | 425 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 9 |
| Com_optimize | 1 |
| 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 | 44839 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 7 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 9 |
| Com_update | 2399 |
| Connections | 2185 |
| Created_tmp_disk_tables | 23 |
| Created_tmp_tables | 1341 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 622 |
| Handler_read_first | 10101 |
| Handler_read_key | 944774 |
| Handler_read_next | 1473171 |
| Handler_read_prev | 6934 |
| Handler_read_rnd | 36227 |
| Handler_read_rnd_next | 3804662 |
| Handler_rollback | 0 |
| Handler_update | 309527 |
| Handler_write | 41253 |
| Key_blocks_used | 4100 |
| Key_read_requests | 1096271 |
| Key_reads | 4100 |
| Key_write_requests | 242 |
| Key_writes | 124 |
| Max_used_connections | 9 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Open_files | 2023 |
| Open_streams | 0 |
| Opened_tables | 2984 |
| Questions | 337518 |
| Qcache_queries_in_cache | 26963 |
| Qcache_inserts | 42675 |
| Qcache_hits | 284259 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2163 |
| Qcache_free_memory | 3345560 |
| Qcache_free_blocks | 21 |
| Qcache_total_blocks | 56233 |
| Rpl_status | NULL |
| Select_full_join | 475 |
| Select_full_range_join | 0 |
| Select_range | 43 |
| Select_range_check | 0 |
| Select_scan | 7895 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 134 |
| Sort_rows | 40089 |
| Sort_scan | 1769 |
| Table_locks_immediate | 73012 |
| Table_locks_waited | 4 |
| Threads_cached | 9 |
| Threads_created | 10 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1084 | 18 mins 4 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.003740 (Cache hit = 99.99626%)
Key Writes/Key Write Requests = 0.512397
Connections/second = 2.016 (/hour = 7256.458)
KB received/second = 54.235 (/hour = 195246.863)
KB sent/second = 53.045 (/hour = 190962.731)
Temporary Tables Created/second = 1.237 (/hour = 4453.506)
Opened Tables/second = 2.753 (/hour = 9909.963)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 311.363 (/hour = 1120908.487)
MySQL Query Cache hits = 284259/329097(86%)
Your load isn't anything to worry about
I'd attribute that amount of load down to general server processes.
I'd attribute that amount of load down to general server processes.
hi again,
this is my server details:
dual xeon 2000 with 1GB RAM and 2*73 SCSI
the load at hits houre is more than 10
Http processes currently running = Mysql processes currently running =
Netstat information summary
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1733 |
| Aborted_connects | 4 |
| Bytes_received | 564721039 |
| Bytes_sent | 606206303 |
| Com_admin_commands | 3971 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 206794 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 2 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 6 |
| Com_delete | 27084 |
| Com_drop_db | 2 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 8 |
| Com_grant | 4 |
| Com_insert | 39280 |
| Com_insert_select | 1766 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 544 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1904 |
| Com_replace_select | 228 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 2535283 |
| Com_set_option | 210 |
| Com_show_binlogs | 0 |
| Com_show_create | 210 |
| Com_show_databases | 6 |
| Com_show_fields | 210 |
| Com_show_grants | 2 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 198 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 117 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 544 |
| Com_update | 281408 |
| Connections | 200963 |
| Created_tmp_disk_tables | 3220 |
| Created_tmp_tables | 76567 |
| Created_tmp_files | 1558 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 41190 |
| Handler_read_first | 202668 |
| Handler_read_key | 583638183 |
| Handler_read_next | 575736925 |
| Handler_read_prev | 20286 |
| Handler_read_rnd | 30295893 |
| Handler_read_rnd_next | 767450722 |
| Handler_update | 57071103 |
| Handler_write | 12550511 |
| Key_blocks_used | 7793 |
| Key_read_requests | 1564290833 |
| Key_reads | 4960337 |
| Key_write_requests | 255888 |
| Key_writes | 163920 |
| Max_used_connections | 618 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 119 |
| Open_streams | 0 |
| Opened_tables | 774539 |
| Questions | 3295100 |
| Select_full_join | 6295 |
| Select_full_range_join | 158 |
| Select_range | 541546 |
| Select_range_check | 0 |
| Select_scan | 724928 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 16 |
| Slow_queries | 3631 | (execution time > 10 secs)
| Sort_merge_passes | 779 |
| Sort_range | 492740 |
| Sort_rows | 165290550 |
| Sort_scan | 444193 |
| Table_locks_immediate | 3489467 |
| Table_locks_waited | 77809 |
| Threads_cached | 0 |
| Threads_created | 200962 |
| Threads_connected | 170 |
| Threads_running | 4 |
| Uptime | 59471 | 16 hrs 31 mins 11 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.003171 (Cache hit = 99.996829%)
Key Writes/Key Write Requests = 0.640593
Connections/second = 3.379 (/hour = 12165.035)
KB received/second = 9.273 (/hour = 33383.431)
KB sent/second = 9.954 (/hour = 35835.833)
Temporary Tables Created/second = 1.287 (/hour = 4634.884)
Opened Tables/second = 13.024 (/hour = 46885.716)
Slow Queries/second = 0.061 (/hour = 219.798)
% of slow queries = 0.110%
Queries/second = 55.407 (/hour = 199464.613)
this is my server details:
dual xeon 2000 with 1GB RAM and 2*73 SCSI
the load at hits houre is more than 10
Http processes currently running = Mysql processes currently running =
Netstat information summary
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| Aborted_clients | 1733 |
| Aborted_connects | 4 |
| Bytes_received | 564721039 |
| Bytes_sent | 606206303 |
| Com_admin_commands | 3971 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 206794 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 2 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 6 |
| Com_delete | 27084 |
| Com_drop_db | 2 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 8 |
| Com_grant | 4 |
| Com_insert | 39280 |
| Com_insert_select | 1766 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 544 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1904 |
| Com_replace_select | 228 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 2535283 |
| Com_set_option | 210 |
| Com_show_binlogs | 0 |
| Com_show_create | 210 |
| Com_show_databases | 6 |
| Com_show_fields | 210 |
| Com_show_grants | 2 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 198 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 117 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 544 |
| Com_update | 281408 |
| Connections | 200963 |
| Created_tmp_disk_tables | 3220 |
| Created_tmp_tables | 76567 |
| Created_tmp_files | 1558 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 41190 |
| Handler_read_first | 202668 |
| Handler_read_key | 583638183 |
| Handler_read_next | 575736925 |
| Handler_read_prev | 20286 |
| Handler_read_rnd | 30295893 |
| Handler_read_rnd_next | 767450722 |
| Handler_update | 57071103 |
| Handler_write | 12550511 |
| Key_blocks_used | 7793 |
| Key_read_requests | 1564290833 |
| Key_reads | 4960337 |
| Key_write_requests | 255888 |
| Key_writes | 163920 |
| Max_used_connections | 618 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 119 |
| Open_streams | 0 |
| Opened_tables | 774539 |
| Questions | 3295100 |
| Select_full_join | 6295 |
| Select_full_range_join | 158 |
| Select_range | 541546 |
| Select_range_check | 0 |
| Select_scan | 724928 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 16 |
| Slow_queries | 3631 | (execution time > 10 secs)
| Sort_merge_passes | 779 |
| Sort_range | 492740 |
| Sort_rows | 165290550 |
| Sort_scan | 444193 |
| Table_locks_immediate | 3489467 |
| Table_locks_waited | 77809 |
| Threads_cached | 0 |
| Threads_created | 200962 |
| Threads_connected | 170 |
| Threads_running | 4 |
| Uptime | 59471 | 16 hrs 31 mins 11 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.003171 (Cache hit = 99.996829%)
Key Writes/Key Write Requests = 0.640593
Connections/second = 3.379 (/hour = 12165.035)
KB received/second = 9.273 (/hour = 33383.431)
KB sent/second = 9.954 (/hour = 35835.833)
Temporary Tables Created/second = 1.287 (/hour = 4634.884)
Opened Tables/second = 13.024 (/hour = 46885.716)
Slow Queries/second = 0.061 (/hour = 219.798)
% of slow queries = 0.110%
Queries/second = 55.407 (/hour = 199464.613)
turn off your mysql cache
I don't believe that turning OFF his mysql cache would help.
However, I have a server identical to yours haill, and my mysqld is performing a lot better. I'd suggest checking out my my.cnf as I posted above; it does the trick for me.
If that doesn't help at all, I'd suggest checking the speed of your disks, that could be the problem.
However, I have a server identical to yours haill, and my mysqld is performing a lot better. I'd suggest checking out my my.cnf as I posted above; it does the trick for me.
If that doesn't help at all, I'd suggest checking the speed of your disks, that could be the problem.
Hi
i have read all the posts but i can't find a good solution for my server 3.06Ht with 1 Gb tam
here's the outpout:
Wed Feb 25 12:02:25 CET 2004
12:02pm up 22:23, 1 user, load average: 2.66, 1.28, 0.96
255 processes: 254 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 19.1% user, 11.0% system, 0.0% nice, 68.0% idle
CPU1 states: 14.0% user, 13.0% system, 0.1% nice, 72.0% idle
Mem: 1031192K av, 1016980K used, 14212K free, 0K shrd, 30628K buff
Swap: 2096472K av, 400772K used, 1695700K free 112828K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
2697 nobody 19 0 1156 1156 820 R 18.1 0.1 0:00 top
5673 nobody 10 0 34180 27M 18628 D 8.2 2.7 0:28 httpd
Http processes currently running = 112
Mysql processes currently running = 59
Netstat information summary
2 CLOSE_WAIT
2 LAST_ACK
10 FIN_WAIT1
28 LISTEN
98 ESTABLISHED
102 FIN_WAIT2
179 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 11130 |
| Aborted_connects | 4 |
| Bytes_received | 452201970 |
| Bytes_sent | 3202435838 |
| Com_admin_commands | 4743 |
| Com_alter_table | 19 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 126 |
| Com_change_db | 218471 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 126 |
| Com_create_db | 5 |
| Com_create_function | 0 |
| Com_create_index | 14 |
| Com_create_table | 687 |
| Com_delete | 60149 |
| Com_delete_multi | 0 |
| Com_drop_db | 4 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 55 |
| Com_flush | 91 |
| Com_grant | 228 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 49712 |
| Com_insert_select | 533 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 9684 |
| Com_optimize | 20 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 4021 |
| Com_replace_select | 10 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 42 |
| Com_savepoint | 0 |
| Com_select | 463409 |
| Com_set_option | 1785 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1758 |
| Com_show_databases | 264 |
| Com_show_fields | 16412 |
| Com_show_grants | 213 |
| Com_show_keys | 1068 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 453 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 320 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2109 |
| Com_show_variables | 857 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 2 |
| Com_unlock_tables | 7838 |
| Com_update | 186726 |
| Connections | 97757 |
| Created_tmp_disk_tables | 6355 |
| Created_tmp_tables | 63731 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 36911 |
| Handler_read_first | 54681 |
| Handler_read_key | 7701913 |
| Handler_read_next | 345624306 |
| Handler_read_prev | 279684 |
| Handler_read_rnd | 3797500 |
| Handler_read_rnd_next | 179218588 |
| Handler_rollback | 11 |
| Handler_update | 2608313 |
| Handler_write | 2026086 |
| Key_blocks_used | 77303 |
| Key_read_requests | 32215537 |
| Key_reads | 71288 |
| Key_write_requests | 604080 |
| Key_writes | 192475 |
| Max_used_connections | 45 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Open_files | 1936 |
| Open_streams | 0 |
| Opened_tables | 10214 |
| Questions | 2386941 |
| Qcache_queries_in_cache | 10616 |
| Qcache_inserts | 435927 |
| Qcache_hits | 1236302 |
| Qcache_lowmem_prunes | 6540 |
| Qcache_not_cached | 24945 |
| Qcache_free_memory | 11860144 |
| Qcache_free_blocks | 3521 |
| Qcache_total_blocks | 26444 |
| Rpl_status | NULL |
| Select_full_join | 3799 |
| Select_full_range_join | 2925 |
| Select_range | 31282 |
| Select_range_check | 255 |
| Select_scan | 190711 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 13 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 36109 |
| Sort_rows | 4592005 |
| Sort_scan | 99556 |
| Table_locks_immediate | 963089 |
| Table_locks_waited | 258 |
| Threads_cached | 43 |
| Threads_created | 46 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 69724 | 19 hrs 22 mins 4 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.002213 (Cache hit = 99.997787%)
Key Writes/Key Write Requests = 0.318625
Connections/second = 1.402 (/hour = 5047.404)
KB received/second = 6.334 (/hour = 22800.912)
KB sent/second = 30.078 (/hour = 108280.414)
Temporary Tables Created/second = 0.914 (/hour = 3290.569)
Opened Tables/second = 0.146 (/hour = 527.371)
Slow Queries/second = 0.000 (/hour = 0.671)
% of slow queries = 0.001%
Queries/second = 34.234 (/hour = 123242.895)
MySQL Query Cache hits = 1236302/1697174(73%)
i have install mytop also but can't find the account that abuse of Mysql, when i restart Mysql the load come back to the normal
i have read all the posts but i can't find a good solution for my server 3.06Ht with 1 Gb tam
here's the outpout:
Wed Feb 25 12:02:25 CET 2004
12:02pm up 22:23, 1 user, load average: 2.66, 1.28, 0.96
255 processes: 254 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 19.1% user, 11.0% system, 0.0% nice, 68.0% idle
CPU1 states: 14.0% user, 13.0% system, 0.1% nice, 72.0% idle
Mem: 1031192K av, 1016980K used, 14212K free, 0K shrd, 30628K buff
Swap: 2096472K av, 400772K used, 1695700K free 112828K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
2697 nobody 19 0 1156 1156 820 R 18.1 0.1 0:00 top
5673 nobody 10 0 34180 27M 18628 D 8.2 2.7 0:28 httpd
Http processes currently running = 112
Mysql processes currently running = 59
Netstat information summary
2 CLOSE_WAIT
2 LAST_ACK
10 FIN_WAIT1
28 LISTEN
98 ESTABLISHED
102 FIN_WAIT2
179 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 11130 |
| Aborted_connects | 4 |
| Bytes_received | 452201970 |
| Bytes_sent | 3202435838 |
| Com_admin_commands | 4743 |
| Com_alter_table | 19 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 126 |
| Com_change_db | 218471 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 126 |
| Com_create_db | 5 |
| Com_create_function | 0 |
| Com_create_index | 14 |
| Com_create_table | 687 |
| Com_delete | 60149 |
| Com_delete_multi | 0 |
| Com_drop_db | 4 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 55 |
| Com_flush | 91 |
| Com_grant | 228 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 49712 |
| Com_insert_select | 533 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 9684 |
| Com_optimize | 20 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 4021 |
| Com_replace_select | 10 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 42 |
| Com_savepoint | 0 |
| Com_select | 463409 |
| Com_set_option | 1785 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1758 |
| Com_show_databases | 264 |
| Com_show_fields | 16412 |
| Com_show_grants | 213 |
| Com_show_keys | 1068 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 453 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 320 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2109 |
| Com_show_variables | 857 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 2 |
| Com_unlock_tables | 7838 |
| Com_update | 186726 |
| Connections | 97757 |
| Created_tmp_disk_tables | 6355 |
| Created_tmp_tables | 63731 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 36911 |
| Handler_read_first | 54681 |
| Handler_read_key | 7701913 |
| Handler_read_next | 345624306 |
| Handler_read_prev | 279684 |
| Handler_read_rnd | 3797500 |
| Handler_read_rnd_next | 179218588 |
| Handler_rollback | 11 |
| Handler_update | 2608313 |
| Handler_write | 2026086 |
| Key_blocks_used | 77303 |
| Key_read_requests | 32215537 |
| Key_reads | 71288 |
| Key_write_requests | 604080 |
| Key_writes | 192475 |
| Max_used_connections | 45 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Open_files | 1936 |
| Open_streams | 0 |
| Opened_tables | 10214 |
| Questions | 2386941 |
| Qcache_queries_in_cache | 10616 |
| Qcache_inserts | 435927 |
| Qcache_hits | 1236302 |
| Qcache_lowmem_prunes | 6540 |
| Qcache_not_cached | 24945 |
| Qcache_free_memory | 11860144 |
| Qcache_free_blocks | 3521 |
| Qcache_total_blocks | 26444 |
| Rpl_status | NULL |
| Select_full_join | 3799 |
| Select_full_range_join | 2925 |
| Select_range | 31282 |
| Select_range_check | 255 |
| Select_scan | 190711 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 13 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 36109 |
| Sort_rows | 4592005 |
| Sort_scan | 99556 |
| Table_locks_immediate | 963089 |
| Table_locks_waited | 258 |
| Threads_cached | 43 |
| Threads_created | 46 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 69724 | 19 hrs 22 mins 4 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.002213 (Cache hit = 99.997787%)
Key Writes/Key Write Requests = 0.318625
Connections/second = 1.402 (/hour = 5047.404)
KB received/second = 6.334 (/hour = 22800.912)
KB sent/second = 30.078 (/hour = 108280.414)
Temporary Tables Created/second = 0.914 (/hour = 3290.569)
Opened Tables/second = 0.146 (/hour = 527.371)
Slow Queries/second = 0.000 (/hour = 0.671)
% of slow queries = 0.001%
Queries/second = 34.234 (/hour = 123242.895)
MySQL Query Cache hits = 1236302/1697174(73%)
i have install mytop also but can't find the account that abuse of Mysql, when i restart Mysql the load come back to the normal
Rackshack don't stock the server you mentioned!
In any case, you don't seem to be having any performance problems.....
In any case, you don't seem to be having any performance problems.....
QUOTE
Originally posted by Intimidated
Here y'are
Also, here are my server stats for your information:
Key Reads/Key Read Requests = 0.002197 (Cache hit = 99.997803%)
Key Writes/Key Write Requests = 0.455471
Connections/second = 0.234 (/hour = 840.688)
KB received/second = 12.669 (/hour = 45608.596)
KB sent/second = 2003.705 (/hour = 7213337.536)
Temporary Tables Created/second = 0.287 (/hour = 1031.519)
Opened Tables/second = 0.109 (/hour = 391.977)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 254.037 (/hour = 914534.097)
MySQL Query Cache hits = 155250/157137(99%)
Here y'are
CODE
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
thread_concurrency=4
max_connections=500
key_buffer=150M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysql.server]
user=mysql
[mysqld_safe]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
thread_concurrency=4
max_connections=500
key_buffer=150M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysql.server]
user=mysql
[mysqld_safe]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
Also, here are my server stats for your information:
Key Reads/Key Read Requests = 0.002197 (Cache hit = 99.997803%)
Key Writes/Key Write Requests = 0.455471
Connections/second = 0.234 (/hour = 840.688)
KB received/second = 12.669 (/hour = 45608.596)
KB sent/second = 2003.705 (/hour = 7213337.536)
Temporary Tables Created/second = 0.287 (/hour = 1031.519)
Opened Tables/second = 0.109 (/hour = 391.977)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 254.037 (/hour = 914534.097)
MySQL Query Cache hits = 155250/157137(99%)
I have a Dell Dual Xeon 2.4 with 2gigs of ram, so this my.cnf file should work fine or do you reccomend any changes?
I host 1 site that gets 100,000+ hits a day its php/mysql
I'd say that should work well for you too.
You may want to tweak the memory / buffer sizes a little bit, as you have more ram than me, but it shouldn't make that much difference.
You may want to tweak the memory / buffer sizes a little bit, as you have more ram than me, but it shouldn't make that much difference.
Thanks, cpanel is showing my php scripts as 0.00 mysql useage even though they are getting 100,000+ hits a day and each request makes about 10 mysql queries
even though they are getting 100,000+ hits a day and each request makes about 10 mysql queries
I used the first My.cnf settings way back when this post started and my load had kept itself down to 1-3 with 150,000 users per day on my Raid 5 dual pentium 2gig of ram. Every site on my server is a Mysql driven site. We total 150,000-300,000 people a day through via 4 search engines and my main site which has 60-100 users on it every second of the day.
Recently I went and wrote a video review and whoa did we get load. 41% of the server process was Mysql we were hovering at a 13 load.
Now I am using mysql 4.018 standard. So I went back through all of your posts and made some modifications from the orginal settings.
I am now using:
[mysqld]
max_connections = 1500
max_user_connections = 400
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 400
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
thread_concurrency = 4
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
[mysqlhotcopy]
interactive-timeout
The difference?
from a 13 to .9 load and peaking at 3.5
Now none of my photo's or video is stored in mysql really is just pure text.
I think what I would like as a host is a cron job that ran mysql optimization every night on ALL users and db's anyone have some insight on how to accomplish that task. I noticed combing through phpmyadmin some of these guys had some huge overhead.
Recently I went and wrote a video review and whoa did we get load. 41% of the server process was Mysql we were hovering at a 13 load.
Now I am using mysql 4.018 standard. So I went back through all of your posts and made some modifications from the orginal settings.
I am now using:
[mysqld]
max_connections = 1500
max_user_connections = 400
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 400
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
thread_concurrency = 4
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
[mysqlhotcopy]
interactive-timeout
The difference?
from a 13 to .9 load and peaking at 3.5
Now none of my photo's or video is stored in mysql really is just pure text.
I think what I would like as a host is a cron job that ran mysql optimization every night on ALL users and db's anyone have some insight on how to accomplish that task. I noticed combing through phpmyadmin some of these guys had some huge overhead.
Surely you can just write a PHP script to optimize the SQL tables, and then run php <script> in cron.daily
I have a script that does that for specific programs but one that does all tables without knowing how many its going to do and what their names are.
A little beyond me. Really should just be part of Cpanel I am sure who ever writes it will get great kudos for submitting it to Cpanel for inclusion.
A little beyond me. Really should just be part of Cpanel I am sure who ever writes it will get great kudos for submitting it to Cpanel for inclusion.
QUOTE
Originally posted by aagunsales
I have a script that does that for specific programs but one that does all tables without knowing how many its going to do and what their names are.
A little beyond me. Really should just be part of Cpanel I am sure who ever writes it will get great kudos for submitting it to Cpanel for inclusion.
I have a script that does that for specific programs but one that does all tables without knowing how many its going to do and what their names are.
A little beyond me. Really should just be part of Cpanel I am sure who ever writes it will get great kudos for submitting it to Cpanel for inclusion.
pick one of three:
nano -w /etc/cron.daily/mysqlopt
pico -w /etc/cron.daily/mysqlopt
vim /etc/cron.daily/mysqlopt
inside the file:
#/bin/bash
#mysqlcheck -Are
mysqlcheck -Ao
mysqlcheck -Aa
save the file
here's your cron
remove the # before the first mysqlcheck if you're paranoid
Lasttime I counted there were 15 My SQL processes running on my new server
The load is hovering between 1.3 - 2.0 on peak times.
The following is my Configuration
Celeron 2.4
1GB Ram
80GB HDD x2
Swap is 512MB
============================
[mysqld]
# max_connections=500
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=50
wait_timeout=50
connect_timeout=10
thread_cache_size=50
key_buffer=50M
join_buffer=1M
max_allowed_packet=16M
table_cache=348
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=32M
======================================
Is there something Horribly wrong. Now the minimum load I get is at .30 It used to be .03
The load is hovering between 1.3 - 2.0 on peak times.
The following is my Configuration
Celeron 2.4
1GB Ram
80GB HDD x2
Swap is 512MB
============================
[mysqld]
# max_connections=500
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=50
wait_timeout=50
connect_timeout=10
thread_cache_size=50
key_buffer=50M
join_buffer=1M
max_allowed_packet=16M
table_cache=348
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=32M
======================================
Is there something Horribly wrong. Now the minimum load I get is at .30 It used to be .03
Increase your key buffer
I have increased mine all the way to 750 because I run 99% mysql. This was a noticable difference in load and VERY fast loading pages.
I have increased mine all the way to 750 because I run 99% mysql. This was a noticable difference in load and VERY fast loading pages.
I don't remember WHERE but I saw earlier in one of the post how someone was bragging that they were not using all their RAM.
This is a BAD thing. If you have spare RAM dedicate it to your most aggressive processes. For me that is MySql so I increased my key buffer.
I use mysql admin you can get a copy from mysql.org and it will show you your buffer use LIVE. The point is to try and maintain 100% buffer use at least 80% of the time if that makes sense to you any of you. Ram is much faster than your harddrive and that is still what mysql is when its said and done.
So make sure your harddrive is optimized and your buffering as much as possible.
I have found that turning OFF cache and increasing key buffer can increase speed if you have a lot of changing data.
Another thing is use the mysql admin and look at the WAY your scripts access mysql you will find a large number of FREE scripts and some paid ones will do entire db searches for data EVERY TIME. This is a HUGE waste of resource. Lazy programmers.
My full time php guy is reworking a lot of our stuff and pages that would take 30 seconds on a 56k modem now only take 4 secs with no loss of graphics. Php is using compression properly, and mysql is buffering data immediately for common pages.
We have also started to eliminate the use of tables in pages as they are as or even heavier than graphics themselves. Complete CMS systems.
We are working on a Shopping cart that will handle up to 100,000 items and deliver a page from search in less than 1 sec. FAST FAST FAST. That is what sells.
This is a BAD thing. If you have spare RAM dedicate it to your most aggressive processes. For me that is MySql so I increased my key buffer.
I use mysql admin you can get a copy from mysql.org and it will show you your buffer use LIVE. The point is to try and maintain 100% buffer use at least 80% of the time if that makes sense to you any of you. Ram is much faster than your harddrive and that is still what mysql is when its said and done.
So make sure your harddrive is optimized and your buffering as much as possible.
I have found that turning OFF cache and increasing key buffer can increase speed if you have a lot of changing data.
Another thing is use the mysql admin and look at the WAY your scripts access mysql you will find a large number of FREE scripts and some paid ones will do entire db searches for data EVERY TIME. This is a HUGE waste of resource. Lazy programmers.
My full time php guy is reworking a lot of our stuff and pages that would take 30 seconds on a 56k modem now only take 4 secs with no loss of graphics. Php is using compression properly, and mysql is buffering data immediately for common pages.
We have also started to eliminate the use of tables in pages as they are as or even heavier than graphics themselves. Complete CMS systems.
We are working on a Shopping cart that will handle up to 100,000 items and deliver a page from search in less than 1 sec. FAST FAST FAST. That is what sells.
Thanks for telling me. I'm getting record numbers of people on my sites. The mysql pages are coming out at the normal rate - but overall the server is doing 35... AND it's an improvement on the 45 it was doing till I implemented that small change. It's still beign massively high. I've changed the key buffer from 50 to 150 .
Anymore help would be gladly appreciated.
==================================
Wed Apr 7 21:08:54 EDT 2004
21:09:44 up 35 days, 7:25, 0 users, load average: 35.10, 36.61, 32.61
232 processes: 198 sleeping, 33 running, 0 zombie, 1 stopped
total 66.2% 0.0% 31.8% 0.6% 1.2% 0.0% 0.0%
Mem: 1022476k av, 983548k used, 38928k free, 0k shrd, 52384k buff
733816k actv, 364k in_d, 21436k in_c
Swap: 1052248k av, 88460k used, 963788k free 616944k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
8556 nobody 15 0 14112 9448 4340 D 79.9 0.9 0:05 0 httpd
8645 nobody 15 0 14584 9380 5464 D 63.0 0.9 0:04 0 httpd
8654 nobody 16 0 13732 8728 4928 R 36.8 0.8 0:01 0 httpd
8555 nobody 16 0 14252 9.9M 5036 D 21.8 0.9 0:04 0 httpd
8438 nobody 16 0 14636 9556 5444 R 20.6 0.9 0:08 0 httpd
8437 nobody 15 0 14272 9.9M 4440 D 11.2 0.9 0:06 0 httpd
8863 nobody 19 0 10116 3948 1720 R 6.2 0.3 0:00 0 httpd
8890 nobody 20 0 10204 4048 1764 R 5.6 0.3 0:00 0 httpd
8891 nobody 18 0 10240 4256 1800 R 4.3 0.4 0:00 0 httpd
8875 nobody 15 0 1252 1252 784 R 1.8 0.1 0:00 0 top
8903 nobody 21 0 9444 3048 1160 R 0.6 0.2 0:00 0 httpd
8911 nobody 21 0 9168 2620 896 R 0.6 0.2 0:00 0 httpd
8878 nobody 20 0 11596 5736 3040 R 0.0 0.5 0:00 0 httpd
8894 nobody 20 0 9188 2784 916 R 0.0 0.2 0:00 0 httpd
8895 nobody 19 0 9200 2840 928 R 0.0 0.2 0:00 0 httpd
8896 nobody 20 0 10644 4708 2160 R 0.0 0.4 0:00 0 httpd
8899 nobody 20 0 9176 2860 904 R 0.0 0.2 0:00 0 httpd
8901 nobody 20 0 9272 2732 988 R 0.0 0.2 0:00 0 httpd
8902 nobody 20 0 9072 2528 796 R 0.0 0.2 0:00 0 httpd
8904 nobody 20 0 9968 3784 1660 D 0.0 0.3 0:00 0 httpd
8905 nobody 20 0 9176 2632 904 R 0.0 0.2 0:00 0 httpd
8907 nobody 20 0 9188 2836 916 R 0.0 0.2 0:00 0 httpd
Http processes currently running = 87
Mysql processes currently running = 48
Netstat information summary
3 CLOSE_WAIT
5 FIN_WAIT1
8 SYN_RECV
32 LISTEN
51 ESTABLISHED
69 FIN_WAIT2
276 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 14187 |
| Aborted_connects | 1836 |
| Bytes_received | 376186874 |
| Bytes_sent | 792539634 |
| Com_admin_commands | 1048 |
| Com_alter_table | 97 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 29916180 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 19 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 300 |
| Com_delete | 1967353 |
| Com_delete_multi | 0 |
| Com_drop_db | 4 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 65 |
| Com_flush | 323 |
| Com_grant | 158 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1944434 |
| Com_insert_select | 114 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 16789 |
| Com_optimize | 121 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 162 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 68 |
| Com_savepoint | 0 |
| Com_select | 9138683 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 7 |
| Com_show_databases | 444 |
| Com_show_fields | 150 |
| Com_show_grants | 397 |
| Com_show_keys | 51 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 2275 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 143 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1141 |
| Com_show_variables | 240 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 16789 |
| Com_update | 4778910 |
| Connections | 3908922 |
| Created_tmp_disk_tables | 62028 |
| Created_tmp_tables | 179331 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 1930649 |
| Handler_read_first | 229384 |
| Handler_read_key | 31066692 |
| Handler_read_next | 43223180 |
| Handler_read_prev | 664 |
| Handler_read_rnd | 40835010 |
| Handler_read_rnd_next | 4150987660 |
| Handler_rollback | 0 |
| Handler_update | 9984116 |
| Handler_write | 9206471 |
| Key_blocks_used | 48845 |
| Key_read_requests | 85362506 |
| Key_reads | 46262 |
| Key_write_requests | 10925780 |
| Key_writes | 5800963 |
| Max_used_connections | 41 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 348 | 100% of table_cache in use
| Open_files | 563 |
| Open_streams | 0 |
| Opened_tables | 3802 |
| Questions | 79861320 |
| Qcache_queries_in_cache | 6563 |
| Qcache_inserts | 8946143 |
| Qcache_hits | 28175163 |
| Qcache_lowmem_prunes | 37360 |
| Qcache_not_cached | 190692 |
| Qcache_free_memory | 21830320 |
| Qcache_free_blocks | 5056 |
| Qcache_total_blocks | 18612 |
| Rpl_status | NULL |
| Select_full_join | 17210 |
| Select_full_range_join | 31357 |
| Select_range | 25233 |
| Select_range_check | 0 |
| Select_scan | 5484616 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 101 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 248363 |
| Sort_rows | 49323716 |
| Sort_scan | 963319 |
| Table_locks_immediate | 19060394 |
| Table_locks_waited | 75866 |
| Threads_cached | 40 |
| Threads_created | 42 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 682655 | 7 days 21 hrs 37 mins 35 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.000542 (Cache hit = 99.999458%)
Key Writes/Key Write Requests = 0.530943
Connections/second = 5.726 (/hour = 20613.808)
KB received/second = 0.538 (/hour = 1937.331)
KB sent/second = 1.134 (/hour = 4081.521)
Temporary Tables Created/second = 0.263 (/hour = 945.707)
Opened Tables/second = 0.006 (/hour = 20.050)
Slow Queries/second = 0.000 (/hour = 0.533)
% of slow queries = 0.000%
Queries/second = 116.986 (/hour = 421150.877)
MySQL Query Cache hits = 28175163/37311998(76%)
========================================
Anymore help would be gladly appreciated.
==================================
Wed Apr 7 21:08:54 EDT 2004
21:09:44 up 35 days, 7:25, 0 users, load average: 35.10, 36.61, 32.61
232 processes: 198 sleeping, 33 running, 0 zombie, 1 stopped
total 66.2% 0.0% 31.8% 0.6% 1.2% 0.0% 0.0%
Mem: 1022476k av, 983548k used, 38928k free, 0k shrd, 52384k buff
733816k actv, 364k in_d, 21436k in_c
Swap: 1052248k av, 88460k used, 963788k free 616944k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
8556 nobody 15 0 14112 9448 4340 D 79.9 0.9 0:05 0 httpd
8645 nobody 15 0 14584 9380 5464 D 63.0 0.9 0:04 0 httpd
8654 nobody 16 0 13732 8728 4928 R 36.8 0.8 0:01 0 httpd
8555 nobody 16 0 14252 9.9M 5036 D 21.8 0.9 0:04 0 httpd
8438 nobody 16 0 14636 9556 5444 R 20.6 0.9 0:08 0 httpd
8437 nobody 15 0 14272 9.9M 4440 D 11.2 0.9 0:06 0 httpd
8863 nobody 19 0 10116 3948 1720 R 6.2 0.3 0:00 0 httpd
8890 nobody 20 0 10204 4048 1764 R 5.6 0.3 0:00 0 httpd
8891 nobody 18 0 10240 4256 1800 R 4.3 0.4 0:00 0 httpd
8875 nobody 15 0 1252 1252 784 R 1.8 0.1 0:00 0 top
8903 nobody 21 0 9444 3048 1160 R 0.6 0.2 0:00 0 httpd
8911 nobody 21 0 9168 2620 896 R 0.6 0.2 0:00 0 httpd
8878 nobody 20 0 11596 5736 3040 R 0.0 0.5 0:00 0 httpd
8894 nobody 20 0 9188 2784 916 R 0.0 0.2 0:00 0 httpd
8895 nobody 19 0 9200 2840 928 R 0.0 0.2 0:00 0 httpd
8896 nobody 20 0 10644 4708 2160 R 0.0 0.4 0:00 0 httpd
8899 nobody 20 0 9176 2860 904 R 0.0 0.2 0:00 0 httpd
8901 nobody 20 0 9272 2732 988 R 0.0 0.2 0:00 0 httpd
8902 nobody 20 0 9072 2528 796 R 0.0 0.2 0:00 0 httpd
8904 nobody 20 0 9968 3784 1660 D 0.0 0.3 0:00 0 httpd
8905 nobody 20 0 9176 2632 904 R 0.0 0.2 0:00 0 httpd
8907 nobody 20 0 9188 2836 916 R 0.0 0.2 0:00 0 httpd
Http processes currently running = 87
Mysql processes currently running = 48
Netstat information summary
3 CLOSE_WAIT
5 FIN_WAIT1
8 SYN_RECV
32 LISTEN
51 ESTABLISHED
69 FIN_WAIT2
276 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 14187 |
| Aborted_connects | 1836 |
| Bytes_received | 376186874 |
| Bytes_sent | 792539634 |
| Com_admin_commands | 1048 |
| Com_alter_table | 97 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 29916180 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 19 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 300 |
| Com_delete | 1967353 |
| Com_delete_multi | 0 |
| Com_drop_db | 4 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 65 |
| Com_flush | 323 |
| Com_grant | 158 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1944434 |
| Com_insert_select | 114 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 16789 |
| Com_optimize | 121 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 162 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 68 |
| Com_savepoint | 0 |
| Com_select | 9138683 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 7 |
| Com_show_databases | 444 |
| Com_show_fields | 150 |
| Com_show_grants | 397 |
| Com_show_keys | 51 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 2275 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 143 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1141 |
| Com_show_variables | 240 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 16789 |
| Com_update | 4778910 |
| Connections | 3908922 |
| Created_tmp_disk_tables | 62028 |
| Created_tmp_tables | 179331 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 1930649 |
| Handler_read_first | 229384 |
| Handler_read_key | 31066692 |
| Handler_read_next | 43223180 |
| Handler_read_prev | 664 |
| Handler_read_rnd | 40835010 |
| Handler_read_rnd_next | 4150987660 |
| Handler_rollback | 0 |
| Handler_update | 9984116 |
| Handler_write | 9206471 |
| Key_blocks_used | 48845 |
| Key_read_requests | 85362506 |
| Key_reads | 46262 |
| Key_write_requests | 10925780 |
| Key_writes | 5800963 |
| Max_used_connections | 41 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 348 | 100% of table_cache in use
| Open_files | 563 |
| Open_streams | 0 |
| Opened_tables | 3802 |
| Questions | 79861320 |
| Qcache_queries_in_cache | 6563 |
| Qcache_inserts | 8946143 |
| Qcache_hits | 28175163 |
| Qcache_lowmem_prunes | 37360 |
| Qcache_not_cached | 190692 |
| Qcache_free_memory | 21830320 |
| Qcache_free_blocks | 5056 |
| Qcache_total_blocks | 18612 |
| Rpl_status | NULL |
| Select_full_join | 17210 |
| Select_full_range_join | 31357 |
| Select_range | 25233 |
| Select_range_check | 0 |
| Select_scan | 5484616 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 101 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 248363 |
| Sort_rows | 49323716 |
| Sort_scan | 963319 |
| Table_locks_immediate | 19060394 |
| Table_locks_waited | 75866 |
| Threads_cached | 40 |
| Threads_created | 42 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 682655 | 7 days 21 hrs 37 mins 35 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.000542 (Cache hit = 99.999458%)
Key Writes/Key Write Requests = 0.530943
Connections/second = 5.726 (/hour = 20613.808)
KB received/second = 0.538 (/hour = 1937.331)
KB sent/second = 1.134 (/hour = 4081.521)
Temporary Tables Created/second = 0.263 (/hour = 945.707)
Opened Tables/second = 0.006 (/hour = 20.050)
Slow Queries/second = 0.000 (/hour = 0.533)
% of slow queries = 0.000%
Queries/second = 116.986 (/hour = 421150.877)
MySQL Query Cache hits = 28175163/37311998(76%)
========================================
Well I found the MAIN cause of why my server was so loaded. It was the new PHP 4.3.5 with Cpanel. Shouldn't have had updated to it. IT worked great for most of the night... but with traffic it seems to just suck up all my CPU .
I could use some big help to bring down loads of averaging around 13, and spiking daily at 25.
I've got some very funky numbers:
01:06:34 up 6 days, 22:35, 1 user, load average: 14.93, 14.57, 14.87
174 processes: 157 sleeping, 17 running, 0 zombie, 0 stopped
total 75.9% 0.0% 23.0% 0.0% 0.9% 0.0% 0.0%
Mem: 1028484k av, 836160k used, 192324k free, 0k shrd, 98272k buff
387988k actv, 47032k in_d, 13824k in_c
Swap: 2048276k av, 49076k used, 1999200k free 201276k cached
Http processes currently running = 48
Mysql processes currently running = 22
Netstat information summary
1 FIN_WAIT1
3 CLOSE_WAIT
22 LISTEN
48 ESTABLISHED
357 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 34 |
| Aborted_connects | 11 |
| Bytes_received | 3832753 |
| Bytes_sent | 2161415111 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 14 |
| Com_change_db | 7519 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 14 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 352 |
| 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 | 383 |
| 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 | 18484 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 0 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 15046 |
| Connections | 7546 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 118 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 187 |
| Handler_read_first | 59 |
| Handler_read_key | 34691 |
| Handler_read_next | 51482 |
| Handler_read_prev | 1793 |
| Handler_read_rnd | 11603 |
| Handler_read_rnd_next | 44592656 |
| Handler_rollback | 0 |
| Handler_update | 13495 |
| Handler_write | 10847 |
| Key_blocks_used | 608 |
| Key_read_requests | 391346 |
| Key_reads | 598 |
| Key_write_requests | 15245 |
| Key_writes | 7922 |
| Max_used_connections | 15 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 142 | 14% of table_cache in use
| Open_files | 276 |
| Open_streams | 0 |
| Opened_tables | 148 |
| Questions | 66821 |
| Qcache_queries_in_cache | 885 |
| Qcache_inserts | 18462 |
| Qcache_hits | 17488 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1808 |
| Qcache_free_memory | 30419264 |
| Qcache_free_blocks | 296 |
| Qcache_total_blocks | 2174 |
| Rpl_status | NULL |
| Select_full_join | 21 |
| Select_full_range_join | 0 |
| Select_range | 1765 |
| Select_range_check | 0 |
| Select_scan | 8277 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 50 |
| Sort_rows | 8064 |
| Sort_scan | 273 |
| Table_locks_immediate | 33864 |
| Table_locks_waited | 480 |
| Threads_cached | 12 |
| Threads_created | 16 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 2252 | 37 mins 32 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001528 (Cache hit = 99.998472%)
Key Writes/Key Write Requests = 0.519646
Connections/second = 3.351 (/hour = 12062.877)
KB received/second = 1.662 (/hour = 5981.883)
KB sent/second = 931.239 (/hour = 3352461.634)
Temporary Tables Created/second = 0.052 (/hour = 188.632)
Opened Tables/second = 0.066 (/hour = 236.590)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 29.672 (/hour = 106818.650)
MySQL Query Cache hits = 17488/37758(46%)
Here is my.cnf currently using:
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
max_allowed_packet=16M
max_connect_errors=10000
myisam_sort_buffer_size=64M
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=256M
table_cache=1024
record_buffer=1M
join_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
log-bin
server-id=1
thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
[mysql]
no-auto-rehash
#safe-updates
[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
[mysqlhotcopy]
interactive-timeout
I've got some very funky numbers:
01:06:34 up 6 days, 22:35, 1 user, load average: 14.93, 14.57, 14.87
174 processes: 157 sleeping, 17 running, 0 zombie, 0 stopped
total 75.9% 0.0% 23.0% 0.0% 0.9% 0.0% 0.0%
Mem: 1028484k av, 836160k used, 192324k free, 0k shrd, 98272k buff
387988k actv, 47032k in_d, 13824k in_c
Swap: 2048276k av, 49076k used, 1999200k free 201276k cached
Http processes currently running = 48
Mysql processes currently running = 22
Netstat information summary
1 FIN_WAIT1
3 CLOSE_WAIT
22 LISTEN
48 ESTABLISHED
357 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 34 |
| Aborted_connects | 11 |
| Bytes_received | 3832753 |
| Bytes_sent | 2161415111 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 14 |
| Com_change_db | 7519 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 14 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 352 |
| 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 | 383 |
| 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 | 18484 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 0 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 15046 |
| Connections | 7546 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 118 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 187 |
| Handler_read_first | 59 |
| Handler_read_key | 34691 |
| Handler_read_next | 51482 |
| Handler_read_prev | 1793 |
| Handler_read_rnd | 11603 |
| Handler_read_rnd_next | 44592656 |
| Handler_rollback | 0 |
| Handler_update | 13495 |
| Handler_write | 10847 |
| Key_blocks_used | 608 |
| Key_read_requests | 391346 |
| Key_reads | 598 |
| Key_write_requests | 15245 |
| Key_writes | 7922 |
| Max_used_connections | 15 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 142 | 14% of table_cache in use
| Open_files | 276 |
| Open_streams | 0 |
| Opened_tables | 148 |
| Questions | 66821 |
| Qcache_queries_in_cache | 885 |
| Qcache_inserts | 18462 |
| Qcache_hits | 17488 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1808 |
| Qcache_free_memory | 30419264 |
| Qcache_free_blocks | 296 |
| Qcache_total_blocks | 2174 |
| Rpl_status | NULL |
| Select_full_join | 21 |
| Select_full_range_join | 0 |
| Select_range | 1765 |
| Select_range_check | 0 |
| Select_scan | 8277 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 50 |
| Sort_rows | 8064 |
| Sort_scan | 273 |
| Table_locks_immediate | 33864 |
| Table_locks_waited | 480 |
| Threads_cached | 12 |
| Threads_created | 16 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 2252 | 37 mins 32 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001528 (Cache hit = 99.998472%)
Key Writes/Key Write Requests = 0.519646
Connections/second = 3.351 (/hour = 12062.877)
KB received/second = 1.662 (/hour = 5981.883)
KB sent/second = 931.239 (/hour = 3352461.634)
Temporary Tables Created/second = 0.052 (/hour = 188.632)
Opened Tables/second = 0.066 (/hour = 236.590)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 29.672 (/hour = 106818.650)
MySQL Query Cache hits = 17488/37758(46%)
Here is my.cnf currently using:
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
max_allowed_packet=16M
max_connect_errors=10000
myisam_sort_buffer_size=64M
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=256M
table_cache=1024
record_buffer=1M
join_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
log-bin
server-id=1
thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
[mysql]
no-auto-rehash
#safe-updates
[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
[mysqlhotcopy]
interactive-timeout
Optimisation helps - but not that much. When we moved from the Celeron 2.4 w 1GB ram, we had to a P4 3.0 HT with 512MB Ram, the server dropped to 1.89 at peak time. The server that was doing about 10. That is one thing to cosider if you can afford it.
Also what sort of Hardware are you using ?
Also what sort of Hardware are you using ?
Celeron 1.7 w/ 1GB Ram. We moved from a 1.3 w/1 GB Ram, and that server was running at loads averaging 3. The change in software was from MySQL 3 to MySQL 4, and Apache 1.3 to Apache 2.0. Initially, the move to MySQL 4 made a great improvement in speed. Over the last few weeks, though, loads have skyrocketted.
I found the problem. Apparently, two other hosting companies (or vhosts on their main IP) were making about 100 concurrent connections to our server. Always good to check network via netstat. Problems aren't always software/hardware.
IPs banned via APF... result:
23:50:06 up 7 days, 21:19, 1 user, load average: 0.92, 0.81, 1.71
At least our MySQL and Apache are extremely optimized now.
IPs banned via APF... result:
23:50:06 up 7 days, 21:19, 1 user, load average: 0.92, 0.81, 1.71
At least our MySQL and Apache are extremely optimized now.
This reply is very late, but I'll put it out there for the benefit of everyone else. I recently experimented with a few mods for phpBB. One of them I added was the sub-categories mod. Seems fairly benign, right? Anything but. Load skyrocketted, just because of that one mod. I turned on the slow query log, and found one bad query that was taking forever to process. Did a grep, and lo and behold, it was in the categories mod. Removed the mod, and got back down to regular load levels.
Now, we have a few more users, and I'm running into load trouble again. Here's the output of the php script:
Here's my my.cnf:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections=150
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 1M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
#join_buffer_size = 1M
# record_buffer_size = 1M
wait_timeout = 9600
connect_timeout = 10
skip-innodb
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 8M
thread_cache = 8
thread_concurrency = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Just a few questions. If my key cache hit rate is 99.99979%, does that mean that my key buffer is too large? Also, the query cache isn't caching too much I don't think, the only site running on this server is a phpBB forum, and it would seem that when a user views a post, that post record is updated with the new view count, thus invalidating that cache entry. For things like the post text it should still be caching though.
My loads are pretty terrible as you can see, reaching the 20's and 30's during peak. Anyone have any suggestions for what to do? Thanks in advance!
Later,
Blacks
Now, we have a few more users, and I'm running into load trouble again. Here's the output of the php script:
CODE
Sun Apr 25 16:01:47 CDT 2004
16:01:49 up 61 days, 14:26, 3 users, load average: 15.57, 16.95, 18.43
271 processes: 261 sleeping, 10 running, 0 zombie, 0 stopped
Mem: 1022796k av, 923056k used, 99740k free, 0k shrd, 31984k buff
673616k actv, 67676k in_d, 29944k in_c
Swap: 2048276k av, 176364k used, 1871912k free 376412k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1413 nobody 20 0 1196 1196 800 R 5.0 0.1 0:00 0 top
Http processes currently running = 141
Mysql processes currently running = 6
Netstat information summary
1 CLOSE_WAIT
1 LAST_ACK
1 SYN_RECV
53 LISTEN
130 FIN_WAIT2
174 TIME_WAIT
380 ESTABLISHED
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 8 |
| Bytes_received | 11843392 |
| Bytes_sent | 80433177 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2762 |
| 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 | 843 |
| 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 | 571 |
| Com_insert_select | 7 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 36 |
| 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 | 11674 |
| Com_set_option | 24 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 24 |
| Com_show_databases | 1 |
| Com_show_fields | 24 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 26 |
| Com_show_variables | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 36 |
| Com_update | 3085 |
| Connections | 2786 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 2283 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 218 |
| Handler_read_first | 581 |
| Handler_read_key | 15686575 |
| Handler_read_next | 29983005 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 301014 |
| Handler_read_rnd_next | 32603387 |
| Handler_rollback | 0 |
| Handler_update | 399007 |
| Handler_write | 5794813 |
| Key_blocks_used | 9817 |
| Key_read_requests | 46771390 |
| Key_reads | 9814 |
| Key_write_requests | 3103 |
| Key_writes | 1493 |
| Max_used_connections | 77 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 128 | 100% of table_cache in use
| Open_files | 141 |
| Open_streams | 0 |
| Opened_tables | 566 |
| Questions | 41552 |
| Qcache_queries_in_cache | 894 |
| Qcache_inserts | 11144 |
| Qcache_hits | 19687 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 519 |
| Qcache_free_memory | 7085856 |
| Qcache_free_blocks | 505 |
| Qcache_total_blocks | 2342 |
| Rpl_status | NULL |
| Select_full_join | 73 |
| Select_full_range_join | 0 |
| Select_range | 975 |
| Select_range_check | 0 |
| Select_scan | 3244 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 36 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 1779 |
| Sort_rows | 584504 |
| Sort_scan | 2491 |
| Table_locks_immediate | 23083 |
| Table_locks_waited | 6772 |
| Threads_cached | 7 |
| Threads_created | 473 |
| Threads_connected | 36 |
| Threads_running | 16 |
| Uptime | 1009 | 16 mins 49 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.000210 (Cache hit = 99.99979%)
Key Writes/Key Write Requests = 0.481147
Connections/second = 2.761 (/hour = 9940.139)
KB received/second = 11.462 (/hour = 41262.636)
KB sent/second = 77.847 (/hour = 280250.545)
Temporary Tables Created/second = 2.263 (/hour = 8145.491)
Opened Tables/second = 0.561 (/hour = 2019.425)
Slow Queries/second = 0.036 (/hour = 128.444)
% of slow queries = 0.087%
Queries/second = 41.181 (/hour = 148252.924)
16:01:49 up 61 days, 14:26, 3 users, load average: 15.57, 16.95, 18.43
271 processes: 261 sleeping, 10 running, 0 zombie, 0 stopped
Mem: 1022796k av, 923056k used, 99740k free, 0k shrd, 31984k buff
673616k actv, 67676k in_d, 29944k in_c
Swap: 2048276k av, 176364k used, 1871912k free 376412k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1413 nobody 20 0 1196 1196 800 R 5.0 0.1 0:00 0 top
Http processes currently running = 141
Mysql processes currently running = 6
Netstat information summary
1 CLOSE_WAIT
1 LAST_ACK
1 SYN_RECV
53 LISTEN
130 FIN_WAIT2
174 TIME_WAIT
380 ESTABLISHED
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 8 |
| Bytes_received | 11843392 |
| Bytes_sent | 80433177 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2762 |
| 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 | 843 |
| 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 | 571 |
| Com_insert_select | 7 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 36 |
| 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 | 11674 |
| Com_set_option | 24 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 24 |
| Com_show_databases | 1 |
| Com_show_fields | 24 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 3 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 26 |
| Com_show_variables | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 36 |
| Com_update | 3085 |
| Connections | 2786 |
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 2283 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 218 |
| Handler_read_first | 581 |
| Handler_read_key | 15686575 |
| Handler_read_next | 29983005 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 301014 |
| Handler_read_rnd_next | 32603387 |
| Handler_rollback | 0 |
| Handler_update | 399007 |
| Handler_write | 5794813 |
| Key_blocks_used | 9817 |
| Key_read_requests | 46771390 |
| Key_reads | 9814 |
| Key_write_requests | 3103 |
| Key_writes | 1493 |
| Max_used_connections | 77 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 128 | 100% of table_cache in use
| Open_files | 141 |
| Open_streams | 0 |
| Opened_tables | 566 |
| Questions | 41552 |
| Qcache_queries_in_cache | 894 |
| Qcache_inserts | 11144 |
| Qcache_hits | 19687 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 519 |
| Qcache_free_memory | 7085856 |
| Qcache_free_blocks | 505 |
| Qcache_total_blocks | 2342 |
| Rpl_status | NULL |
| Select_full_join | 73 |
| Select_full_range_join | 0 |
| Select_range | 975 |
| Select_range_check | 0 |
| Select_scan | 3244 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 36 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 1779 |
| Sort_rows | 584504 |
| Sort_scan | 2491 |
| Table_locks_immediate | 23083 |
| Table_locks_waited | 6772 |
| Threads_cached | 7 |
| Threads_created | 473 |
| Threads_connected | 36 |
| Threads_running | 16 |
| Uptime | 1009 | 16 mins 49 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.000210 (Cache hit = 99.99979%)
Key Writes/Key Write Requests = 0.481147
Connections/second = 2.761 (/hour = 9940.139)
KB received/second = 11.462 (/hour = 41262.636)
KB sent/second = 77.847 (/hour = 280250.545)
Temporary Tables Created/second = 2.263 (/hour = 8145.491)
Opened Tables/second = 0.561 (/hour = 2019.425)
Slow Queries/second = 0.036 (/hour = 128.444)
% of slow queries = 0.087%
Queries/second = 41.181 (/hour = 148252.924)
Here's my my.cnf:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections=150
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 1M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
#join_buffer_size = 1M
# record_buffer_size = 1M
wait_timeout = 9600
connect_timeout = 10
skip-innodb
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 8M
thread_cache = 8
thread_concurrency = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Just a few questions. If my key cache hit rate is 99.99979%, does that mean that my key buffer is too large? Also, the query cache isn't caching too much I don't think, the only site running on this server is a phpBB forum, and it would seem that when a user views a post, that post record is updated with the new view count, thus invalidating that cache entry. For things like the post text it should still be caching though.
My loads are pretty terrible as you can see, reaching the 20's and 30's during peak. Anyone have any suggestions for what to do? Thanks in advance!
Later,
Blacks
Hello,
I have a P4 2.8GHz HT with 1gb RAM.
Could anyone recommend a good config for my server. I am using it for a mailing lis that opens one-connection to mysql and runs the commands through that and then disconnects after it is done sending. I use cPanel/EXIM and MySQL 4.018.
It currently only send 13.333333333333333 emails per second which is horrible for the size of this list. My client is the only account on the server and sending speed is the KEY*!
Thanks and God Bless!
I have a P4 2.8GHz HT with 1gb RAM.
Could anyone recommend a good config for my server. I am using it for a mailing lis that opens one-connection to mysql and runs the commands through that and then disconnects after it is done sending. I use cPanel/EXIM and MySQL 4.018.
It currently only send 13.333333333333333 emails per second which is horrible for the size of this list. My client is the only account on the server and sending speed is the KEY*!
Thanks and God Bless!
Hello all,
I have a Celeron 1.3 with 1GB RAM running MySQL 4.0.13 (PHP 4.3.1 & ZendOptimizer) and on that server I have some phpbb forums as well as some php/mysql based sites. Below you can find the my.cnf file, I'd appreciate your thoughts on how to modify it to improve the perfomance.
Any other thoughts on how to improve performace on such a server would also be greatly appreciated, thanks.
Thanks in advance.
/Marcus
I have a Celeron 1.3 with 1GB RAM running MySQL 4.0.13 (PHP 4.3.1 & ZendOptimizer) and on that server I have some phpbb forums as well as some php/mysql based sites. Below you can find the my.cnf file, I'd appreciate your thoughts on how to modify it to improve the perfomance.
Any other thoughts on how to improve performace on such a server would also be greatly appreciated, thanks.
QUOTE
[mysqld]
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-locking
connect_timeout=15
max_allowed_packet=16M
table_cache=512
key_buffer=150M
join_buffer=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
sort_buffer=1M
thread_cache_size=40
thread_concurrency=2
myisam_sort_buffer_size=64M
max_connections=400
wait_timeout=100
interactive_timeout=100
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-locking
connect_timeout=15
max_allowed_packet=16M
table_cache=512
key_buffer=150M
join_buffer=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
sort_buffer=1M
thread_cache_size=40
thread_concurrency=2
myisam_sort_buffer_size=64M
max_connections=400
wait_timeout=100
interactive_timeout=100
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
Thanks in advance.
/Marcus
Hello,
I have a lot of sites that use mysql and the server specs are :
1 GB ECC DDRAM
2.4 Ghz P4
apache and mysql both go crazy at times, this isn't a busy time and these are the stats:
05:11:58 up 30 days, 10:19, 1 user, load average: 1.48, 1.10, 0.78
168 processes: 166 sleeping, 1 running, 1 zombie, 0 stopped
Mem: 1032320k av, 1019408k used, 12912k free, 0k shrd, 50716k buff
471828k active, 459036k inactive
Swap: 2104504k av, 79744k used, 2024760k free 627720k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
29169 nobody 16 0 1244 1244 852 R 10.6 0.1 0:00 0 top
Http processes currently running = 77
Mysql processes currently running = 3
Netstat information summary
1 SYN_SENT
2 CLOSE_WAIT
2 LAST_ACK
4 FIN_WAIT1
24 LISTEN
41 FIN_WAIT2
75 ESTABLISHED
133 TIME_WAIT
--------- MYSQL--------------
Key Reads/Key Read Requests = 0.003713 (Cache hit = 99.996287%)
Key Writes/Key Write Requests = 0.234114
Connections/second = 2.716 (/hour = 9778.571)
KB received/second = 15.585 (/hour = 56107.143)
KB sent/second = 120.317 (/hour = 433142.857)
Temporary Tables Created/second = 1.159 (/hour = 4171.429)
Opened Tables/second = 1.506 (/hour = 5421.429)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 162.718 (/hour = 585785.714)
This isn't a busy time and see the number of queries/ second. I think there is still a room for improvment.
For apache I have kept keepalive on because if i set it to off then there are a lot of TIME_WAIT connections.
Any help would be greately appreciated, I have used the my.cnf aussie posted, except that i have wait_timeout set to 900 instead of 100.
thanks...
I have a lot of sites that use mysql and the server specs are :
1 GB ECC DDRAM
2.4 Ghz P4
apache and mysql both go crazy at times, this isn't a busy time and these are the stats:
05:11:58 up 30 days, 10:19, 1 user, load average: 1.48, 1.10, 0.78
168 processes: 166 sleeping, 1 running, 1 zombie, 0 stopped
Mem: 1032320k av, 1019408k used, 12912k free, 0k shrd, 50716k buff
471828k active, 459036k inactive
Swap: 2104504k av, 79744k used, 2024760k free 627720k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
29169 nobody 16 0 1244 1244 852 R 10.6 0.1 0:00 0 top
Http processes currently running = 77
Mysql processes currently running = 3
Netstat information summary
1 SYN_SENT
2 CLOSE_WAIT
2 LAST_ACK
4 FIN_WAIT1
24 LISTEN
41 FIN_WAIT2
75 ESTABLISHED
133 TIME_WAIT
--------- MYSQL--------------
Key Reads/Key Read Requests = 0.003713 (Cache hit = 99.996287%)
Key Writes/Key Write Requests = 0.234114
Connections/second = 2.716 (/hour = 9778.571)
KB received/second = 15.585 (/hour = 56107.143)
KB sent/second = 120.317 (/hour = 433142.857)
Temporary Tables Created/second = 1.159 (/hour = 4171.429)
Opened Tables/second = 1.506 (/hour = 5421.429)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 162.718 (/hour = 585785.714)
This isn't a busy time and see the number of queries/ second. I think there is still a room for improvment.
For apache I have kept keepalive on because if i set it to off then there are a lot of TIME_WAIT connections.
Any help would be greately appreciated, I have used the my.cnf aussie posted, except that i have wait_timeout set to 900 instead of 100.
thanks...
QUOTE
Originally posted by NAPPA
Lasttime I counted there were 15 My SQL processes running on my new server
The load is hovering between 1.3 - 2.0 on peak times.
The following is my Configuration
Celeron 2.4
1GB Ram
80GB HDD x2
Swap is 512MB
============================
[mysqld]
# max_connections=500
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=50
wait_timeout=50
connect_timeout=10
thread_cache_size=50
key_buffer=50M
join_buffer=1M
max_allowed_packet=16M
table_cache=348
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=32M
======================================
Is there something Horribly wrong. Now the minimum load I get is at .30 It used to be .03
Lasttime I counted there were 15 My SQL processes running on my new server
The load is hovering between 1.3 - 2.0 on peak times.
The following is my Configuration
Celeron 2.4
1GB Ram
80GB HDD x2
Swap is 512MB
============================
[mysqld]
# max_connections=500
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=50
wait_timeout=50
connect_timeout=10
thread_cache_size=50
key_buffer=50M
join_buffer=1M
max_allowed_packet=16M
table_cache=348
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=32M
======================================
Is there something Horribly wrong. Now the minimum load I get is at .30 It used to be .03
Thats nothing, the other morning we were notified by a few clients that they were getting a MYSQL TOO BUSY msg when attempting to connect to their db's. What did we find? Over 500mysql processes running. Still trying to work out what happened there.
Has everyone running Cpanel boxes upgraded to the latest version of MySQL?
QUOTE
Originally posted by aussie
Thats nothing, the other morning we were notified by a few clients that they were getting a MYSQL TOO BUSY msg when attempting to connect to their db's. What did we find? Over 500mysql processes running. Still trying to work out what happened there.
Has everyone running Cpanel boxes upgraded to the latest version of MySQL?
Thats nothing, the other morning we were notified by a few clients that they were getting a MYSQL TOO BUSY msg when attempting to connect to their db's. What did we find? Over 500mysql processes running. Still trying to work out what happened there.
Has everyone running Cpanel boxes upgraded to the latest version of MySQL?
Yup, MySQL 4.0.20-standard.
I had some problems with it when trying to restart MySQL with InnoDB tables - it's apparently buggy with InnoDB. I converted InnoDB tables to MyISAM and that fixed it.
ahh, so do you think its due to 4.0.20, my server was fine until it automatically upgraded to v 4.0.20, used to be 4.0.18 ago ...
it sucks
it sucks
Hey Everyone,
I need help with my server. I've had an average load of 20 for the last week, due to very heavy MySQL usage.
I have a Celeron 2.0Ghz and 1GB RAM. MySQL 4.0.2
What can you recommend? I'm getting hammered.
I need help with my server. I've had an average load of 20 for the last week, due to very heavy MySQL usage.
I have a Celeron 2.0Ghz and 1GB RAM. MySQL 4.0.2
What can you recommend? I'm getting hammered.
Hmh, i get results that i didnt see here yet. This is fresh installation of RHE , 3.2 Ghz P4 with HT, 1 GB OF RAM.
[root@matrix html]# php -v
PHP 5.0.1 (cli) (built: Sep 13 2004 17:52:38)
self compiled php
[root@matrix html]# mysql -V
mysql Ver 12.22 Distrib 4.0.21, for pc-linux (i686)
rpm installation of mysql
this is my my.cnf file:
[mysqld]
max_connections = 500
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 128
thread_concurrency=2
interactive_timeout=100
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
These are mysql results i am getting:
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 942 |
| Bytes_sent | 52414 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| 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 | 0 |
| 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 | 0 |
| 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 | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 8 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 8 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Connections | 18 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 3 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 12 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 0 | 0% of table_cache in use
| Open_files | 0 |
| Open_streams | 0 |
| Opened_tables | 6 |
| Questions | 32 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 33545640 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 6 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 1 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 590 | 9 mins 50 secs
+--------------------------+----------+
Connections/second = 0.031 (/hour = 109.831)
KB received/second = 0.000 (/hour = 0.000)
KB sent/second = 0.086 (/hour = 311.186)
Temporary Tables Created/second = 0.000 (/hour = 0.000)
Opened Tables/second = 0.010 (/hour = 36.610)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 0.054 (/hour = 195.254)
MySQL Query Cache hits = 0/0(0%)
Obiovulsy something is very wrong, also i forgot to mention:
[root@matrix html]# uname -a
Linux matrix.mydomain.net 2.6.8.1 #2 SMP Sun Sep 12 06:51:08 CDT 2004 i686 i686 i386 GNU/Linux
I compiled newest version of kernel myself.
Help is welcome
[root@matrix html]# php -v
PHP 5.0.1 (cli) (built: Sep 13 2004 17:52:38)
self compiled php
[root@matrix html]# mysql -V
mysql Ver 12.22 Distrib 4.0.21, for pc-linux (i686)
rpm installation of mysql
this is my my.cnf file:
[mysqld]
max_connections = 500
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 128
thread_concurrency=2
interactive_timeout=100
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
These are mysql results i am getting:
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 942 |
| Bytes_sent | 52414 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| 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 | 0 |
| 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 | 0 |
| 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 | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 8 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 8 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Connections | 18 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 3 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 12 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 0 | 0% of table_cache in use
| Open_files | 0 |
| Open_streams | 0 |
| Opened_tables | 6 |
| Questions | 32 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 33545640 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 6 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 1 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 590 | 9 mins 50 secs
+--------------------------+----------+
Connections/second = 0.031 (/hour = 109.831)
KB received/second = 0.000 (/hour = 0.000)
KB sent/second = 0.086 (/hour = 311.186)
Temporary Tables Created/second = 0.000 (/hour = 0.000)
Opened Tables/second = 0.010 (/hour = 36.610)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 0.054 (/hour = 195.254)
MySQL Query Cache hits = 0/0(0%)
Obiovulsy something is very wrong, also i forgot to mention:
[root@matrix html]# uname -a
Linux matrix.mydomain.net 2.6.8.1 #2 SMP Sun Sep 12 06:51:08 CDT 2004 i686 i686 i386 GNU/Linux
I compiled newest version of kernel myself.
Help is welcome
Hello,
How do you do th receive this MySQL stats, and what do we have to consider for tuning ?
Thanks
Best regards
Jean
How do you do th receive this MySQL stats, and what do we have to consider for tuning ?
Thanks
Best regards
Jean
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.