Actually, they recommend using binary logging in place of the update log.
But if you don't log any of that, then you are saving cpu time, especially on a busy server with lots of updates.
Full Version: HOWTO: Optimize MySQL 4.0.12 after installation 2-Parts
QUOTE
Originally posted by blacks
aussie, I'm curious why you suggest using the log-bin directive, in your my.cnf? I admit, I blindly used your provided my.cnf file, tweaking a few of the caching directives, but I never looked long and hard at the rest of the options. log-bin is a neat directive, if you are involved in mysql replication, but for the majority of users reading this guide, I believe it is unnecessary.
Note, I found this unnecessary option, while spelunking around /var/lib/mysql, and discovering a 1 gigabyte file full of these UPDATE transactions! The CPU time spent writing to this file could have been used for other tasks.
Great guide, I just want to shed some light on that one option.
Blacks
aussie, I'm curious why you suggest using the log-bin directive, in your my.cnf? I admit, I blindly used your provided my.cnf file, tweaking a few of the caching directives, but I never looked long and hard at the rest of the options. log-bin is a neat directive, if you are involved in mysql replication, but for the majority of users reading this guide, I believe it is unnecessary.
Note, I found this unnecessary option, while spelunking around /var/lib/mysql, and discovering a 1 gigabyte file full of these UPDATE transactions! The CPU time spent writing to this file could have been used for other tasks.
Great guide, I just want to shed some light on that one option.
Blacks
spelunking around
Love that! Great choice of words. I will add that to my dictionary of words to use.
Which option exactly were you referrring too? Is that to or too?
quite off topic , howeve how to read the netstat report ?
for example , I have
Netstat information summary
6 FIN_WAIT2
29 ESTABLISHED
70 TIME_WAIT
93 LISTEN
If I refresh the page only Listen on port 93 doesn't change .....
for example , I have
Netstat information summary
6 FIN_WAIT2
29 ESTABLISHED
70 TIME_WAIT
93 LISTEN
If I refresh the page only Listen on port 93 doesn't change .....
QUOTE
Originally posted by whatgives
After installing MySQL 4, the mysqld process no longer runs as 'nice'
i.e. it shows up with the S flag on ps, not like before with SN flags:
mysql 5867 0.0 3.2 122200 67420 pts/0 S 03:35 0:13 /usr/sbin/mysqld
Anyone knows to make it run as nice like before?
After installing MySQL 4, the mysqld process no longer runs as 'nice'
i.e. it shows up with the S flag on ps, not like before with SN flags:
mysql 5867 0.0 3.2 122200 67420 pts/0 S 03:35 0:13 /usr/sbin/mysqld
Anyone knows to make it run as nice like before?
Just FYI, this has been fixed in 4.0.14
The directive I was referring to was "log-bin". From what I had read, it is only useful in replication between different servers.
perlchild's comment confused me though, because I had seen no comments (outside a replication context) referring to the log-bin directive, or even binary logging in general.
I had removed that directive, and have been running fine since then, but I'm curious what made you (aussie) decide to put it in the my.cnf that you posted? Right now it's pretty much for academic reasons, I have been running without the directive being used for the last month or so, without trouble, but am always looking for a tip to squeeze any bit of performance I can out of my server!
Blacks
perlchild's comment confused me though, because I had seen no comments (outside a replication context) referring to the log-bin directive, or even binary logging in general.
I had removed that directive, and have been running fine since then, but I'm curious what made you (aussie) decide to put it in the my.cnf that you posted? Right now it's pretty much for academic reasons, I have been running without the directive being used for the last month or so, without trouble, but am always looking for a tip to squeeze any bit of performance I can out of my server!
Blacks
QUOTE
Originally posted by blacks
The directive I was referring to was "log-bin". From what I had read, it is only useful in replication between different servers.
perlchild's comment confused me though, because I had seen no comments (outside a replication context) referring to the log-bin directive, or even binary logging in general.
The directive I was referring to was "log-bin". From what I had read, it is only useful in replication between different servers.
perlchild's comment confused me though, because I had seen no comments (outside a replication context) referring to the log-bin directive, or even binary logging in general.
I'm sorry if I confused you, I happen to be the kind of person who never gets enough logs... So turning off log-update and log-query never occured to me(optimising them yes, /dev/nulling the info no)
Just one addition to this. You may want to consider using InnoDB for performance tuning. This requires converting all your databases and a bit more extra setup work, but supposedly you gain speed as well as a few other advantages:
1) transactions
2) row level locking!!
3) ability to make hot backups (doesn't do any lock on the database while making a backup)
The third one requires some extra not-so-free software, but if you are like me and running 200-300 queries per second, this is kinda useful.
1) transactions
2) row level locking!!
3) ability to make hot backups (doesn't do any lock on the database while making a backup)
The third one requires some extra not-so-free software, but if you are like me and running 200-300 queries per second, this is kinda useful.
i'm having the same problem here is my my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[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
and Dave's script output:
Mon Aug 18 22:29:58 AST 2003
10:29pm up 7 days, 15:45, 1 user, load average: 4.75, 4.30, 5.14
193 processes: 184 sleeping, 8 running, 1 zombie, 0 stopped
CPU0 states: 94.0% user, 5.1% system, 0.0% nice, 0.0% idle
CPU1 states: 48.0% user, 17.0% system, 0.0% nice, 33.1% idle
CPU2 states: 52.1% user, 5.1% system, 0.0% nice, 41.0% idle
CPU3 states: 42.0% user, 16.0% system, 0.0% nice, 41.0% idle
Mem: 2064716K av, 2047708K used, 17008K free, 0K shrd, 20280K buff
Swap: 2048276K av, 39328K used, 2008948K free 1764940K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
3687 mysql 19 0 45448 44M 1452 R 54.1 2.2 0:23 mysqld
3544 mysql 18 0 45316 44M 1452 R 47.4 2.1 0:31 mysqld
3572 mysql 17 0 45364 44M 1452 R 38.1 2.1 0:32 mysqld
3590 mysql 17 0 45388 44M 1452 R 32.1 2.1 0:44 mysqld
13000 nobody 16 0 1096 1096 828 R 13.5 0.0 0:00 top
3508 mysql 22 0 45280 44M 1452 R 0.0 2.1 0:37 mysqld
12966 nobody 16 0 6984 6984 3900 R 0.0 0.3 0:00 httpd
Http processes currently running = 77
Mysql processes currently running = 48
Netstat information summary
1 LAST_ACK
2 CLOSING
4 FIN_WAIT2
8 SYN_RECV
12 CLOSE_WAIT
15 FIN_WAIT1
22 LISTEN
30 ESTABLISHED
628 TIME_WAIT
and its only running ONE heavy vbulletin forum website! what do i need to change?
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[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
and Dave's script output:
Mon Aug 18 22:29:58 AST 2003
10:29pm up 7 days, 15:45, 1 user, load average: 4.75, 4.30, 5.14
193 processes: 184 sleeping, 8 running, 1 zombie, 0 stopped
CPU0 states: 94.0% user, 5.1% system, 0.0% nice, 0.0% idle
CPU1 states: 48.0% user, 17.0% system, 0.0% nice, 33.1% idle
CPU2 states: 52.1% user, 5.1% system, 0.0% nice, 41.0% idle
CPU3 states: 42.0% user, 16.0% system, 0.0% nice, 41.0% idle
Mem: 2064716K av, 2047708K used, 17008K free, 0K shrd, 20280K buff
Swap: 2048276K av, 39328K used, 2008948K free 1764940K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
3687 mysql 19 0 45448 44M 1452 R 54.1 2.2 0:23 mysqld
3544 mysql 18 0 45316 44M 1452 R 47.4 2.1 0:31 mysqld
3572 mysql 17 0 45364 44M 1452 R 38.1 2.1 0:32 mysqld
3590 mysql 17 0 45388 44M 1452 R 32.1 2.1 0:44 mysqld
13000 nobody 16 0 1096 1096 828 R 13.5 0.0 0:00 top
3508 mysql 22 0 45280 44M 1452 R 0.0 2.1 0:37 mysqld
12966 nobody 16 0 6984 6984 3900 R 0.0 0.3 0:00 httpd
Http processes currently running = 77
Mysql processes currently running = 48
Netstat information summary
1 LAST_ACK
2 CLOSING
4 FIN_WAIT2
8 SYN_RECV
12 CLOSE_WAIT
15 FIN_WAIT1
22 LISTEN
30 ESTABLISHED
628 TIME_WAIT
and its only running ONE heavy vbulletin forum website! what do i need to change?
your key buffer is very small.
you might want to try installing mtop and using it to monitor your hit rates.
I personally use around 512meg key buffers, but my databases have very large index's with lots of rows to sort.
My settings are likely not going to be good for most people, but you can give them a try if your existing settings are bad. Keep in mind this is setup for my own software and that my servers have 2 gig ram.
[mysqld]
set-variable = query_cache_type=1
set-variable = query_cache_limit=10M
set-variable = query_cache_size=150M
set-variable = max_connections=512
set-variable = ft_max_word_len=30
set-variable = ft_max_word_len_for_sort=20
set-variable = ft_min_word_len=2
bind-address = 127.0.0.1
port = 3306
set-variable = delay_key_write=ON
set-variable = delayed_queue_size=10000
set-variable = delayed_insert_limit=50
set-variable = max_delayed_threads=8
set-variable = connect_timeout=10
set-variable = interactive_timeout=100
set-variable = wait_timeout=60
set-variable = low-priority-updates=ON
set-variable = key_buffer=550M
set-variable = table_cache=5500
set-variable = sort_buffer_size=5M
set-variable = join_buffer_size=5M
set-variable = read_buffer_size=5M
set-variable = read_rnd_buffer_size=5M
set-variable = tmp_table_size=400M
set-variable = myisam_sort_buffer_size=256M
set-variable = thread_cache=256
set-variable = thread_stack=256000
set-variable = thread_concurrency=4
server-id = 1
set-variable = max_allowed_packet=20M
set-variable = flush_time=9600
skip-bdb
skip-innodb
you might want to try installing mtop and using it to monitor your hit rates.
I personally use around 512meg key buffers, but my databases have very large index's with lots of rows to sort.
My settings are likely not going to be good for most people, but you can give them a try if your existing settings are bad. Keep in mind this is setup for my own software and that my servers have 2 gig ram.
[mysqld]
set-variable = query_cache_type=1
set-variable = query_cache_limit=10M
set-variable = query_cache_size=150M
set-variable = max_connections=512
set-variable = ft_max_word_len=30
set-variable = ft_max_word_len_for_sort=20
set-variable = ft_min_word_len=2
bind-address = 127.0.0.1
port = 3306
set-variable = delay_key_write=ON
set-variable = delayed_queue_size=10000
set-variable = delayed_insert_limit=50
set-variable = max_delayed_threads=8
set-variable = connect_timeout=10
set-variable = interactive_timeout=100
set-variable = wait_timeout=60
set-variable = low-priority-updates=ON
set-variable = key_buffer=550M
set-variable = table_cache=5500
set-variable = sort_buffer_size=5M
set-variable = join_buffer_size=5M
set-variable = read_buffer_size=5M
set-variable = read_rnd_buffer_size=5M
set-variable = tmp_table_size=400M
set-variable = myisam_sort_buffer_size=256M
set-variable = thread_cache=256
set-variable = thread_stack=256000
set-variable = thread_concurrency=4
server-id = 1
set-variable = max_allowed_packet=20M
set-variable = flush_time=9600
skip-bdb
skip-innodb
you mena it might break any software setup?
i'm running vbulletin forum
some settings in your my.cnf are for special software which might not be compatabile with vb ?
i'm running vbulletin forum
some settings in your my.cnf are for special software which might not be compatabile with vb ?
No, it's not going to break anything (unless you use an old mysql that doesn't understand some of the settings detailed here).
The problem you may encounter though, is wasted ram. e.g; using over 500 megs in the key buffer is wasteful if your server isn't going to make use of it.
Also, if you get a LOT of visitors, things that are on a per connection basis such as sort buffer, can make your system crawl to a halt if each thread uses 5 megs. So you have to be aware of how your site operates and what can and can't be done.
If you do something wrong, make sure you have a backup of your original cnf file handy
A mistake will make itself painfully known.
The problem you may encounter though, is wasted ram. e.g; using over 500 megs in the key buffer is wasteful if your server isn't going to make use of it.
Also, if you get a LOT of visitors, things that are on a per connection basis such as sort buffer, can make your system crawl to a halt if each thread uses 5 megs. So you have to be aware of how your site operates and what can and can't be done.
If you do something wrong, make sure you have a backup of your original cnf file handy
A mistake will make itself painfully known.
i have a backup of my.cnf
i placed your settings now and monitoring
nothing changed load almost the same
the forum has a load such as webhostingtalk.com how come their website doesn't get slow or load?
i placed your settings now and monitoring
nothing changed load almost the same
the forum has a load such as webhostingtalk.com how come their website doesn't get slow or load?
Hmmm, well, a forum with proper index settings will be very quick to load data, and there will not be much load on the database at all. I don't run vb though, so I'm not going to be of much help there.
One thing I can mention is, removing the Hyper Threading from my dual xeon boxes made mysql queries process faster (on the range of 20-30%). You may want to try that out -- though in your situation, I don't know if it will lower the load much.
One thing I can mention is, removing the Hyper Threading from my dual xeon boxes made mysql queries process faster (on the range of 20-30%). You may want to try that out -- though in your situation, I don't know if it will lower the load much.
how to remove the hyper threading?
To disable HT, read here: http://forum.rackshack.net/showthread.php?...&threadid=26073
Also, there were people who posted there who claimed better performance with vB after it being disabled.
Also, there were people who posted there who claimed better performance with vB after it being disabled.
thank you!
My server load sometimes raises to 3. I have upgraded to mySQl 4 and i have made my.cnf same as the second post by aussie.
There is a little decrease in the load, but still its too much.
I have 284 httpd processes running. Is this due to that or mySQL ?
------------------------------------------------------------------------------------
Thu Sep 11 22:39:04 GMT 2003
10:39pm up 1:00, 1 user, load average: 2.51, 1.95, 1.96
368 processes: 367 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 1005084K used, 26120K free, 0K shrd, 68460K buff
Swap: 1052248K av, 161800K used, 890448K free 306024K cached
Http processes currently running = 284
Mysql processes currently running = 17
Netstat information summary
1 CLOSE_WAIT
1 LAST_ACK
3 SYN_SENT
4 SYN_RECV
6 CLOSING
7 FIN_WAIT2
18 ESTABLISHED
19 LISTEN
332 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 1 |
| Aborted_connects | 3 |
| Bytes_received | 4941067 |
| Bytes_sent | 957156014 |
| Com_admin_commands | 0 |
| Com_alter_table | 21 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 8623 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 2 |
| Com_create_table | 5 |
| Com_delete | 508 |
| 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 | 396 |
| Com_insert_select | 5 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 12194 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 3 |
| Com_replace | 21 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 10415 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 46 |
| Com_show_fields | 3 |
| Com_show_grants | 2 |
| Com_show_keys | 5 |
| 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 | 33 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1841 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1921 |
| Connections | 2369 |
| Created_tmp_disk_tables | 151 |
| Created_tmp_tables | 1398 |
| Created_tmp_files | 12 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 346 |
| Handler_read_first | 840 |
| Handler_read_key | 402653 |
| Handler_read_next | 3256618 |
| Handler_read_prev | 152 |
| Handler_read_rnd | 6910802 |
| Handler_read_rnd_next | 19957505 |
| Handler_rollback | 0 |
| Handler_update | 30908 |
| Handler_write | 217713 |
| Key_blocks_used | 54621 |
| Key_read_requests | 2129230 |
| Key_reads | 51669 |
| Key_write_requests | 318917 |
| Key_writes | 7832 |
| Max_used_connections | 10 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 | 100% of table_cache in use
| Open_files | 1002 |
| Open_streams | 0 |
| Opened_tables | 12837 |
| Questions | 52564 |
| Qcache_queries_in_cache | 2402 |
| Qcache_inserts | 9611 |
| Qcache_hits | 13766 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 563 |
| Qcache_free_memory | 27855288 |
| Qcache_free_blocks | 369 |
| Qcache_total_blocks | 5731 |
| Rpl_status | NULL |
| Select_full_join | 120 |
| Select_full_range_join | 5 |
| Select_range | 93 |
| Select_range_check | 0 |
| Select_scan | 4658 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 4 | (execution time > 10 secs)
| Sort_merge_passes | 1 |
| Sort_range | 901 |
| Sort_rows | 6919153 |
| Sort_scan | 2213 |
| Table_locks_immediate | 31531 |
| Table_locks_waited | 10 |
| Threads_cached | 10 |
| Threads_created | 11 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1731 | 28 mins 51 secs
+--------------------------+-----------+
Key Reads/Key Read Requests = 0.024267 (Cache hit = 99.975733%)
Key Writes/Key Write Requests = 0.024558
Connections/second = 1.369 (/hour = 4926.863)
KB received/second = 2.787 (/hour = 10034.662)
KB sent/second = 539.990 (/hour = 1943962.565)
Temporary Tables Created/second = 0.808 (/hour = 2907.452)
Opened Tables/second = 7.416 (/hour = 26697.400)
Slow Queries/second = 0.002 (/hour = 8.319)
% of slow queries = 0.008%
Queries/second = 30.366 (/hour = 109318.544)
MySQL Query Cache hits = 13766/23940(58%)
There is a little decrease in the load, but still its too much.
I have 284 httpd processes running. Is this due to that or mySQL ?
------------------------------------------------------------------------------------
Thu Sep 11 22:39:04 GMT 2003
10:39pm up 1:00, 1 user, load average: 2.51, 1.95, 1.96
368 processes: 367 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 1005084K used, 26120K free, 0K shrd, 68460K buff
Swap: 1052248K av, 161800K used, 890448K free 306024K cached
Http processes currently running = 284
Mysql processes currently running = 17
Netstat information summary
1 CLOSE_WAIT
1 LAST_ACK
3 SYN_SENT
4 SYN_RECV
6 CLOSING
7 FIN_WAIT2
18 ESTABLISHED
19 LISTEN
332 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 1 |
| Aborted_connects | 3 |
| Bytes_received | 4941067 |
| Bytes_sent | 957156014 |
| Com_admin_commands | 0 |
| Com_alter_table | 21 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 8623 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 2 |
| Com_create_table | 5 |
| Com_delete | 508 |
| 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 | 396 |
| Com_insert_select | 5 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 12194 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 3 |
| Com_replace | 21 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 10415 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 46 |
| Com_show_fields | 3 |
| Com_show_grants | 2 |
| Com_show_keys | 5 |
| 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 | 33 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1841 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1921 |
| Connections | 2369 |
| Created_tmp_disk_tables | 151 |
| Created_tmp_tables | 1398 |
| Created_tmp_files | 12 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 346 |
| Handler_read_first | 840 |
| Handler_read_key | 402653 |
| Handler_read_next | 3256618 |
| Handler_read_prev | 152 |
| Handler_read_rnd | 6910802 |
| Handler_read_rnd_next | 19957505 |
| Handler_rollback | 0 |
| Handler_update | 30908 |
| Handler_write | 217713 |
| Key_blocks_used | 54621 |
| Key_read_requests | 2129230 |
| Key_reads | 51669 |
| Key_write_requests | 318917 |
| Key_writes | 7832 |
| Max_used_connections | 10 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 | 100% of table_cache in use
| Open_files | 1002 |
| Open_streams | 0 |
| Opened_tables | 12837 |
| Questions | 52564 |
| Qcache_queries_in_cache | 2402 |
| Qcache_inserts | 9611 |
| Qcache_hits | 13766 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 563 |
| Qcache_free_memory | 27855288 |
| Qcache_free_blocks | 369 |
| Qcache_total_blocks | 5731 |
| Rpl_status | NULL |
| Select_full_join | 120 |
| Select_full_range_join | 5 |
| Select_range | 93 |
| Select_range_check | 0 |
| Select_scan | 4658 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 4 | (execution time > 10 secs)
| Sort_merge_passes | 1 |
| Sort_range | 901 |
| Sort_rows | 6919153 |
| Sort_scan | 2213 |
| Table_locks_immediate | 31531 |
| Table_locks_waited | 10 |
| Threads_cached | 10 |
| Threads_created | 11 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1731 | 28 mins 51 secs
+--------------------------+-----------+
Key Reads/Key Read Requests = 0.024267 (Cache hit = 99.975733%)
Key Writes/Key Write Requests = 0.024558
Connections/second = 1.369 (/hour = 4926.863)
KB received/second = 2.787 (/hour = 10034.662)
KB sent/second = 539.990 (/hour = 1943962.565)
Temporary Tables Created/second = 0.808 (/hour = 2907.452)
Opened Tables/second = 7.416 (/hour = 26697.400)
Slow Queries/second = 0.002 (/hour = 8.319)
% of slow queries = 0.008%
Queries/second = 30.366 (/hour = 109318.544)
MySQL Query Cache hits = 13766/23940(58%)
QUOTE
Originally posted by important
My server load sometimes raises to 3. I have upgraded to mySQl 4 and i have made my.cnf same as the second post by aussie.
There is a little decrease in the load, but still its too much.
I have 284 httpd processes running. Is this due to that or mySQL ?
My server load sometimes raises to 3. I have upgraded to mySQl 4 and i have made my.cnf same as the second post by aussie.
There is a little decrease in the load, but still its too much.
I have 284 httpd processes running. Is this due to that or mySQL ?
If KeepAlive is on turn it off
(sorry for my english, im a spanish user)
Today i upgraded mysql 3.23 to 4.0.14, i ran mysql_fix_privilege_tables and optimized my.cnf with this how-to.
All work very fine, but when i enter into phpmyadmin with my user/pass i can see all databases in the server (only see, not modify) :confused:
Any fix?
---- Great How-to
Today i upgraded mysql 3.23 to 4.0.14, i ran mysql_fix_privilege_tables and optimized my.cnf with this how-to.
All work very fine, but when i enter into phpmyadmin with my user/pass i can see all databases in the server (only see, not modify) :confused:
Any fix?
---- Great How-to
KeepAlive is off already.
Today its even more :
Fri Sep 12 20:15:28 GMT 2003
8:15pm up 22:37, 1 user, load average: 1.71, 2.16, 2.75
471 processes: 470 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 1014432K used, 16772K free, 0K shrd, 70632K buff
Swap: 1052248K av, 296648K used, 755600K free 272060K cached
Http processes currently running = 292
Mysql processes currently running = 56
Any more ideas ?
Thanks for any replies.
Today its even more :
Fri Sep 12 20:15:28 GMT 2003
8:15pm up 22:37, 1 user, load average: 1.71, 2.16, 2.75
471 processes: 470 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 1014432K used, 16772K free, 0K shrd, 70632K buff
Swap: 1052248K av, 296648K used, 755600K free 272060K cached
Http processes currently running = 292
Mysql processes currently running = 56
Any more ideas ?
Thanks for any replies.
Is 4.0 compatible with ensim pro? I heard some compatability issues were present
Please advise
Please advise
I upgraded to Mysql 4 but im just not seeing the performance that I thought I would. Here is my stats:
11:31pm up 48 days, 1:43, 1 users, load average: 1.45, 1.57, 1.45
128 processes: 127 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1023128K av, 981532K used, 41596K free, 0K shrd, 62588K buff
Swap: 2048276K av, 79032K used, 1969244K free 666948K cached
Key Reads/Key Read Requests = 0.000109 (Cache hit = 99.999891%)
Key Writes/Key Write Requests = 0.026460
Connections/second = 2.470 (/hour = 8892.131)
KB received/second = 1.217 (/hour = 4382.282)
KB sent/second = 10.821 (/hour = 38956.540)
Temporary Tables Created/second = 0.200 (/hour = 720.375)
Opened Tables/second = 0.029 (/hour = 104.117)
Slow Queries/second = 0.002 (/hour = 6.566)
% of slow queries = 0.012%
Queries/second = 14.768 (/hour = 53163.314)
MySQL Query Cache hits = 18313/22730(81%)
my.cnf:
[mysqld]
bind-address=127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_type=1
query_cache_limit =2M
query_cache_size=32M
key_buffer=40M
thread_concurrency=2
interactive_timeout=100
table_cache=384
join_buffer_size=512K
read_rnd_buffer_size=512K
read_buffer_size=512K
max_connections=800
max_connect_errors=65535
connect_timeout=30
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
does the settings and stats look good? Any suggestions?
11:31pm up 48 days, 1:43, 1 users, load average: 1.45, 1.57, 1.45
128 processes: 127 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1023128K av, 981532K used, 41596K free, 0K shrd, 62588K buff
Swap: 2048276K av, 79032K used, 1969244K free 666948K cached
Key Reads/Key Read Requests = 0.000109 (Cache hit = 99.999891%)
Key Writes/Key Write Requests = 0.026460
Connections/second = 2.470 (/hour = 8892.131)
KB received/second = 1.217 (/hour = 4382.282)
KB sent/second = 10.821 (/hour = 38956.540)
Temporary Tables Created/second = 0.200 (/hour = 720.375)
Opened Tables/second = 0.029 (/hour = 104.117)
Slow Queries/second = 0.002 (/hour = 6.566)
% of slow queries = 0.012%
Queries/second = 14.768 (/hour = 53163.314)
MySQL Query Cache hits = 18313/22730(81%)
my.cnf:
[mysqld]
bind-address=127.0.0.1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_type=1
query_cache_limit =2M
query_cache_size=32M
key_buffer=40M
thread_concurrency=2
interactive_timeout=100
table_cache=384
join_buffer_size=512K
read_rnd_buffer_size=512K
read_buffer_size=512K
max_connections=800
max_connect_errors=65535
connect_timeout=30
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
does the settings and stats look good? Any suggestions?
zorafex - what is the spec of the box - processor/ram?
QUOTE
Originally posted by Dave#
zorafex - what is the spec of the box - processor/ram?
zorafex - what is the spec of the box - processor/ram?
Sorry, its a P4 2.4Ghz, 1GB Ram, Hard drive is set to UDMA5
QUOTE
Originally posted by zorafex
Sorry, its a P4 2.4Ghz, 1GB Ram, Hard drive is set to UDMA5
Sorry, its a P4 2.4Ghz, 1GB Ram, Hard drive is set to UDMA5
Your setting look fine - although 800 connections seeems very high
Your not swapping which is good, although you may want to check the output of 'free' to see how much real Ram is being used.
I'd check out the line in top that starts
CPU states:
To see if IOWAIT or the CPU is bound.
Well "top" shows the high load is being caused mostly by mysql. There are usually 2-3 mysql processes that are using 15-25% CPU (only for a few secs.. then it goes away, but only to come back in a minute) the other mysql processes only use like 0.5% CPU.
mtop data:
load average: 0.94, 1.38, 1.39 mysqld 4.0.15-standard up 0 day(s), 13:15 hrs
5 threads: 3 running, 0 cached. Queries/slow: 527.1K/27 Cache Hit: 100.00%
Opened tables: 153 RRN: 160.3M TLW: 1.4K SFJ: 212 SMP: 0 QPS: 11
Is 153 opened tables, bad?
load average: 0.94, 1.38, 1.39 mysqld 4.0.15-standard up 0 day(s), 13:15 hrs
5 threads: 3 running, 0 cached. Queries/slow: 527.1K/27 Cache Hit: 100.00%
Opened tables: 153 RRN: 160.3M TLW: 1.4K SFJ: 212 SMP: 0 QPS: 11
Is 153 opened tables, bad?
Please help me .. My load too much..
And this is since i ugpraded to 3.5.18 ensim pro.
mySQL configurations are same like the aussie's one.
Sun Sep 14 21:39:41 GMT 2003
9:39pm up 2:19, 1 user, load average: 14.61, 13.60, 9.67
494 processes: 493 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 995680K used, 35524K free, 0K shrd, 125624K buff
Swap: 1052248K av, 100664K used, 951584K free 423288K cached
Http processes currently running = 302
Mysql processes currently running = 65
Netstat information summary
2 CLOSE_WAIT
2 FIN_WAIT2
3 SYN_SENT
4 SYN_RECV
8 FIN_WAIT1
12 ESTABLISHED
21 LISTEN
815 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 504 |
| Aborted_connects | 1 |
| Bytes_received | 10190500 |
| Bytes_sent | 2917322932 |
| Com_admin_commands | 770 |
| Com_alter_table | 5 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6981 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 5 |
| Com_create_table | 16 |
| Com_delete | 796 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 643 |
| Com_insert_select | 11 |
| 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 | 10 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 26820 |
| 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 | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 5831 |
| Connections | 5014 |
| Created_tmp_disk_tables | 883 |
| Created_tmp_tables | 2849 |
| Created_tmp_files | 4 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 787 |
| Handler_read_first | 1247 |
| Handler_read_key | 514266 |
| Handler_read_next | 2037074 |
| Handler_read_prev | 2883 |
| Handler_read_rnd | 9371377 |
| Handler_read_rnd_next | 163767205 |
| Handler_rollback | 0 |
| Handler_update | 75408 |
| Handler_write | 1436362 |
| Key_blocks_used | 5194 |
| Key_read_requests | 1400867 |
| Key_reads | 4379 |
| Key_write_requests | 58146 |
| Key_writes | 4626 |
| Max_used_connections | 81 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 | 100% of table_cache in use
| Open_files | 956 |
| Open_streams | 0 |
| Opened_tables | 807 |
| Questions | 72988 |
| Qcache_queries_in_cache | 2069 |
| Qcache_inserts | 26019 |
| Qcache_hits | 27084 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1209 |
| Qcache_free_memory | 28642600 |
| Qcache_free_blocks | 951 |
| Qcache_total_blocks | 5706 |
| Rpl_status | NULL |
| Select_full_join | 174 |
| Select_full_range_join | 24 |
| Select_range | 188 |
| Select_range_check | 0 |
| Select_scan | 14905 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 1 |
| Slow_queries | 17 | (execution time > 10 secs)
| Sort_merge_passes | 2 |
| Sort_range | 2568 |
| Sort_rows | 9385578 |
| Sort_scan | 4876 |
| Table_locks_immediate | 45634 |
| Table_locks_waited | 162 |
| Threads_cached | 39 |
| Threads_created | 82 |
| Threads_connected | 20 |
| Threads_running | 2 |
| Uptime | 2338 | 38 mins 58 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.003126 (Cache hit = 99.996874%)
Key Writes/Key Write Requests = 0.079558
Connections/second = 2.145 (/hour = 7720.445)
KB received/second = 4.256 (/hour = 15322.327)
KB sent/second = 896.985 (/hour = 3229146.108)
Temporary Tables Created/second = 1.219 (/hour = 4386.826)
Opened Tables/second = 0.345 (/hour = 1242.601)
Slow Queries/second = 0.007 (/hour = 26.176)
% of slow queries = 0.023%
Queries/second = 31.218 (/hour = 112385.287)
MySQL Query Cache hits = 27084/54312(50%)
And this is since i ugpraded to 3.5.18 ensim pro.
mySQL configurations are same like the aussie's one.
Sun Sep 14 21:39:41 GMT 2003
9:39pm up 2:19, 1 user, load average: 14.61, 13.60, 9.67
494 processes: 493 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1031204K av, 995680K used, 35524K free, 0K shrd, 125624K buff
Swap: 1052248K av, 100664K used, 951584K free 423288K cached
Http processes currently running = 302
Mysql processes currently running = 65
Netstat information summary
2 CLOSE_WAIT
2 FIN_WAIT2
3 SYN_SENT
4 SYN_RECV
8 FIN_WAIT1
12 ESTABLISHED
21 LISTEN
815 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 504 |
| Aborted_connects | 1 |
| Bytes_received | 10190500 |
| Bytes_sent | 2917322932 |
| Com_admin_commands | 770 |
| Com_alter_table | 5 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6981 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 5 |
| Com_create_table | 16 |
| Com_delete | 796 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 643 |
| Com_insert_select | 11 |
| 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 | 10 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 26820 |
| 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 | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 5831 |
| Connections | 5014 |
| Created_tmp_disk_tables | 883 |
| Created_tmp_tables | 2849 |
| Created_tmp_files | 4 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 787 |
| Handler_read_first | 1247 |
| Handler_read_key | 514266 |
| Handler_read_next | 2037074 |
| Handler_read_prev | 2883 |
| Handler_read_rnd | 9371377 |
| Handler_read_rnd_next | 163767205 |
| Handler_rollback | 0 |
| Handler_update | 75408 |
| Handler_write | 1436362 |
| Key_blocks_used | 5194 |
| Key_read_requests | 1400867 |
| Key_reads | 4379 |
| Key_write_requests | 58146 |
| Key_writes | 4626 |
| Max_used_connections | 81 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 | 100% of table_cache in use
| Open_files | 956 |
| Open_streams | 0 |
| Opened_tables | 807 |
| Questions | 72988 |
| Qcache_queries_in_cache | 2069 |
| Qcache_inserts | 26019 |
| Qcache_hits | 27084 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1209 |
| Qcache_free_memory | 28642600 |
| Qcache_free_blocks | 951 |
| Qcache_total_blocks | 5706 |
| Rpl_status | NULL |
| Select_full_join | 174 |
| Select_full_range_join | 24 |
| Select_range | 188 |
| Select_range_check | 0 |
| Select_scan | 14905 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 1 |
| Slow_queries | 17 | (execution time > 10 secs)
| Sort_merge_passes | 2 |
| Sort_range | 2568 |
| Sort_rows | 9385578 |
| Sort_scan | 4876 |
| Table_locks_immediate | 45634 |
| Table_locks_waited | 162 |
| Threads_cached | 39 |
| Threads_created | 82 |
| Threads_connected | 20 |
| Threads_running | 2 |
| Uptime | 2338 | 38 mins 58 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.003126 (Cache hit = 99.996874%)
Key Writes/Key Write Requests = 0.079558
Connections/second = 2.145 (/hour = 7720.445)
KB received/second = 4.256 (/hour = 15322.327)
KB sent/second = 896.985 (/hour = 3229146.108)
Temporary Tables Created/second = 1.219 (/hour = 4386.826)
Opened Tables/second = 0.345 (/hour = 1242.601)
Slow Queries/second = 0.007 (/hour = 26.176)
% of slow queries = 0.023%
Queries/second = 31.218 (/hour = 112385.287)
MySQL Query Cache hits = 27084/54312(50%)
QUOTE
Originally posted by Dave#
If KeepAlive is on turn it off
If KeepAlive is on turn it off
What is KeepAlive, what does it do, and how would you go about turning it off?
Great howto by the way
QUOTE
Originally posted by GetWired
What is KeepAlive, what does it do, and how would you go about turning it off?
Great howto by the way
What is KeepAlive, what does it do, and how would you go about turning it off?
Great howto by the way
KeepAlive would be an apache configuration setting, to enable http1.1 keepalive requests, and other pipelining, it's popular on lightly loaded servers, as it helps keep them that way, but heavily loaded servers seem to get even more loaded. A good rule of thumb is if your users can loiter around, and stay on your site a long time, changing pages, you tend to use KeepAlive, and if you need them to leave, and reconnect, for the next user, you want it off...
Hi
here is my test results..
Key Reads/Key Read Requests = 0.000208 (Cache hit = 99.999792%)
Key Writes/Key Write Requests = 0.671481
Connections/second = 1.080 (/hour = 3886.928)
KB received/second = 2.430 (/hour = 8746.731)
KB sent/second = 17.291 (/hour = 62248.548)
Temporary Tables Created/second = 0.784 (/hour = 2822.614)
Opened Tables/second = 0.010 (/hour = 35.472)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 17.101 (/hour = 61563.862)
somehow , I did not have "MySQL Query Cache hits = "?
I am sure I did thing righ though
*************
Mon Oct 20 23:20:08 HKT 2003
11:20pm up 12 days, 20:10, 1 user, load average: 0.33, 0.35, 0.34
233 processes: 232 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1028616K av, 1015056K used, 13560K free, 0K shrd, 52916K buff
Swap: 2096472K av, 306988K used, 1789484K free 297252K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
22889 nobody 12 0 1132 1132 820 R 1.9 0.1 0:00 top
Http processes currently running = 160
Mysql processes currently running = 15
Netstat information summary
1 LAST_ACK
17 SYN_RECV
21 FIN_WAIT1
25 LISTEN
127 ESTABLISHED
180 FIN_WAIT2
224 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 182 |
| Aborted_connects | 157 |
| Bytes_received | 56813251 |
| Bytes_sent | 404323155 |
| Com_admin_commands | 5 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 20 |
| Com_change_db | 24814 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 20 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 6519 |
| 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 | 3794 |
| Com_insert_select | 350 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 344 |
| 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 | 112959 |
| Com_set_option | 34 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 34 |
| Com_show_databases | 0 |
| Com_show_fields | 34 |
| 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 | 81 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 327 |
| Com_show_variables | 25 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 344 |
| Com_update | 29659 |
| Connections | 24655 |
| Created_tmp_disk_tables | 494 |
| Created_tmp_tables | 17904 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3207 |
| Handler_read_first | 7129 |
| Handler_read_key | 5532024 |
| Handler_read_next | 9635036 |
| Handler_read_prev | 55587 |
| Handler_read_rnd | 810038 |
| Handler_read_rnd_next | 13912222 |
| Handler_rollback | 0 |
| Handler_update | 124268 |
| Handler_write | 342205 |
| Key_blocks_used | 3770 |
| Key_read_requests | 15505938 |
| Key_reads | 3220 |
| Key_write_requests | 48332 |
| Key_writes | 32454 |
| Max_used_connections | 8 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 219 | 21% of table_cache in use
| Open_files | 423 |
| Open_streams | 0 |
| Opened_tables | 225 |
| Questions | 390503 |
| Qcache_queries_in_cache | 2959 |
| Qcache_inserts | 110914 |
| Qcache_hits | 186093 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1886 |
| Qcache_free_memory | 29749664 |
| Qcache_free_blocks | 1409 |
| Qcache_total_blocks | 7505 |
| Rpl_status | NULL |
| Select_full_join | 110 |
| Select_full_range_join | 88 |
| Select_range | 1914 |
| Select_range_check | 0 |
| Select_scan | 28855 |
| 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 | 20018 |
| Sort_rows | 1107675 |
| Sort_scan | 21142 |
| Table_locks_immediate | 289406 |
| Table_locks_waited | 11 |
| Threads_cached | 6 |
| Threads_created | 9 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 22835 | 6 hrs 20 mins 35 secs
+--------------------------+-----------+
*********
here is my test results..
Key Reads/Key Read Requests = 0.000208 (Cache hit = 99.999792%)
Key Writes/Key Write Requests = 0.671481
Connections/second = 1.080 (/hour = 3886.928)
KB received/second = 2.430 (/hour = 8746.731)
KB sent/second = 17.291 (/hour = 62248.548)
Temporary Tables Created/second = 0.784 (/hour = 2822.614)
Opened Tables/second = 0.010 (/hour = 35.472)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 17.101 (/hour = 61563.862)
somehow , I did not have "MySQL Query Cache hits = "?
I am sure I did thing righ though
*************
Mon Oct 20 23:20:08 HKT 2003
11:20pm up 12 days, 20:10, 1 user, load average: 0.33, 0.35, 0.34
233 processes: 232 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1028616K av, 1015056K used, 13560K free, 0K shrd, 52916K buff
Swap: 2096472K av, 306988K used, 1789484K free 297252K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
22889 nobody 12 0 1132 1132 820 R 1.9 0.1 0:00 top
Http processes currently running = 160
Mysql processes currently running = 15
Netstat information summary
1 LAST_ACK
17 SYN_RECV
21 FIN_WAIT1
25 LISTEN
127 ESTABLISHED
180 FIN_WAIT2
224 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 182 |
| Aborted_connects | 157 |
| Bytes_received | 56813251 |
| Bytes_sent | 404323155 |
| Com_admin_commands | 5 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 20 |
| Com_change_db | 24814 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 20 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 6519 |
| 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 | 3794 |
| Com_insert_select | 350 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 344 |
| 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 | 112959 |
| Com_set_option | 34 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 34 |
| Com_show_databases | 0 |
| Com_show_fields | 34 |
| 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 | 81 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 9 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 327 |
| Com_show_variables | 25 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 344 |
| Com_update | 29659 |
| Connections | 24655 |
| Created_tmp_disk_tables | 494 |
| Created_tmp_tables | 17904 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3207 |
| Handler_read_first | 7129 |
| Handler_read_key | 5532024 |
| Handler_read_next | 9635036 |
| Handler_read_prev | 55587 |
| Handler_read_rnd | 810038 |
| Handler_read_rnd_next | 13912222 |
| Handler_rollback | 0 |
| Handler_update | 124268 |
| Handler_write | 342205 |
| Key_blocks_used | 3770 |
| Key_read_requests | 15505938 |
| Key_reads | 3220 |
| Key_write_requests | 48332 |
| Key_writes | 32454 |
| Max_used_connections | 8 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 219 | 21% of table_cache in use
| Open_files | 423 |
| Open_streams | 0 |
| Opened_tables | 225 |
| Questions | 390503 |
| Qcache_queries_in_cache | 2959 |
| Qcache_inserts | 110914 |
| Qcache_hits | 186093 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1886 |
| Qcache_free_memory | 29749664 |
| Qcache_free_blocks | 1409 |
| Qcache_total_blocks | 7505 |
| Rpl_status | NULL |
| Select_full_join | 110 |
| Select_full_range_join | 88 |
| Select_range | 1914 |
| Select_range_check | 0 |
| Select_scan | 28855 |
| 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 | 20018 |
| Sort_rows | 1107675 |
| Sort_scan | 21142 |
| Table_locks_immediate | 289406 |
| Table_locks_waited | 11 |
| Threads_cached | 6 |
| Threads_created | 9 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 22835 | 6 hrs 20 mins 35 secs
+--------------------------+-----------+
*********
Made changes and restarted mysql.
Waiting for mysql to restart.... . . . . . . . . . . finished.
mysql status
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)").
Restored backup and restarted.
I am now making changes in groups to see where I can isolate the problem child line.
Waiting for mysql to restart.... . . . . . . . . . . finished.
mysql status
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)").
Restored backup and restarted.
I am now making changes in groups to see where I can isolate the problem child line.
QUOTE
Originally posted by aussie
Continued ...
Add it under [mysqld] as indicated above.
One other change which should be done to your my.cnf file is the removal of [b]set-variable.
Find all occurances of set-variable and remove it from my.cnf. Its not needed!
Here is my copy of my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2 <--- Try 4 for dual pentiums
myisam_sort_buffer_size=64M
log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid <-- Not necessary
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[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
This may be good starting point for you if you dont have your my.cnf setup. You can try mine out on your server or modify it to suit your own server and memory requirements.
Once you have made all of the above changes its time to save the file and restart MySQL. I recommend that you view your mysqld.log file after restarting MySQL to make sure there are no errors being logged.
I highly recommend that everyone view this link for more information upgrading from version 3.23 to 4.0.
http://www.mysql.com/doc/en/Upgrading-from-3.23.html
I have certainly not covered everthing and there may be other information here that may benefit you. Depending on the type of database you host there are additional commands to run for innodb type db's and other optimization methods to consider in preparation for the release of MySQL 4.1 and 5.0.
Happy SQL'ing!
[/B]
Continued ...
Add it under [mysqld] as indicated above.
One other change which should be done to your my.cnf file is the removal of [b]set-variable.
Find all occurances of set-variable and remove it from my.cnf. Its not needed!
Here is my copy of my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2 <--- Try 4 for dual pentiums
myisam_sort_buffer_size=64M
log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid <-- Not necessary
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[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
This may be good starting point for you if you dont have your my.cnf setup. You can try mine out on your server or modify it to suit your own server and memory requirements.
Once you have made all of the above changes its time to save the file and restart MySQL. I recommend that you view your mysqld.log file after restarting MySQL to make sure there are no errors being logged.
I highly recommend that everyone view this link for more information upgrading from version 3.23 to 4.0.
http://www.mysql.com/doc/en/Upgrading-from-3.23.html
I have certainly not covered everthing and there may be other information here that may benefit you. Depending on the type of database you host there are additional commands to run for innodb type db's and other optimization methods to consider in preparation for the release of MySQL 4.1 and 5.0.
Happy SQL'ing!
[/B]
It's either in [isamchk] or [myisamchk].
What is the significance of these two?
Oddly enough after adding those it is working now.
Thanks.
What is the significance of these two?
Oddly enough after adding those it is working now.
Thanks.
QUOTE
Originally posted by solokron
Made changes and restarted mysql.
Waiting for mysql to restart.... . . . . . . . . . . finished.
mysql status
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)").
Restored backup and restarted.
I am now making changes in groups to see where I can isolate the problem child line.
Made changes and restarted mysql.
Waiting for mysql to restart.... . . . . . . . . . . finished.
mysql status
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)").
Restored backup and restarted.
I am now making changes in groups to see where I can isolate the problem child line.
Is open tables supposed to constantly rise until it hits 100%? :confused:
If not, how can I stop it from doing what it is doing...
If not, how can I stop it from doing what it is doing...
hi aussie i tried your script and my mysql died off with this error think can help ??
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"). Nov 26 17:23:14 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:23:14 freedom rc: Stopping mysql: succeeded Nov 26 17:34:31 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:34:31 freedom rc: Stopping mysql: succeeded
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"). Nov 26 17:23:14 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:23:14 freedom rc: Stopping mysql: succeeded Nov 26 17:34:31 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:34:31 freedom rc: Stopping mysql: succeeded
Match your .pid with your hostname .pid file in /var/lib/mysql/
For the sock error you might want to try...
rm -f /tmp/mysql.sock
rm -f /var/tmp/mysql.sock
/etc/init.d/mysql stop
rm -f /var/lib/mysql/mysql.sock
/etc/init.d/mysql start
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
ln -s /var/lib/mysql/mysql.sock /var/tmp/mysql.sock
For the sock error you might want to try...
rm -f /tmp/mysql.sock
rm -f /var/tmp/mysql.sock
/etc/init.d/mysql stop
rm -f /var/lib/mysql/mysql.sock
/etc/init.d/mysql start
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
ln -s /var/lib/mysql/mysql.sock /var/tmp/mysql.sock
QUOTE
Originally posted by wisher
hi aussie i tried your script and my mysql died off with this error think can help ??
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"). Nov 26 17:23:14 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:23:14 freedom rc: Stopping mysql: succeeded Nov 26 17:34:31 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:34:31 freedom rc: Stopping mysql: succeeded
hi aussie i tried your script and my mysql died off with this error think can help ??
mysql has failed, please contact the sysadmin (result was "/usr/bin/mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)"). Nov 26 17:23:14 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:23:14 freedom rc: Stopping mysql: succeeded Nov 26 17:34:31 freedom mysql: No mysqld pid file found. Looked for /var/lib/mysql/freedom.paradoxhost.com.pid. Nov 26 17:34:31 freedom rc: Stopping mysql: succeeded
QUOTE
Originally posted by solokron
Match your .pid with your hostname .pid file in /var/lib/mysql/
For the sock error you might want to try...
rm -f /tmp/mysql.sock "worked"
rm -f /var/tmp/mysql.sock "worked"
/etc/init.d/mysql stop "worked"
rm -f /var/lib/mysql/mysql.sock "worked"
/etc/init.d/mysql start
root@freedom [/etc]# /etc/init.d/mysql start
/usr/bin/my_print_defaults: unrecognized option `--loose-verbose'
root@freedom [/etc]# Starting mysqld daemon with databases from /var/lib/mysql
031126 19:57:22 mysqld ended
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock "worked"
ln -s /var/lib/mysql/mysql.sock /var/tmp/mysql.sock "worked"
Match your .pid with your hostname .pid file in /var/lib/mysql/
For the sock error you might want to try...
rm -f /tmp/mysql.sock "worked"
rm -f /var/tmp/mysql.sock "worked"
/etc/init.d/mysql stop "worked"
rm -f /var/lib/mysql/mysql.sock "worked"
/etc/init.d/mysql start
root@freedom [/etc]# /etc/init.d/mysql start
/usr/bin/my_print_defaults: unrecognized option `--loose-verbose'
root@freedom [/etc]# Starting mysqld daemon with databases from /var/lib/mysql
031126 19:57:22 mysqld ended
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock "worked"
ln -s /var/lib/mysql/mysql.sock /var/tmp/mysql.sock "worked"
Looks like my cpanel is busted or wat ?
I some how manage to get mysql to run by
service mysqld restart
but i have an issue here in the instance of updating i seem to have busted my cpanel by installing Mysql RPM from the Cpanel installation and now my Cpanel uses ver 4 MySql and my linux uses the version 3
any one out there ?? SOS
Give this a shot.
Make sure you have updated your httpd.conf with the proper .pid or simply remove that section completely.
Keep cpanel to 4 then run a cpanel upgrade.
Make sure you have updated your httpd.conf with the proper .pid or simply remove that section completely.
Keep cpanel to 4 then run a cpanel upgrade.
QUOTE
Originally posted by wisher
Looks like my cpanel is busted or wat ?
I some how manage to get mysql to run by
service mysqld restart
but i have an issue here in the instance of updating i seem to have busted my cpanel by installing Mysql RPM from the Cpanel installation and now my Cpanel uses ver 4 MySql and my linux uses the version 3
any one out there ?? SOS
Looks like my cpanel is busted or wat ?
I some how manage to get mysql to run by
service mysqld restart
but i have an issue here in the instance of updating i seem to have busted my cpanel by installing Mysql RPM from the Cpanel installation and now my Cpanel uses ver 4 MySql and my linux uses the version 3
any one out there ?? SOS
it's still not working i wonders if theres a way to use rpm install for that copy of mysql for Cpanel
Hello All,
I am having the mysql issue on My server which is causing a Mysql intersive site to load Slower (if i am not worng)
# Here is the output of Daves Script
2:52pm up 3:02, 1 user, load average: 2.31, 1.85, 1.54
217 processes: 212 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 60.0% user, 9.0% system, 0.0% nice, 30.0% idle
CPU1 states: 50.0% user, 16.0% system, 0.0% nice, 33.0% idle
Mem: 1030672K av, 565680K used, 464992K free, 0K shrd, 91060K buff
Swap: 1052216K av, 12312K used, 1039904K free 250604K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1517 nobody 11 0 4324 4324 2632 R 1.9 0.4 0:06 httpd
15948 mysql 16 0 19664 19M 2148 R 0.9 1.9 0:00 mysqld
27738 nobody 9 0 4384 4384 2824 R 0.0 0.4 0:01 httpd
31018 nobody 9 0 4384 4384 2820 R 0.0 0.4 0:01 httpd
Http processes currently running = 138
Mysql processes currently running = 21
Netstat information summary
2 FIN_WAIT2
8 SYN_RECV
29 LISTEN
126 ESTABLISHED
189 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 18 |
| Bytes_received | 48824104 |
| Bytes_sent | 223883857 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 104499 |
| 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 | 4466 |
| 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 | 2123 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 71 |
| Com_optimize | 484 |
| 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 | 268919 |
| 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 | 37 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 25 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 71 |
| Com_update | 303268 |
| Connections | 69748 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 287 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 61 |
| Handler_read_first | 489 |
| Handler_read_key | 6206420 |
| Handler_read_next | 587692 |
| Handler_read_prev | 33911 |
| Handler_read_rnd | 122498 |
| Handler_read_rnd_next | 226204452 |
| Handler_rollback | 0 |
| Handler_update | 311451 |
| Handler_write | 3640 |
| Key_blocks_used | 5169 |
| Key_read_requests | 12478434 |
| Key_reads | 5150 |
| Key_write_requests | 2564 |
| Key_writes | 2329 |
| Max_used_connections | 36 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 89 |
| Open_streams | 0 |
| Opened_tables | 701 |
| Questions | 753690 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 273 |
| Select_full_range_join | 0 |
| Select_range | 2 |
| Select_range_check | 0 |
| Select_scan | 268557 |
| 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 | 10 |
| Sort_rows | 122460 |
| Sort_scan | 28266 |
| Table_locks_immediate | 542252 |
| Table_locks_waited | 65798 |
| Threads_cached | 0 |
| Threads_created | 69747 |
| Threads_connected | 8 |
| Threads_running | 3 |
| Uptime | 10946 | 3 hrs 2 mins 26 secs
+--------------------------+-----------+
Key Reads/Key Read Requests = 0.000413 (Cache hit = 99.999587%)
Key Writes/Key Write Requests = 0.908346
Connections/second = 6.372 (/hour = 22939.229)
KB received/second = 4.356 (/hour = 15681.016)
KB sent/second = 19.974 (/hour = 71906.596)
Temporary Tables Created/second = 0.026 (/hour = 94.391)
Opened Tables/second = 0.064 (/hour = 230.550)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 68.855 (/hour = 247879.043)
my.cnf
=====
[mysqld]
#max_connections=500
set-variable = max_connections=500
CPU/MYSQL USAGE
================
mysql 12.09 21.72 0.0
Top Process %CPU 73.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Top Process %CPU 66.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Top Process %CPU 54.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Server Config
===========
Dual PIII
1 GB RAM
Any comment on how do i improve the mysql performance will be appreciated.
Thank you,
I am having the mysql issue on My server which is causing a Mysql intersive site to load Slower (if i am not worng)
# Here is the output of Daves Script
2:52pm up 3:02, 1 user, load average: 2.31, 1.85, 1.54
217 processes: 212 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 60.0% user, 9.0% system, 0.0% nice, 30.0% idle
CPU1 states: 50.0% user, 16.0% system, 0.0% nice, 33.0% idle
Mem: 1030672K av, 565680K used, 464992K free, 0K shrd, 91060K buff
Swap: 1052216K av, 12312K used, 1039904K free 250604K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1517 nobody 11 0 4324 4324 2632 R 1.9 0.4 0:06 httpd
15948 mysql 16 0 19664 19M 2148 R 0.9 1.9 0:00 mysqld
27738 nobody 9 0 4384 4384 2824 R 0.0 0.4 0:01 httpd
31018 nobody 9 0 4384 4384 2820 R 0.0 0.4 0:01 httpd
Http processes currently running = 138
Mysql processes currently running = 21
Netstat information summary
2 FIN_WAIT2
8 SYN_RECV
29 LISTEN
126 ESTABLISHED
189 TIME_WAIT
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 18 |
| Bytes_received | 48824104 |
| Bytes_sent | 223883857 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 104499 |
| 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 | 4466 |
| 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 | 2123 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 71 |
| Com_optimize | 484 |
| 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 | 268919 |
| 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 | 37 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 25 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 71 |
| Com_update | 303268 |
| Connections | 69748 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 287 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 61 |
| Handler_read_first | 489 |
| Handler_read_key | 6206420 |
| Handler_read_next | 587692 |
| Handler_read_prev | 33911 |
| Handler_read_rnd | 122498 |
| Handler_read_rnd_next | 226204452 |
| Handler_rollback | 0 |
| Handler_update | 311451 |
| Handler_write | 3640 |
| Key_blocks_used | 5169 |
| Key_read_requests | 12478434 |
| Key_reads | 5150 |
| Key_write_requests | 2564 |
| Key_writes | 2329 |
| Max_used_connections | 36 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 | 100% of table_cache in use
| Open_files | 89 |
| Open_streams | 0 |
| Opened_tables | 701 |
| Questions | 753690 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 273 |
| Select_full_range_join | 0 |
| Select_range | 2 |
| Select_range_check | 0 |
| Select_scan | 268557 |
| 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 | 10 |
| Sort_rows | 122460 |
| Sort_scan | 28266 |
| Table_locks_immediate | 542252 |
| Table_locks_waited | 65798 |
| Threads_cached | 0 |
| Threads_created | 69747 |
| Threads_connected | 8 |
| Threads_running | 3 |
| Uptime | 10946 | 3 hrs 2 mins 26 secs
+--------------------------+-----------+
Key Reads/Key Read Requests = 0.000413 (Cache hit = 99.999587%)
Key Writes/Key Write Requests = 0.908346
Connections/second = 6.372 (/hour = 22939.229)
KB received/second = 4.356 (/hour = 15681.016)
KB sent/second = 19.974 (/hour = 71906.596)
Temporary Tables Created/second = 0.026 (/hour = 94.391)
Opened Tables/second = 0.064 (/hour = 230.550)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 68.855 (/hour = 247879.043)
my.cnf
=====
[mysqld]
#max_connections=500
set-variable = max_connections=500
CPU/MYSQL USAGE
================
mysql 12.09 21.72 0.0
Top Process %CPU 73.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Top Process %CPU 66.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Top Process %CPU 54.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.combuzz.com.pid --skip-locking
Server Config
===========
Dual PIII
1 GB RAM
Any comment on how do i improve the mysql performance will be appreciated.
Thank you,
Did you read the thread? Why dont you configure your my.cnf based on the information already posted? Your my.cnf is a virgin config and should be updated accordingly. There is alot of good information here. Configure your my.cnf based on the guidelines posted.
Thanks for this thread, it's loaded with good information. I am kind of new to mysql optimization. I previously had optimized our old server for postgresql, so I'm trying to catch on.
My question is I can't seem to find a mysqld.log file anywhere. Any tips?
Thanks again for the great information!
My question is I can't seem to find a mysqld.log file anywhere. Any tips?
Thanks again for the great information!
I have the following :
------------------
P4 2.4
1GB Ram
Redhat 9
120GB HDD
And this is my my.cnf
I ran that script that was in this thread. And this is what I got.
I'm not sure how to interpret these results. Are they alright? or is it possible to optimize them further ?
NAPPA
------------------
P4 2.4
1GB Ram
Redhat 9
120GB HDD
And this is my my.cnf
CODE
[mysqld]
thread_cache_size=80
key_buffer=150M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[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
thread_cache_size=80
key_buffer=150M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[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 ran that script that was in this thread. And this is what I got.
CODE
Thu Dec 11 12:58:06 PST 2003
12:58:07 up 3 days, 11:03, 1 user, load average: 0.47, 0.30, 0.27
95 processes: 94 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1029884k av, 1003500k used, 26384k free, 0k shrd, 281112k buff
768764k actv, 8k in_d, 20904k in_c
Swap: 2097136k av, 98480k used, 1998656k free 564708k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1430 nobody 20 0 1180 1180 852 R 0.0 0.1 0:00 0 top
Http processes currently running = 19
Mysql processes currently running = 4
Netstat information summary
2 FIN_WAIT2
3 ESTABLISHED
7 SYN_RECV
8 FIN_WAIT1
33 LISTEN
606 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 152 |
| Aborted_connects | 115 |
| Bytes_received | 97860164 |
| Bytes_sent | 1969132458 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 617832 |
| 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 | 33013 |
| 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 | 32740 |
| Com_insert_select | 48 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 271 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 173804 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1 |
| Com_show_databases | 0 |
| Com_show_fields | 4 |
| 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 | 110 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 5 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 271 |
| Com_update | 81014 |
| Connections | 65192 |
| Created_tmp_disk_tables | 1146 |
| Created_tmp_tables | 1852 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 32186 |
| Handler_read_first | 814 |
| Handler_read_key | 861827 |
| Handler_read_next | 1468700 |
| Handler_read_prev | 9 |
| Handler_read_rnd | 640873 |
| Handler_read_rnd_next | 564366337 |
| Handler_rollback | 0 |
| Handler_update | 124135 |
| Handler_write | 342069 |
| Key_blocks_used | 12797 |
| Key_read_requests | 3412430 |
| Key_reads | 9981 |
| Key_write_requests | 331552 |
| Key_writes | 109461 |
| Max_used_connections | 10 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 234 | 23% of table_cache in use
| Open_files | 451 |
| Open_streams | 0 |
| Opened_tables | 240 |
| Questions | 1601084 |
| Qcache_queries_in_cache | 2099 |
| Qcache_inserts | 173344 |
| Qcache_hits | 596179 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 349 |
| Qcache_free_memory | 27999456 |
| Qcache_free_blocks | 761 |
| Qcache_total_blocks | 5120 |
| Rpl_status | NULL |
| Select_full_join | 3 |
| Select_full_range_join | 523 |
| Select_range | 1654 |
| Select_range_check | 0 |
| Select_scan | 91460 |
| 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 | 7149 |
| Sort_rows | 690129 |
| Sort_scan | 14776 |
| Table_locks_immediate | 357884 |
| Table_locks_waited | 76 |
| Threads_cached | 10 |
| Threads_created | 11 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 33129 | 9 hrs 12 mins 9 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.002925 (Cache hit = 99.997075%)
Key Writes/Key Write Requests = 0.330147
Connections/second = 1.968 (/hour = 7084.162)
KB received/second = 2.885 (/hour = 10384.787)
KB sent/second = 58.045 (/hour = 208962.782)
Temporary Tables Created/second = 0.056 (/hour = 201.250)
Opened Tables/second = 0.007 (/hour = 26.080)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 48.329 (/hour = 173983.591)
MySQL Query Cache hits = 596179/769872(77%)
12:58:07 up 3 days, 11:03, 1 user, load average: 0.47, 0.30, 0.27
95 processes: 94 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1029884k av, 1003500k used, 26384k free, 0k shrd, 281112k buff
768764k actv, 8k in_d, 20904k in_c
Swap: 2097136k av, 98480k used, 1998656k free 564708k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1430 nobody 20 0 1180 1180 852 R 0.0 0.1 0:00 0 top
Http processes currently running = 19
Mysql processes currently running = 4
Netstat information summary
2 FIN_WAIT2
3 ESTABLISHED
7 SYN_RECV
8 FIN_WAIT1
33 LISTEN
606 TIME_WAIT
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 152 |
| Aborted_connects | 115 |
| Bytes_received | 97860164 |
| Bytes_sent | 1969132458 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 617832 |
| 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 | 33013 |
| 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 | 32740 |
| Com_insert_select | 48 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 271 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 173804 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1 |
| Com_show_databases | 0 |
| Com_show_fields | 4 |
| 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 | 110 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 5 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 271 |
| Com_update | 81014 |
| Connections | 65192 |
| Created_tmp_disk_tables | 1146 |
| Created_tmp_tables | 1852 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 32186 |
| Handler_read_first | 814 |
| Handler_read_key | 861827 |
| Handler_read_next | 1468700 |
| Handler_read_prev | 9 |
| Handler_read_rnd | 640873 |
| Handler_read_rnd_next | 564366337 |
| Handler_rollback | 0 |
| Handler_update | 124135 |
| Handler_write | 342069 |
| Key_blocks_used | 12797 |
| Key_read_requests | 3412430 |
| Key_reads | 9981 |
| Key_write_requests | 331552 |
| Key_writes | 109461 |
| Max_used_connections | 10 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 234 | 23% of table_cache in use
| Open_files | 451 |
| Open_streams | 0 |
| Opened_tables | 240 |
| Questions | 1601084 |
| Qcache_queries_in_cache | 2099 |
| Qcache_inserts | 173344 |
| Qcache_hits | 596179 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 349 |
| Qcache_free_memory | 27999456 |
| Qcache_free_blocks | 761 |
| Qcache_total_blocks | 5120 |
| Rpl_status | NULL |
| Select_full_join | 3 |
| Select_full_range_join | 523 |
| Select_range | 1654 |
| Select_range_check | 0 |
| Select_scan | 91460 |
| 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 | 7149 |
| Sort_rows | 690129 |
| Sort_scan | 14776 |
| Table_locks_immediate | 357884 |
| Table_locks_waited | 76 |
| Threads_cached | 10 |
| Threads_created | 11 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 33129 | 9 hrs 12 mins 9 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.002925 (Cache hit = 99.997075%)
Key Writes/Key Write Requests = 0.330147
Connections/second = 1.968 (/hour = 7084.162)
KB received/second = 2.885 (/hour = 10384.787)
KB sent/second = 58.045 (/hour = 208962.782)
Temporary Tables Created/second = 0.056 (/hour = 201.250)
Opened Tables/second = 0.007 (/hour = 26.080)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 48.329 (/hour = 173983.591)
MySQL Query Cache hits = 596179/769872(77%)
I'm not sure how to interpret these results. Are they alright? or is it possible to optimize them further ?
NAPPA
Awesome thread, thanks
I have been playing around with some different settings in my.conf file. I have a Dual 2.4 Ghz Xeon with 1 GB ram.
My current conf file is:
[mysqld]
max_connections = 500
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
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
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 2M
sort_buffer = 2M
read_buffer_size = 1M
write_buffer = 2M
The CPU/Memory/MySQL resport is this:
User Domain %CPU %MEM Mysql Processes
mysql 12.67 22.56 0.0
Top Process %CPU 6.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Should I still be seeing this as the top process? If not what else can I try to tweak??
I have been playing around with some different settings in my.conf file. I have a Dual 2.4 Ghz Xeon with 1 GB ram.
My current conf file is:
[mysqld]
max_connections = 500
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
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
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 2M
sort_buffer = 2M
read_buffer_size = 1M
write_buffer = 2M
The CPU/Memory/MySQL resport is this:
User Domain %CPU %MEM Mysql Processes
mysql 12.67 22.56 0.0
Top Process %CPU 6.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Should I still be seeing this as the top process? If not what else can I try to tweak??
QUOTE
Originally posted by eaglewolf
Awesome thread, thanks
I have been playing around with some different settings in my.conf file. I have a Dual 2.4 Ghz Xeon with 1 GB ram.
My current conf file is:
[mysqld]
max_connections = 500
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
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
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 2M
sort_buffer = 2M
read_buffer_size = 1M
write_buffer = 2M
The CPU/Memory/MySQL resport is this:
User Domain %CPU %MEM Mysql Processes
mysql 12.67 22.56 0.0
Top Process %CPU 6.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Should I still be seeing this as the top process? If not what else can I try to tweak??
Awesome thread, thanks
I have been playing around with some different settings in my.conf file. I have a Dual 2.4 Ghz Xeon with 1 GB ram.
My current conf file is:
[mysqld]
max_connections = 500
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
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
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 2M
sort_buffer = 2M
read_buffer_size = 1M
write_buffer = 2M
The CPU/Memory/MySQL resport is this:
User Domain %CPU %MEM Mysql Processes
mysql 12.67 22.56 0.0
Top Process %CPU 6.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Top Process %CPU 5.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/myserver.pid --skip-locking
Should I still be seeing this as the top process? If not what else can I try to tweak??
Are you turning for performance or for least system impact?
If you ARE turning for performance, on a dual system, mysql should hog a whole cpu to itself... With the other going to apache(under 1.3, under apache 2.0 different rules apply).
If you really want to do tuning though, you'll need to know more about web access patterns etc... I did that recently for a client, moved all his forum customers to a single machine, turned off keepalives in apache and turned the mysql for larger results, and more connections. It just wouldn't have been possible on the same machine as the banner rotator(whose session characteristics are opposite, despite the fact that both are written in php).
I am actually trying to first tune for system impact, then for performance - if there is such a thing.
seperating the servers like you did is not really an option for me at this time, so i need to try and tweak it this way.
seperating the servers like you did is not really an option for me at this time, so i need to try and tweak it this way.
QUOTE
Originally posted by eaglewolf
I am actually trying to first tune for system impact, then for performance - if there is such a thing.
seperating the servers like you did is not really an option for me at this time, so i need to try and tweak it this way.
I am actually trying to first tune for system impact, then for performance - if there is such a thing.
seperating the servers like you did is not really an option for me at this time, so i need to try and tweak it this way.
You mean you're trying to tune for least system impact and least negative impact on performance. I usually tune for the exact opposite (most improvement on performance with least negative impact on system load) Good luck! It's a hard balance to strike sometimes...
Yes least system impact and then to improve performance. Yes it is a balancing act.
there any different in my.cnf about dual xeon 2400 with 2 GB RAM and 2*73 SCSI .
thank you.
thank you.
i got a serious problem here.
my phpbb forum suddenly just slowed down to a nearly complete stop.
here's the dave script's readings:
[PHP]Sat Jan 3 14:51:03 SGT 2004
2:51pm up 1 day, 11:23, 1 user, load average: 4.44, 7.72, 9.04
121 processes: 118 sleeping, 3 running, 0 zombie, 0 stopped
Mem: 1020344K av, 900884K used, 119460K free, 18140K shrd, 11780K buff
Swap: 2096472K av, 25684K used, 2070788K free 626180K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
12188 mysql 17 10 25688 25M 2188 R N 34.2 2.5 0:13 mysqld
12350 mysql 17 10 25688 25M 2188 R N 33.3 2.5 0:06 mysqld
Http processes currently running = 55
Mysql processes currently running = 21
Netstat information summary
4 FIN_WAIT2
12 ESTABLISHED
15 LISTEN
145 TIME_WAIT
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Bytes_received | 881192 |
| Bytes_sent | 13648537 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 272 |
| 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 | 106 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 54 |
| Com_insert_select | 3 |
| Com_kill | 0 |
| Com_load | 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_select | 5170 |
| Com_set_option | 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_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 11 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 11 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 590 |
| Connections | 253 |
| Created_tmp_disk_tables | 15 |
| Created_tmp_tables | 732 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 57 |
| Handler_read_first | 138 |
| Handler_read_key | 1646104 |
| Handler_read_next | 901072 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 237501 |
| Handler_read_rnd_next | 76486395 |
| Handler_update | 46086 |
| Handler_write | 785275 |
| Key_blocks_used | 8322 |
| Key_read_requests | 4130086 |
| Key_reads | 8196 |
| Key_write_requests | 14796 |
| Key_writes | 396 |
| Max_used_connections | 13 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 119 | 12% of table_cache in use
| Open_files | 188 |
| Open_streams | 0 |
| Opened_tables | 125 |
| Questions | 6472 |
| Select_full_join | 0 |
| Select_full_range_join | 5 |
| Select_range | 78 |
| Select_range_check | 0 |
| Select_scan | 3218 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 1 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 281 |
| Sort_rows | 357113 |
| Sort_scan | 1314 |
| Table_locks_immediate | 8096 |
| Table_locks_waited | 219 |
| Threads_cached | 5 |
| Threads_created | 14 |
| Threads_connected | 9 |
| Threads_running | 9 |
| Uptime | 243 | 4 mins 3 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.001984 (Cache hit = 99.998016%)
Key Writes/Key Write Requests = 0.026764
Connections/second = 1.041 (/hour = 3748.148)
KB received/second = 3.539 (/hour = 12740.741)
KB sent/second = 54.848 (/hour = 197451.852)
Temporary Tables Created/second = 3.012 (/hour = 10844.444)
Opened Tables/second = 0.514 (/hour = 1851.852)
Slow Queries/second = 0.004 (/hour = 14.815)
% of slow queries = 0.015%
Queries/second = 26.634 (/hour = 95881.481)
[/PHP]
here's my my.cnf:
[PHP][mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = interactive_timeout=75
set-variable = wait_timeout=75
set-variable = max_connect_errors=1000
set-variable = key_buffer=128M
set-variable = myisam_sort_buffer_size=16M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=8M
set-variable = table_cache=1024
set-variable = thread_cache_size=16
set-variable = max_allowed_packet=16M
set-variable = thread_concurrency=2
skip-locking
skip-innodb
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid[/PHP]
and in my phpbb forum's page generation readings:
[php]Page generation time: 34.5895s (PHP: 20% - SQL: 80%) - SQL queries: 24 - GZIP disabled - Debug on[/php]
any help?
my phpbb forum suddenly just slowed down to a nearly complete stop.
here's the dave script's readings:
[PHP]Sat Jan 3 14:51:03 SGT 2004
2:51pm up 1 day, 11:23, 1 user, load average: 4.44, 7.72, 9.04
121 processes: 118 sleeping, 3 running, 0 zombie, 0 stopped
Mem: 1020344K av, 900884K used, 119460K free, 18140K shrd, 11780K buff
Swap: 2096472K av, 25684K used, 2070788K free 626180K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
12188 mysql 17 10 25688 25M 2188 R N 34.2 2.5 0:13 mysqld
12350 mysql 17 10 25688 25M 2188 R N 33.3 2.5 0:06 mysqld
Http processes currently running = 55
Mysql processes currently running = 21
Netstat information summary
4 FIN_WAIT2
12 ESTABLISHED
15 LISTEN
145 TIME_WAIT
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Bytes_received | 881192 |
| Bytes_sent | 13648537 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 272 |
| 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 | 106 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 54 |
| Com_insert_select | 3 |
| Com_kill | 0 |
| Com_load | 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_select | 5170 |
| Com_set_option | 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_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 11 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 11 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 590 |
| Connections | 253 |
| Created_tmp_disk_tables | 15 |
| Created_tmp_tables | 732 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 57 |
| Handler_read_first | 138 |
| Handler_read_key | 1646104 |
| Handler_read_next | 901072 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 237501 |
| Handler_read_rnd_next | 76486395 |
| Handler_update | 46086 |
| Handler_write | 785275 |
| Key_blocks_used | 8322 |
| Key_read_requests | 4130086 |
| Key_reads | 8196 |
| Key_write_requests | 14796 |
| Key_writes | 396 |
| Max_used_connections | 13 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 119 | 12% of table_cache in use
| Open_files | 188 |
| Open_streams | 0 |
| Opened_tables | 125 |
| Questions | 6472 |
| Select_full_join | 0 |
| Select_full_range_join | 5 |
| Select_range | 78 |
| Select_range_check | 0 |
| Select_scan | 3218 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 1 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 281 |
| Sort_rows | 357113 |
| Sort_scan | 1314 |
| Table_locks_immediate | 8096 |
| Table_locks_waited | 219 |
| Threads_cached | 5 |
| Threads_created | 14 |
| Threads_connected | 9 |
| Threads_running | 9 |
| Uptime | 243 | 4 mins 3 secs
+--------------------------+----------+
Key Reads/Key Read Requests = 0.001984 (Cache hit = 99.998016%)
Key Writes/Key Write Requests = 0.026764
Connections/second = 1.041 (/hour = 3748.148)
KB received/second = 3.539 (/hour = 12740.741)
KB sent/second = 54.848 (/hour = 197451.852)
Temporary Tables Created/second = 3.012 (/hour = 10844.444)
Opened Tables/second = 0.514 (/hour = 1851.852)
Slow Queries/second = 0.004 (/hour = 14.815)
% of slow queries = 0.015%
Queries/second = 26.634 (/hour = 95881.481)
[/PHP]
here's my my.cnf:
[PHP][mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = interactive_timeout=75
set-variable = wait_timeout=75
set-variable = max_connect_errors=1000
set-variable = key_buffer=128M
set-variable = myisam_sort_buffer_size=16M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=8M
set-variable = table_cache=1024
set-variable = thread_cache_size=16
set-variable = max_allowed_packet=16M
set-variable = thread_concurrency=2
skip-locking
skip-innodb
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid[/PHP]
and in my phpbb forum's page generation readings:
[php]Page generation time: 34.5895s (PHP: 20% - SQL: 80%) - SQL queries: 24 - GZIP disabled - Debug on[/php]
any help?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.