cPanel on x86_64 Apache/MySQL/HighLoads Problem(s)...

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Hello,

Simple Box Info.:
PROCESSOR: Dual AMD Opteron 64bit
RAM: 3gb ecc ram
HDD: 2x250gb sata hdd's , having /var/lib/mysql on a 3rd separate unused sata hdd also
OS: CentOS 4.2 x86_64
WHM/cPanel Version: WHM 10.8.0 cPanel 10.8.2-E5
Apache Version: 1.3.34 , with mod_fastcgi,eaccelerator
MySQL Version: 4.1.14-standard
-------------------------------

Now, as i've stated most of my server's main asked about configs, i have these problems as stated below and "Hope" that cpanelnick or one of the "Gurus" looks to find me a solution as i've done finding the solution by myself.!!!

1. i keep getting this messages in my /var/log/messages from time to time so close before my server's load go mad (Mar 12 08:18:41 server kernel: httpd[8101]: segfault at 0000000000000000 rip 00002aaaac166f33 rsp 00007fffffd9be20 error 6)

2. my uptime loads are usually from 5 to 30 and normall at 8

3. my "ps aux | grep -c httpd" gives me average from 90 to 130 although im having my timeout set to 20 in httpd.conf and i have my sysctl config optimized for finilazing timeouts a bit faster than default and having keep alives off.

4. my "netstat -a -n|grep -E "^(tcp)"| cut -c 68-|sort|uniq -c|sort -n|grep TIME_WAIT" gives me always from 1300 to (1500 TIME_WAIT)

5. also i have my mysql conf optimized and if someone could this listing it might help saves me from this load then it'll be nice to place it here.

,Thanks for all.
 
Last edited:

Franzh

Member
Dec 16, 2004
9
0
151
Do you have enough memory? When the system starts to swap, you often get a lot higher load.

With mysql you can always tweak out more performance. Run SHOW STATUS; and SHOW VARIABLES; and post it here.

I would say that 8 is very high. I'm starting to wonder what's wrong if my load is higher than 3.
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Thanks Franzh for replying, here's the info you requested.

####################################
mysql> SHOW STATUS;
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Aborted_clients | 185 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 189864292 |
| Bytes_sent | 11913732929 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 29650 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 6995 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 6 |
| Com_grant | 9 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 25699 |
| Com_insert_select | 140 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 15780 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2444 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 113899 |
| Com_set_option | 367 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 112 |
| Com_show_databases | 8 |
| Com_show_errors | 0 |
| Com_show_fields | 112 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 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_privileges | 0 |
| Com_show_processlist | 24 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 118 |
| Com_show_variables | 8 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_close | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 15783 |
| Com_update | 27058 |
| Com_update_multi | 99 |
| Connections | 23698 |
| Created_tmp_disk_tables | 6871 |
| Created_tmp_files | 5276 |
| Created_tmp_tables | 12089 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 20396 |
| Handler_discover | 0 |
| Handler_read_first | 8365 |
| Handler_read_key | 5552388 |
| Handler_read_next | 12910689 |
| Handler_read_prev | 69274 |
| Handler_read_rnd | 4733506 |
| Handler_read_rnd_next | 140558431 |
| Handler_rollback | 0 |
| Handler_update | 417808 |
| Handler_write | 3300557 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 282316 |
| Key_blocks_used | 41045 |
| Key_read_requests | 23789107 |
| Key_reads | 58107 |
| Key_write_requests | 747968 |
| Key_writes | 108230 |
| Max_used_connections | 27 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1512 |
| Open_streams | 0 |
| Open_tables | 831 |
| Opened_tables | 872 |
| Qcache_free_blocks | 2816 |
| Qcache_free_memory | 104704608 |
| Qcache_hits | 777438 |
| Qcache_inserts | 113038 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 648 |
| Qcache_queries_in_cache | 4494 |
| Qcache_total_blocks | 12880 |
| Questions | 1039347 |
| Rpl_status | NULL |
| Select_full_join | 76 |
| Select_full_range_join | 120 |
| Select_range | 16948 |
| Select_range_check | 0 |
| Select_scan | 26346 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 14 |
| Sort_merge_passes | 54142 |
| Sort_range | 5575 |
| Sort_rows | 19386658 |
| Sort_scan | 13187 |
| Table_locks_immediate | 229118 |
| Table_locks_waited | 426 |
| Threads_cached | 21 |
| Threads_connected | 6 |
| Threads_created | 27 |
| Threads_running | 1 |
| Uptime | 7299 |
+----------------------------+-------------+
162 rows in set (0.00 sec)
#########################################


FOLLOW WITH THE NEXT REPLY DUE TO CHARS. LIMITATIONS >
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
#########################################
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 | 10 |
| 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_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 | 120 |
| join_buffer_size | 1044480 |
| key_buffer_size | 402653184 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| 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 | 1048576 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 8 |
| max_connections | 750 |
| max_delayed_threads | 20 |
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4360 |
| pid_file | /var/lib/mysql/server.my_domain_name.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| 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 | 1 |
| 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 | 32776 |
| sql_mode | |
| storage_engine | MyISAM |
| sql_notes | ON |
| sql_warnings | ON |
| sync_binlog | 0 |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| sync_frm | ON |
| system_time_zone | EST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 1024 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.14-standard-log |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
| wait_timeout | 120 |
+---------------------------------+----------------------------------------------+
184 rows in set (0.14 sec)
#######################################


Hope you could work out something from this.. :)
 

Franzh

Member
Dec 16, 2004
9
0
151
Ok, a quick look shows that one setting is very wrong.
Look for sort_merge_passes in your status. You see a quite high figure here. Over 54000 after only 2 hours. Sort_merge_passes indicates that when you have ORDER BY in your queries the sorts has to be done in many smaller sorts. This is due to the sort_buffer_size (not the myisam_sort_buffer_size) being set very low. Try raising it from 32k to 1M.

Also, the Handler_read_rnd and the Handler_read_rnd_next suggests you're doing a lot of table scans. Perhaps your tables don't have the proper indexes or your queries are not taking advantage of them. Take a look at this http://dev.mysql.com/doc/refman/4.1/en/explain.html
Proper use of indexes could make a query 100 times faster than a query not using indexes properly.

Your log is showing only 14 slow queries which would normally be quite normal. However you have set the long_query_time to 10. Try changing this to 3 instead and then have a look at the slow query log to see if you can find queries to optimize.

You're using eaccelerator. Have you tried eaccelerators shared memory feature? (eaccelerator_put(), eaccelerator_get())
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Well, i think you have a good knowledge in mysql or something :) , so i think i pasted you below my mysql conf so maybe it could help, and i've changed my sort buffer value, so here is it..


#########################
[mysqld]
##datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=750
##wait_timeout = 14400
wait_timeout = 30
safe-show-database
connect_timeout=10
interactive_timeout=120
##max_heap_table_size = 64M
max_connect_errors=8
tmp_table_size=128
##back_log = 50
skip-locking
skip-innodb
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
##query_cache_min_res_unit = 8192
##query_cache_min_res_unit = 2K
server-id=1
##thread_concurrency=8
thread_cache_size=256
table_cache=1200
join_buffer_size = 1M
sort_buffer_size = 1M
read_buffer_size = 1M
record_buffer=1M
##myisam_sort_buffer_size=128M
key_buffer = 384M
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3


[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3

[mysqldump]
quick
max_allowed_packet=10M

[mysqlhotcopy]
interactive-timeout=25

[isamchk]
key_buffer=50M
sort_buffer_size=768K
read_buffer_size=256K
read_rnd_buffer_size=512K

[myisamchk]
key_buffer=50M
sort_buffer_size=768K
read_buffer_size=256K
read_rnd_buffer_size=512K
#############################
 

Franzh

Member
Dec 16, 2004
9
0
151
Found another one :cool:

tmp_table_size...
MySQL often creates temporary tables while processing your query. If possible MySQL will use RAM for this. If the available RAM for temporary tables has runned out, MySQL will use disk insted (much slower). The default setting for this is 32M but I think however tried to optimize your my.cnf wanted to raise it to 128M. However, the person forgot the "M" so it says tmp_table_size=128 instead. 128 is even below the minimum value (1k) for this setting so MySQL ignores this setting and uses 1k instead. This is terrible for performance. You should probably notice a boost in performance if you change to tmp_table_size=128M. (64M will probably be enough on your system)

Please run the "free" command from your linux prompt. This will show how much memory is being used, how much is being swapped and how much is being cached.
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Ok, i've added the letter "M" at the end of the tmp value and also i'll paste you here the output of free...


[email protected] [~]# free -m
total used free shared buffers cached
Mem: 3007 2598 408 0 28 708
-/+ buffers/cache: 1861 1145
Swap: 5867 153 5713
 
Last edited:

Franzh

Member
Dec 16, 2004
9
0
151
Memory is looking good. Would be interesting to know if you noticed any performance improvements yet.
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Well, actually it's not swapping like before but also the loads now is from 4 to 10 , also the TIME_WAIT value is still high (now it's 1268) also i've set the apache timeout too low and when i do "ps aux |grep -c httpd" it outputs a value not less than 80 and eventually 120, i have this variable in my apache which i think is good enough to finalize connections, or you have another idea ?!

#############
MinSpareServers 4
MaxSpareServers 18
StartServers 6
#############

And really thanks for holding up till now with me :)
 

Franzh

Member
Dec 16, 2004
9
0
151
If you could show me the output of phpinfo() I could have a look.

You might want to send me a link or via a private message to prevent everyone from find out your server's weeknesses.

80-120 http processes is nothing strange. Apache has something called keepalive processes. When you send a request to an Apache server the same connection can be used for images etc. on the page you requested. This makes a connection last for some 10-15 seconds.
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Nice, this is turning so bad now as when i was doing some checks i found my load got to 70 by itself and i then checked my /var/log/messages then i found this interesting line...

###############

Mar 13 13:24:07 server kernel: httpd[5775] general protection rip:2aaaac166f33 rsp:7fffffb09540 error:0

###############



Hope anybody could get a reasonable description for this problem or how to get this solved...!!!!!!!
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
So, isn't there anybody in this whole cPanel official forum which could help in my issues ! :mad:


i also got this from a couple of hours just before a bad load flow up and getting the box almost unreachable....

############

Mar 14 07:32:23 server httpd: *** glibc detected *** double free or corruption (out): 0x00007fffff83
d870 ***

############
 

chirpy

Well-Known Member
Verifed Vendor
Jun 15, 2002
13,437
33
473
Go on, have a guess
While this forum is on their domain, it is not an official avenue for support from cPanel. If you want that, you'll have to log a ticket through your cPanel license provider. If you son't want to do that, then it would seem sensible to hire a server administrator to help you out.
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
Well, i thought there could be another better way for help than sending a support ticket to my license provider and let them contact cpanel then they contact them back and ask for box access info and login to check which actually won't take less than 2 days :confused:
 

platinum

Active Member
Nov 21, 2004
30
0
156
I'm actually having a similar problem with the same glibc error and extremely high loads (on a dual xeon machine) -

*** glibc detected *** double free or corruption (!prev):

also running a 64bit version of linux (centOS) would be interested to see if anyone has any ideas!
 

mOdY

Well-Known Member
Dec 25, 2004
80
0
156
platinum said:
I'm actually having a similar problem with the same glibc error and extremely high loads (on a dual xeon machine) -

*** glibc detected *** double free or corruption (!prev):

also running a 64bit version of linux (centOS) would be interested to see if anyone has any ideas!
nice, after tracing the cause of this problem i think it's the old glibc which is causing this problems plus too many segmentation faults in the server logs, upgrading to glibc-2.4 will fix this although we will need to know if this would affect cpanel by anything... :rolleyes: