Optimize my server

Please, I'm having problems with the database after the migration from VB to XF

120224 11:10:06 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1635778560
read_buffer_size=1048576
max_used_connections=20
max_threads=402
threads_connected=7
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2836610 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2aab5f761f00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x420a4100 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x987f5e]
/usr/sbin/mysqld(handle_segfault+0x382) [0x5ebea2]
/lib64/libpthread.so.0 [0x3655a0eb70]
/usr/sbin/mysqld(filesort(THD*, st_table*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*)+0x597) [0x6cc047]
/usr/sbin/mysqld [0x65e5e5]
/usr/sbin/mysqld(JOIN::exec()+0x819) [0x667929]
/usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x199) [0x6695e9]
/usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x167) [0x669ec7]
/usr/sbin/mysqld [0x5f5660]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x739) [0x5f8909]
/usr/sbin/mysqld(Prepared_statement::execute(String*, bool)+0x387) [0x677037]
/usr/sbin/mysqld(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*)+0x8b) [0x677c3b]
/usr/sbin/mysqld(mysqld_stmt_execute(THD*, char*, unsigned int)+0xfa) [0x677e2a]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xc9d) [0x5ff32d]
/usr/sbin/mysqld(do_command(THD*)+0xe6) [0x5ffd96]
/usr/sbin/mysqld(handle_one_connection+0x9e) [0x5f18ae]
/lib64/libpthread.so.0 [0x3655a0673d]
/lib64/libc.so.6(clone+0x6d) [0x36552d456d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x12215dd0): SELECT session_activity.*
,
user.*,
user_profile.*,
user_option.*
FROM xf_session_activity AS session_activity

LEFT JOIN xf_user AS user ON
(user.user_id = session_activity.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = user.user_id)
LEFT JOIN xf_user_option AS user_option ON
(user_option.user_id = user.user_id)
WHERE (session_activity.view_date > 1330085406)
ORDER BY session_activity.view_date DESC
Connection ID (thread ID): 379015
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
120224 11:10:07 mysqld_safe Number of processes running now: 0


my.cnf
[mysqld]
datadir=/mysql/mysqldata
socket=/mysql/mysqldata/mysql.sock

safe-show-database
back_log = 50
skip-innodb
max_connections = 400
key_buffer_size = 1560M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_definition_cache =10000
table_open_cache = 10000
thread_cache_size = 512
wait_timeout = 300
open_files_limit = 16384
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 512M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data=1024
net_buffer_length = 16384
max_connect_errors = 10000
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 4
ft_min_word_len=2
max_delayed_threads=0

[mysqlcheck]
socket=/mysql/mysqldata/mysql.sock


[mysqldump]

socket=/mysql/mysqldata/mysql.sock
quick
max_allowed_packet = 128M

[myisamchk]
datadir=/mysql/mysqldata
socket=/mysql/mysqldata/mysql.sock
key_buffer_size = 2560M
sort_buffer_size = 16M
read_buffer_size = 16M
write_buffer_size = 16M

Apache
StartServers 25
<IfModule prefork.c>
MinSpareServers 25
MaxSpareServers 35
</IfModule>
ServerLimit 256
MaxClients 2256
MaxRequestsPerChild 1000
KeepAlive On
KeepAliveTimeout 2
MaxKeepAliveRequests 100

WebServer:
Running Cpanel/Whm
Apache 2.0
PHP 5.2.17
Intel Xeon-Lynnfield 3460-Quadcore [2.8GHz]
12gb DDR3
Western Digital WD Caviar RE 16 WD2500YS [250GB] - SYSTEM
Western Digital WD Caviar RE 16 WD2500YS [250GB] - MYSQL
Western Digital WD Caviar RE 16 WD2500YS [250GB] - BACKUP
 
That does not sound good at all and is not something that I've ever seen before. Have you been running MySQL on this server without issue up to this point or is this a new install? It doesn't sound like this is a Xenforo issue. I'd be interested to see if it's possible to manually compile a MySQL build and see if it exhibits the same issues.
 
Signal 11 typically means your database is corrupt and you should recover from a backup, if you do not know how to repair it. Or just as the error message suggest, some kind of hardware failure, in which case you should contact your host and assume corruption has taken place (as it's more than likely).

I'd look into corruption 1st. Check your vBulletin database before you import. vBulletin depends heavily on Myisam, which is prone to easy corruption (debatable). Normally vBulletin or PhpMyAdmin can correct these errors. If they find any, chances are they got ported over as such.

You could also do the same for XenForo, assuming it too is using Myisam via the import.

I would suggest using Innodb. You can convert over. But do some reading on this first as I recall 4 tables in XenForo need to stay as Myisam (technically, they don't need to stay, but its recommended for speed).

Assuming your database checks out. Hardware would be my next best guess.
 
I think the problem is not the hardware, after 6 months using the same equipment in vbulletin, never had problems after 4 days of migration to the XF the database started to show errors

According to the logs and even the XF is having memory overflow, which leads me to believe that the problem is setting.
error.webp
 
What is the stack trace if you click on one of those errors?

Essentially though, your server is out of physical memory and has no swap space left.
 
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Out of memory (Needed 8 bytes) - library/Zend/Db/Statement/Mysqli.php:214
Gerado Por: Conta Desconhecida, Hoje às 14:41
Rastreamento de Pilha
#0 /home/xxx/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/xxx/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/xxx/public_html/library/Zend/Db/Adapter/Abstract.php(825): Zend_Db_Adapter_Abstract->query('????SELECT cach...', 1)
#3 /home/xxx/public_html/library/XenForo/Model/User.php(810): Zend_Db_Adapter_Abstract->fetchOne('????SELECT cach...', 1)
#4 /home/xxx/public_html/library/XenForo/Model/User.php(785): XenForo_Model_User->setPermissionsOnVisitorArray(Array)
#5 /home/xxx/public_html/library/XenForo/Visitor.php(395): XenForo_Model_User->getVisitingGuestUser()
#6 /home/xxx/public_html/library/XenForo/Session.php(204): XenForo_Visitor::setup(false, Array)
#7 /home/xxx/public_html/library/XenForo/Controller.php(286): XenForo_Session::startPublicSession(Object(Zend_Controller_Request_Http))
#8 /home/xxx/public_html/library/XenForo/Controller.php(298): XenForo_Controller->_setupSession('Trophies')
#9 /home/xxx/public_html/library/XenForo/FrontController.php(309): XenForo_Controller->preDispatch('Trophies')
#10 /home/xxx/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#11 /home/xxx/public_html/index.php(13): XenForo_FrontController->run()
#12 {main}
Solicitar Estado
array(3) {
["url"] => string(56) "http://www.plusgsm.com.br/members/daviddiniz.45/trophies"
["_GET"] => array(0) {
}
["_POST"] => array(0) {
}
}
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2836610 K
The max each query could use is 2.770GB

Does the machine really have 12gb DDR3 available?

There are a bunch of things in your configuration that have been tuned for MyISAM, I would go back and get the default mysql configurations, watch it for a few days and see how it does.
 
My webserver is Dedicated Server by SoftLayer

top - 16:30:16 up 8 days, 6:05, 1 user, load average: 1.40, 1.11, 1.21
Tasks: 247 total, 4 running, 243 sleeping, 0 stopped, 0 zombie
Cpu0 : 14.0%us, 0.7%sy, 0.0%ni, 85.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 10.0%us, 1.3%sy, 0.0%ni, 88.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.7%us, 0.0%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 2.0%us, 1.0%sy, 0.0%ni, 97.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 10.3%us, 1.3%sy, 0.0%ni, 88.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 17.9%us, 1.0%sy, 0.0%ni, 81.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 96.7%us, 2.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 1.3%si, 0.0%st
Cpu7 : 2.3%us, 0.3%sy, 0.0%ni, 97.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 12291208k total, 10291236k used, 1999972k free, 303624k buffers
Swap: 2096440k total, 192k used, 2096248k free, 7661736k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31642 mysql 15 0 1023m 699m 4392 S 105.5 5.8 0:57.84 mysqld
32419 nobody 15 0 275m 45m 11m S 7.7 0.4 0:00.23 httpd
32378 nobody 15 0 271m 43m 13m S 6.3 0.4 0:00.41 httpd
32379 nobody 15 0 272m 43m 12m S 4.7 0.4 0:00.47 httpd
32274 nobody 15 0 272m 44m 12m S 3.7 0.4 0:00.91 httpd
32302 nobody 15 0 274m 45m 12m S 3.7 0.4 0:00.66 httpd
32311 nobody 15 0 270m 41m 11m S 3.7 0.3 0:01.16 httpd
32380 nobody 15 0 271m 43m 13m S 3.7 0.4 0:00.83 httpd
32245 nobody 15 0 270m 43m 13m S 3.0 0.4 0:01.94 httpd
32413 nobody 15 0 271m 41m 10m S 3.0 0.3 0:00.09 httpd
32411 nobody 15 0 274m 43m 9940 S 2.7 0.4 0:00.17 httpd
32425 nobody 18 0 285m 54m 10m R 2.7 0.5 0:00.08 httpd
32420 nobody 17 0 284m 52m 9736 R 2.3 0.4 0:00.07 httpd
32412 nobody 15 0 274m 43m 10m R 1.7 0.4 0:00.05 httpd
32297 nobody 15 0 284m 58m 14m S 0.7 0.5 0:01.75 httpd
32426 nobody 17 0 270m 38m 9488 S 0.7 0.3 0:00.02 httpd
32236 nobody 15 0 273m 44m 12m S 0.3 0.4 0:01.36 httpd
32310 nobody 15 0 278m 50m 13m S 0.3 0.4 0:01.27 httpd
32323 nobody 15 0 279m 56m 18m S 0.3 0.5 0:01.26 httpd
32332 nobody 15 0 274m 45m 11m S 0.3 0.4 0:00.94 httpd

In any case, removed the Maridb and Mysql installed, let's see how it will behave
 
Informação do Erro
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Out of memory (Needed 512 bytes) - library/Zend/Db/Statement/Mysqli.php:214
Gerado Por: Conta Desconhecida, Hoje às 14:37
Rastreamento de Pilha
#0 /home/plusgsm/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/plusgsm/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/plusgsm/public_html/library/XenForo/Model.php(218): Zend_Db_Adapter_Abstract->query('????SELECT post...', 10414, 2)
#3 /home/plusgsm/public_html/library/XenForo/Model/Post.php(198): XenForo_Model->fetchAllKeyed('????SELECT post...', 'post_id', 10414)
#4 /home/plusgsm/public_html/library/XenForo/ControllerPublic/Thread.php(69): XenForo_Model_Post->getPostsInThread(10414, Array)
#5 /home/plusgsm/public_html/library/XenForo/FrontController.php(310): XenForo_ControllerPublic_Thread->actionIndex()
#6 /home/plusgsm/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /home/plusgsm/public_html/index.php(13): XenForo_FrontController->run()
#8 {main}
Solicitar Estado
array(3) {
["url"] => string(69) "http://www.plusgsm.com.br/threads/emulador-vbagx-1-25-o-melhor.10414/"
["_GET"] => array(0) {
}
["_POST"] => array(0) {
}
}
 
My webserver is Dedicated Server by SoftLayer
In any case, removed the Maridb and Mysql installed, let's see how it will behave
Three things which version of mysql are you running?
And looking at your my.cnf file I don't see any of the InnoDB specific settings, are they set up?
Are the Xenforo tables InnoDB or did you create them as MyISAM ?
 
mysql version 5.1.61
all tables are using MyISAM tables except the Memory

To what extent recommend using innodb? I read several articles recommend using MyISAM for better performance.

Currently my largest tables are:
xf_user_field_value - 3,799,157 rows - 140,2mb
archived_import_log - 1,880,307 rows - 60,6mb
xf_ip - 1,566,589 rows - 125,9mb
xf_post - 1,090,209 rows - 492,4mb
xf_user - 475,075 rows - 77,9mb

For the system to search the forum I'm using elasticsearch provided by Xenforo.com
 
what is making me think about moving to Innodb in this recommendation is tuningprimer
TABLE LOCKING
Current Lock Wait ratio = 1 : 370
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

Any recommendations on which table should be used to Innodb?
 
forgot who recommended it, but you could run the python script for some suggestions.
http://mysqltuner.pl/mysqltuner.pl

mysqltuner.pl 1.2.0 result
./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.60-MariaDB-mariadb107
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 371)
[--] Data in InnoDB tables: 1G (Tables: 137)
[--] Data in MEMORY tables: 1M (Tables: 3)
[!!] Total fragmented tables: 40

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11m 12s (72K q [108.318 qps], 8K conn, TX: 933M, RX: 18M)
[--] Reads / Writes: 64% / 36%
[--] Total buffers: 4.1G global + 6.3M per thread (200 max threads)
[OK] Maximum possible memory usage: 5.4G (45% of installed RAM)
[OK] Slow queries: 0% (1/72K)
[OK] Highest usage of available connections: 16% (32/200)
[OK] Key buffer size / total MyISAM indexes: 1.5G/799.0M
[!!] Key buffer hit rate: 94.6% (61K cached / 3K reads)
[OK] Query cache efficiency: 43.7% (16K cached / 37K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 6K sorts)
[!!] Temporary tables created on disk: 36% (746 on disk / 2K total)
[OK] Thread cache hit rate: 99% (32 created / 8K connections)
[OK] Table cache hit rate: 36% (631 open / 1K opened)
[OK] Open file limit used: 1% (796/63K)
[OK] Table locks acquired immediately: 99% (62K immediate / 62K locks)
[OK] InnoDB data size / buffer pool: 1.7G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
 
mysql version 5.1.61
all tables are using MyISAM tables except the Memory
No. You have a bunch of tables
[--] Data in InnoDB tables: 1G (Tables: 137)

Unless that is some other database?

However in your my.conf you have
skip-innodb

My guess is that is why you are getting,
[!!] Key buffer hit rate: 94.6% (61K cached / 3K reads)
And
[!!] Temporary tables created on disk: 36% (746 on disk / 2K total)

You kind of need to go back to basics, configure for InnoDB and it will get better.

 
Top Bottom