Jump to content

Asking For Optimization Suggestion


Guest bfcrew

Recommended Posts

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

Link to comment
Share on other sites

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 output

top



This will show the top processes running, server load, memory/swap usage, and processor usage.

Link to comment
Share on other sites

@BFarber: this dedicated server is only for my forum there's nothing else

This is my show variables info on mysql

mysql> 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 info
note: 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 %C

top - 23:03:47 up 9 days,  1:12,  2 users,  load average: 30.03, 20.61, 14.06





Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

  • 4 weeks later...

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...