I wanted to ask you guys (especially Eva2k ;) if you could give me some hints on optimizing my mysql setting. When I bought my server it was preconfigured with everything installed and it ran fin. After moving the site over I noted that it is slow at times and became even slower when upgrading to vb3, I actually was about to purchase a memory upgrade but someone told me to optimize mysql setting before because that could have been the problem.
I looked at my my.conf file in /etc and it just had this in it:
[mysqld]
set-variable = max_connections=500
I looked at Eva's guide and changed it to:
[mysqld]
set-variable = max_connections=300
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
Now I wanted to ask if you could give me some more hints on improving the settings? I am on a PIV 2.8 GHz with 512MB RAM. My site receives around 50k hits a day and forum has around 200 users active average.
Thanks for your help,
Sebastian
BTW: http://i4net.tv/marticle/get.php?action=getarticle&articleid=3 <-- seems to be down, is that temporary or is the site gone?
edit: my cpu load is still at around 2, I wonder why it is so high, will more memory help or what should I do?
Thanks your your reply, I'll try my best with answering all those questions:
1. is this on dedicated or shared virual server
It is a dedicated server I rented but I host more than one site on it.
2. your server specs. For example:
cpu speed/type single or dual cpus): PIV 2.8 GHZ
how much memory installed: 512mb ram
hard drive type/configuration: 2x 40GB EIDE non raid
linux distributor or windows version: redhat linux 9.0
apache/IIS version: (IN WHM it says:) 1.3.29
PHP version: php 4.3.3
MySQL version: mysql 4.0.15
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
I don't really know that one.
4. if possible how mysql was compiled/installed
no idea :(
5. your top stats
what you mean with that?
6. your mysql configuration variables located at /etc/my.cnf or c:my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type
[mysqld]
set-variable = max_connections=300
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
7. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
+--------------------------+------------+
Variable_name Value
+--------------------------+------------+
Aborted_clients 0
Aborted_connects 2491
Bytes_received 60734261
Bytes_sent 2845682264
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 152869
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 3
Com_delete 15179
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 2
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 19003
Com_insert_select 133
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 446
Com_optimize 33
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 180
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 46387
Com_set_option 101
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 101
Com_show_databases 11
Com_show_fields 101
Com_show_grants 1
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 153
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 3
Com_show_innodb_status 0
Com_show_tables 41
Com_show_variables 2
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 445
Com_update 62867
Connections 154388
Created_tmp_disk_tables 177
Created_tmp_tables 3739
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 13762
Handler_read_first 1244750
Handler_read_key 632376
Handler_read_next 4336887
Handler_read_prev 11426
Handler_read_rnd 408687
Handler_read_rnd_next 3960492
Handler_rollback 0
Handler_update 65289
Handler_write 700762
Key_blocks_used 6867
Key_read_requests 4539681
Key_reads 6701
Key_write_requests 89242
Key_writes 69278
Max_used_connections 148
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 64
Open_files 113
Open_streams 0
Opened_tables 1500
Questions 634475
Qcache_queries_in_cache 1076
Qcache_inserts 46230
Qcache_hits 184266
Qcache_lowmem_prunes 0
Qcache_not_cached 151
Qcache_free_memory 28239816
Qcache_free_blocks 330
Qcache_total_blocks 2669
Rpl_status NULL
Select_full_join 2663
Select_full_range_join 6
Select_range 6975
Select_range_check 0
Select_scan 20895
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 2
Sort_merge_passes 0
Sort_range 4247
Sort_rows 408559
Sort_scan 3859
Table_locks_immediate 180519
Table_locks_waited 142
Threads_cached 0
Threads_created 154387
Threads_connected 3
Threads_running 1
Uptime 45849
+--------------------------+------------+
8. oh and is your vB the only thing on the server? or other scripts? sites?
no, there are other sites being hosted on the server and the also have mysql scripts.
9. how many average and max concurrent users on your vB forum ?
average: 150 max: 300, maybe 400
10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://csbanana.com/info.csb
11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
12. what version of vB are you running ?
vb 3.0 gamma
Thanks so much for your help,
Sebastian
I tried installing a second apache (version 2) just for the images but I couldn't get it to bind to an ip because it said it is already used even thought the main apache is bound to a different one.
Would be great if you could help me make my server fasta :D
BTW: http://i4net.tv/marticle/get.php?action=getarticle&articleid=3 <-- seems to be down, is that temporary or is the site gone?
edit: my cpu load is still at around 2, I wonder why it is so high, will more memory help or what should I do?
1GB ram would be better
as to my site at i4net.tv, my forum and entire server is down right now, 1of of the dual cpus died, onboard scsi controller died, scsi disk dying and 1 ide back drive died.. all need replacing :(
try this new /etc/my.cnf below and restart mysql
[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
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 = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
#If you have any other info about this subject , Please add it free.# |
