bfcrew Posted August 18, 2007 Posted August 18, 2007 My server is Dual-Core AMD Opteron Processor 1216 HE 4GB RAM But I still feel so slow and high load when in peak time for more than 700 users. I already put eaccelerator, full text searching, turn off concurrent connections, upgrade to PHP5 but I'm still in Apache 1.3 My forum have more than 530.000 posts it it growing rapidly with more than 100 user registrations in a day. Please help me to optimize as I still feel slow when in peak time. this is my.cnf code:[mysqld] safe-show-database max_allowed_packet = 16M max_connections=250 skip-name-resolve #set-variable = interactive_timeout=100 #set-variable = wait_timeout=15 #set-variable = connect_timeout=10 skip-locking skip-innodb thread_concurrency=2 thread_cache=32 query_cache_size = 468435456 query_cache_type=1 query_cache_limit=1048576 [safe_mysqld] And my httpd.conf Regards
bfarber Posted August 20, 2007 Posted August 20, 2007 Is it just this site on the server, or are there others? Your hardware is strong enough to support the activity and content you mentioned. What is your key_buffer_size right now? Run this query and copy the output (removing any data you feel is too sensitive):show variables Also, during the "slower" times you mentioned, login to SSH and run this command and copy the outputtop This will show the top processes running, server load, memory/swap usage, and processor usage.
ibprobattle.com Posted August 20, 2007 Posted August 20, 2007 why run top when you can just do a system overview in dianostics in the acp is that not the same information that top will give you?
Bobsuch Posted August 20, 2007 Posted August 20, 2007 Yes I think the it is the same as top, but you don't have to go through the trouble of logging into your acp and doing those extra clicks :)
bfcrew Posted August 21, 2007 Posted August 21, 2007 @BFarber: this dedicated server is only for my forum there's nothing else This is my show variables info on mysqlmysql> show variables; +---------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------+ | back_log | 50 | | basedir | / | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | NO | | have_bdb | NO | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv | NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED | | have_isam | NO | | have_merge_engine | YES | | have_ndbcluster | NO | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir | | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 8388600 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_error | | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 16776192 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 250 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 10000 | | pid_file | /var/lib/mysql/server.hostname.com.pid | | port | 3306 | | preload_buffer_size | 32768 | | prepared_stmt_count | 0 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 468434944 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slave_transaction_retries | 0 | | slow_launch_time | 2 | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 2097144 | | sql_mode | | | sql_notes | ON | | sql_warnings | ON | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | sync_replication | 0 | | sync_replication_slave_id | 0 | | sync_replication_timeout | 0 | | system_time_zone | CDT | | table_cache | 64 | | table_type | MyISAM | | thread_cache_size | 32 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.22-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | | wait_timeout | 28800 | +---------------------------------+------------------------------------------+ 189 rows in set (0.07 sec) And this is my top infonote: it can be up to 50! this time is not really busy.. Tasks: 267 total, 14 running, 252 sleeping, 0 stopped, 1 zombie Cpu(s): 52.1% us, 47.6% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.3% hi, 0.0% si Mem: 3635396k total, 3532972k used, 102424k free, 118840k buffers Swap: 2096440k total, 2708k used, 2093732k free, 2002688k cached PID USER PR NI VIRT RES SHR S %Ctop - 23:03:47 up 9 days, 1:12, 2 users, load average: 30.03, 20.61, 14.06
bfcrew Posted August 21, 2007 Posted August 21, 2007 This is when 825 users at my board!top - 01:44:24 up 9 days, 3:52, 2 users, load average: 67.31, 51.01, 41.49 Tasks: 372 total, 60 running, 311 sleeping, 1 stopped, 0 zombie Cpu(s): 57.1% us, 42.8% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.1% hi, 0.0% si Mem: 3635396k total, 3619476k used, 15920k free, 17408k buffers Swap: 2096440k total, 2676k used, 2093764k free, 1551120k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 18045 mysql 15 0 543m 307m 2656 S 17 8.7 1838:25 mysqld 22320 nobody 16 0 49452 29m 15m R 5 0.8 3:29.70 httpd 5923 nobody 15 0 49480 22m 8656 S 5 0.6 0:07.13 httpd 7451 nobody 16 0 49168 22m 9332 R 5 0.6 0:03.92 httpd 7500 nobody 16 0 48908 22m 8972 R 5 0.6 0:02.76 httpd 7495 nobody 15 0 48780 21m 8452 S 4 0.6 0:02.42 httpd 7822 nobody 16 0 49956 21m 7448 R 4 0.6 0:01.12 httpd 15540 nobody 15 0 48768 28m 15m S 4 0.8 5:11.50 httpd 7931 nobody 16 0 48904 20m 7536 R 4 0.6 0:01.44 httpd 17036 nobody 16 0 49780 28m 14m R 4 0.8 4:34.49 httpd 22831 nobody 16 0 49548 27m 13m R 4 0.8 2:05.67 httpd 7874 nobody 16 0 49876 22m 7976 R 4 0.6 0:01.00 httpd 22743 nobody 16 0 49472 28m 14m R 4 0.8 3:18.35 httpd 5914 nobody 15 0 48364 22m 10m S 4 0.6 0:15.33 httpd 7854 nobody 15 0 47696 19m 7816 S 4 0.6 0:01.47 httpd 7862 nobody 15 0 48344 19m 7288 S 4 0.6 0:01.50 httpd 2263 nobody 15 0 47496 22m 10m S 3 0.6 0:29.23 httpd 20611 nobody 16 0 49148 28m 14m R 2 0.8 4:11.04 httpd 6737 nobody 15 0 47440 21m 9332 S 2 0.6 0:11.44 httpd 7427 nobody 16 0 43420 16m 8740 R 2 0.5 0:02.86 httpd 22267 nobody 16 0 47848 26m 13m R 2 0.7 2:55.85 httpd 26266 nobody 16 0 48824 26m 13m R 2 0.7 1:55.21 httpd 6709 nobody 16 0 48816 23m 9868 S 2 0.6 0:08.01 httpd 7424 nobody 16 0 47124 20m 8728 S 2 0.6 0:02.38 httpd 7928 nobody 15 0 46604 17m 6052 S 2 0.5 0:00.55 httpd 7975 nobody 15 0 48792 20m 7812 S 2 0.6 0:00.53 httpd 22266 nobody 16 0 51192 29m 13m R 2 0.8 2:49.23 httpd 26322 nobody 15 0 49092 27m 14m S 2 0.8 2:24.80 httpd 27105 nobody 16 0 51424 29m 13m R 2 0.8 1:29.82 httpd 5225 nobody 16 0 47944 23m 11m S 2 0.7 0:24.03 httpd 5877 nobody 16 0 47148 21m 10m R 2 0.6 0:17.36 httpd 5894 nobody 16 0 47004 21m 10m R 2 0.6 0:13.64 httpd 5910 nobody 15 0 43872 20m 11m S 2 0.6 0:19.01 httpd 7428 nobody 16 0 47212 19m 8300 R 2 0.6 0:02.98 httpd 7498 nobody 16 0 48640 21m 8688 R 2 0.6 0:02.93 httpd 26268 nobody 16 0 47636 25m 12m R 2 0.7 1:27.72 httpd 27903 nobody 15 0 44060 22m 13m S 2 0.6 1:36.10 httpd
bfarber Posted August 21, 2007 Posted August 21, 2007 Hello, It looks like you have the default MySQL config (at least in terms of the variables that affect performance) when you should be using the my.cnf-huge config. key_buffer_size is probably the #1 configuration option for MySQL, and right now you have it at the 8MB default. It should be approximately 25-50% of your server's memory (stay around 25% - 1GB memory for this option is plenty, and this isn't a dedicated mysql box). Take a look at this site. It's not gospel in the MySQL world, but this is an excellent and clearly written site (even if it's a few years old now) that will show you some of the more important mysql variables to change:http://www.databasejournal.com/features/my...cle.php/3367871 Change those in my.cnf and restart mysql. @the poster who asked why I said top - not all servers all you to see the info in the ACP (i.e. if exec is disabled, or if the user running php doesn't have rights to run top, etc.). It's simpler on a dedicated server to do it from shell.
bfcrew Posted August 21, 2007 Posted August 21, 2007 I change my.cnf to this setting and looks like load average is turning down for now[mysqld] safe-show-database max_allowed_packet = 16M max_connections=250 skip-name-resolve key_buffer=1024M table_cache=1024 record_buffer=1M sort_buffer=2M # Try number of CPU's*2 for thread_concurrency thread_concurrency=8 myisam_sort_buffer_size=64M log-bin server-id=1 #set-variable = interactive_timeout=100 #set-variable = wait_timeout=15 #set-variable = connect_timeout=10 skip-locking skip-innodb thread_cache=32 query_cache_size = 468435456 query_cache_type=1 query_cache_limit=1048576 [safe_mysqld] [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout I will update the top tomorrow at peak time. Thanks!
bfarber Posted August 21, 2007 Posted August 21, 2007 Ok cool. 3 other changes I could see helping looking at your config file are read_buffer_size read_rnd_buffer_size (Would set both to 2M) And query_cache_limit=1048576 Would increase to 2M (will allow more queries to be cached, and your query cache size is large enough to support it).
bfcrew Posted August 21, 2007 Posted August 21, 2007 my.cnf updated:[mysqld] safe-show-database max_allowed_packet = 16M max_connections=250 skip-name-resolve key_buffer=1024M table_cache=1024 record_buffer=1M sort_buffer_size=2M read_buffer_size=2M read_rnd_buffer_size=2M # Try number of CPU's*2 for thread_concurrency thread_concurrency=8 myisam_sort_buffer_size=64M log-bin server-id=1 #set-variable = interactive_timeout=100 #set-variable = wait_timeout=15 #set-variable = connect_timeout=10 skip-locking skip-innodb thread_cache=32 query_cache_size = 468435456 query_cache_type=1 query_cache_limit=2M [safe_mysqld] [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout
bfcrew Posted August 22, 2007 Posted August 22, 2007 bfarber: I got mysql overload! my server was crash.. and mysql server dead.. :( Right now I'm turning back my old my.cnf to the system. please help!
bfarber Posted August 22, 2007 Posted August 22, 2007 What exactly do you mean by mysql overload? What happened (exactly)? Are there any error logs? Were there queries locked? I'd need more info - the settings shouldn't have hurt anything by themself from what I can see.
bfcrew Posted August 23, 2007 Posted August 23, 2007 This is what I got from support:Your disk usage is full. Please see that your /var partition is full. [root@server var]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda5 9.9G 1.3G 8.1G 14% / /dev/sdb1 231G 37G 182G 17% /backup /dev/sda1 99M 20M 74M 22% /boot none 1.8G 0 1.8G 0% /dev/shm /dev/sda7 198G 18G 171G 10% /home /dev/sda8 1012M 37M 924M 4% /tmp /dev/sda3 9.9G 2.7G 6.7G 29% /usr /dev/sda2 9.9G 9.4G 0 100% /var You have only 9.9 G, for var partition, and all the space is used by mysql databases. I really don't understand about this.
bfarber Posted August 23, 2007 Posted August 23, 2007 It means you are out of disk space. Has nothing to do with the tweaks in that case. How many databases do you have? Just one? Are they using MyISAM engine? InnoDB takes up 3x more disk space than myisam. You could ask your host to repartition that drive to add more space, but if you are using up 9GB with only 530,000 posts there is something odd going on there.
Xtrm2Matt_merged Posted August 24, 2007 Posted August 24, 2007 Hi there, Would the settings posted by BFCrew be fine on a Core2Duo 2.4GHz, 4GB RAM, Windows 2003 web server?[mysqld] safe-show-database max_allowed_packet = 16M max_connections=250 skip-name-resolve key_buffer=1024M table_cache=1024 record_buffer=1M sort_buffer_size=2M read_buffer_size=2M read_rnd_buffer_size=2M # Try number of CPU's*2 for thread_concurrency thread_concurrency=8 myisam_sort_buffer_size=64M log-bin server-id=1 #set-variable = interactive_timeout=100 #set-variable = wait_timeout=15 #set-variable = connect_timeout=10 skip-locking skip-innodb thread_cache=32 query_cache_size = 468435456 query_cache_type=1 query_cache_limit=2M [safe_mysqld] [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout The only other thing running on the server would be the forum (hosted on Apache). Been tweaking my my.cnf for days now, since I realised I was using the 64MB one on a 4GB machine! x_x Cheers
Darkside_RG_merged Posted August 25, 2007 Posted August 25, 2007 :unsure: Just a thought and if I'm wrong I apologise as I haven't read your config files in detail ....but wipe out any bin log files other than the most recent and consider if bin logging is worth having at all if you back up frequently. If bin logging is enabled it can rapidly consume all your disk space.
bfcrew Posted August 27, 2007 Posted August 27, 2007 I finally found solution for this! DON'T USE APACHE!!! If you're really have busy forum it will blow your server load out! You may find there's a lot webserver software that you can use except Apache. Right now I'm using LiteSpeed (free version) when with Apache I can get server load more than 50 in peak time, but when I'm using LiteSpeed the load are much much faster only 5 in my dual core server. Doesn't mean to make a commercial ads but it's true! I will buy LS as soon I have money it's pretty expensive but it's worth every cent since you don't have to upgrade again. I will have no worry if my forum have 2000 user online at the same time. :D If you don't like LS because it's closed source maybe you can try Lighttpd or NginX but as I know they don't support cPanel :( Thanks for your help. Regards BFCrew :)
bfarber Posted August 27, 2007 Posted August 27, 2007 Good to hear that you're getting somewhere. :) I still say the MySQL tweaks are worth the trouble - I'd figure out the disk space issue, and then reapply them. Do a few at a time and test them out. If you run into any problems just revert back to the last config that did work. @Darkside, yes, you have about the same specs. It's just important to note that if there are OTHER sites on the server, some of those settings are per-thread and can mean using much more memory than you are really setting.
ckoebke Posted August 27, 2007 Posted August 27, 2007 I change my.cnf to this setting and looks like load average is turning down for now . .. ... [mysqld] [color="#ff0000"][b]#log-bin[/b][/color] ... .. . I will update the top tomorrow at peak time. Thanks! Comment out the "log-bin" if you dont have a mysql cluster or use bin logfiles for recover your databases. These logfiles eat too much diskspace.This is what I got from support: I really don't understand about this. Same as above. Binary logfiles eat too much diskspace
bfcrew Posted August 29, 2007 Posted August 29, 2007 Now my server are smooth like heaven.. :D I can sleep well now.. lol...
MarcusInMd Posted October 20, 2007 Posted October 20, 2007 Litespeed seem to work wonders. I started using about two months ago. Our server loads were averaging around 1.5 to 2 during light times during the day with about 250 to 300 users online. But from time to time the server load would just spike like crazy for no reason. I fought hard last year for a week when we had heavy traffic of almost 1000 online at once for most days. Was a mess. We have not had the traffic yet this fall that we did over the winter so I can't say that litespeed has fixed our load issues but I can say that since installing it load has dropped to below .5 to .75 on average with typical 250 to 300 users online. Much improved over apache. NOw I am trying to tweak litespeed for optimum performance.
yacenty Posted November 14, 2007 Posted November 14, 2007 last days I had moved to nginx server. I'm totally crazy with its performance. Earlier with 300 users online I had load in average 8, Today I havent seen 2 - it was in between 0.8 to 1.8 Also I had changed ea to xcache - why I did it so late?
Recommended Posts
Archived
This topic is now archived and is closed to further replies.