What storage/database to use for big boards

[onkelchen]

Hi there,
we're running a big vbulletin having 2,5 million posts (4,5G database) and about 2500-3000 users online. We're currently having two dedicated linux servers for the forums, however this is not enough anymore.
Now we're planning upgrade scenarios, therefore I ask what possibilities there are, and which you do suggest.

Just to give you the necessary informations:
1.
Dedicated hosting on two servers, just for the forums, no other scripts are running on these servers, running a vBulletin 3.5.3 with patched XSS flaws from 3.5.4 release

2.
mySQL + frontend:
Debian Linux
Dual Xeon 2,8 4G RAM
2 x 35GB Raid 1 IBM harddisks
apache 2.0.55, PHP 4.4.0 Apache SAPI, Zend Optimizer, eAccelerator
mySQL 5.0.18 self compiled with ICC

dedicated frontend:
Debian Linux
Dual Xeon 2,4 2G RAM
2 x 32GB Raid 1 Maxtor harddisks
apache 2.0.55, PHP 4.4.0 Apache SAPI, Zend Optimizer, eAccelerator

There is no chance to run the frontend on a single host, it's overloaded.

3.
Only MyISAM tables

4.
export CXX=/opt/intel/cc/9.0/bin/icpc
export LDFLAGS="-I/opt/intel/cc/9.0/lib"
export CFLAGS="-mcpu=pentium4 -mtune=pentium4 -march=pentium4 -msse2 -axN -I/opt/intel/cc/9.0/include"

/configure --enable-assembler --prefix=/usr/local/mysql5-icc --with-berkeley-db --with-big-tables --without-innodb

other things aren't intersting on that point. The forums are heavily overloaded on that point, so I would need some upgrade scenarios. As far as I know currently is only mySQL supported right? Therefore I see just 3 solutions

1. A big dedicated mysql machine e.g. a 8 core Sun machine for 15k-20k bucks or something like that
2. mySQL Replication. I think the best solution
3. mySQL Cluster. On the paper the best thing. But I must say, I already used a mySQL NDB Cluster with an other project (2 million visitors, 15.000 queries per second) having 3 nodes (3 x Dual Opteron 6G RAM). It didn't work well. mySQL NDB is slow, a single query takes about 3 times so long than on a single host (no, not a configuration matter). Indeed mySQL NDB is for high availability not high performance, then it seems extremly unstable. The entire cluster crashes with reasons like "Unknown error, send bug report" and so on, I can't use such a piece of software in a professional environment.

Do you have any suggestions?

[eva2000]

first need all info asked at http://www.vbulletin.com/forum/showthread.php?t=70117 particularly question #6 and 7 and mysqlreport output on db server, should be enough info to ascertain what your current loads are really

[onkelchen]

Uhm why?
I know I have no chance still to tune my current configuration. I know I need new hardware, I'm just unsure for what (NDB/Replication/a single powerful machine).

No I don't know why you should need theese stats, however:


+---------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 150 |
| basedir | /usr/local/mysql5-icc/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 256000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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/local/mysql5-icc/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| 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 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 3 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | NO |
| 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 | |
| interactive_timeout | 28800 |
| join_buffer_size | 5238784 |
| key_buffer_size | 268435456 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql5-icc/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 1000 |
| max_connections | 950 |
| 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_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 65536 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4760 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/foo.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 4190208 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 3145720 |
| 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 | 10 |
| system_time_zone | CET |
| table_cache | 500 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 6291456 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.18 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (December 21, 2005) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------+
+----------------------------+-------------+
| Variable_name | Value |
+----------------------------+-------------+
| Aborted_clients | 1698 |
| Aborted_connects | 5 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1729382002 |
| Bytes_sent | 3757956871 |
| 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 | 58580848 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 17 |
| Com_dealloc_sql | 0 |
| Com_delete | 1272238 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 13953490 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 115 |
| 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 | 1215218 |
| 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 | 512109538 |
| Com_set_option | 6657 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 19 |
| Com_show_charsets | 213 |
| Com_show_collations | 213 |
| Com_show_column_types | 0 |
| Com_show_create_db | 120 |
| Com_show_create_table | 5638 |
| Com_show_databases | 67 |
| Com_show_errors | 0 |
| Com_show_fields | 6107 |
| Com_show_grants | 72 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 83 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 5888 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5892 |
| Com_show_storage_engines | 1 |
| Com_show_tables | 1878 |
| Com_show_triggers | 5582 |
| Com_show_variables | 641 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 115 |
| Com_update | 29029259 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 58580662 |
| Created_tmp_disk_tables | 159897 |
| Created_tmp_files | 113019 |
| Created_tmp_tables | 3074989 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5338841 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 52353166 |
| Handler_read_key | 3483105450 |
| Handler_read_next | 314061899 |
| Handler_read_prev | 1076951191 |
| Handler_read_rnd | 155161094 |
| Handler_read_rnd_next | 242941617 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 34368615 |
| Handler_write | 27388689 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 8626 |
| Key_blocks_used | 231960 |
| Key_read_requests | 43525943802 |
| Key_reads | 175286332 |
| Key_write_requests | 36186804 |
| Key_writes | 28517478 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 348 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 520 |
| Open_streams | 0 |
| Open_tables | 500 |
| Opened_tables | 6837452 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 674785217 |
| Rpl_status | NULL |
| Select_full_join | 8385 |
| Select_full_range_join | 0 |
| Select_range | 136474819 |
| Select_range_check | 0 |
| Select_scan | 54044095 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 3 |
| Slow_queries | 27377 |
| Sort_merge_passes | 56539 |
| Sort_range | 48896770 |
| Sort_rows | 2986871680 |
| Sort_scan | 4747223 |
| Table_locks_immediate | 875881439 |
| Table_locks_waited | 15236200 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 214 |
| Threads_created | 58580661 |
| Threads_running | 150 |
| Uptime | 3170739 |
+----------------------------+-------------+
Uptime: 3170739 Threads: 217 Questions: 674785226 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 500 Queries per second avg: 612.816
mysqladmin Ver 8.41 Distrib 5.0.18, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.18
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 36 days 16 hours 45 min 39 sec

Threads: 217 Questions: 674785228 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 500 Queries per second avg: 612.816



[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
bind-address=192.168.21.1
skip-locking
skip-innodb
skip-name-resolve

set-variable = back_log=150
set-variable = connect_timeout=5
set-variable = delayed_insert_limit=100
set-variable = delayed_insert_timeout=300
set-variable = delayed_queue_size=1000
set-variable = join_buffer=5M
set-variable = flush_time=0
set-variable = key_buffer=256M
set-variable = long_query_time=5
set-variable = max_allowed_packet=1M
set-variable = max_connections=950
set-variable = max_connect_errors=1000
set-variable = max_join_size=4294967295
set-variable = max_sort_length=1024
set-variable = max_write_lock_count=4294967295
set-variable = net_buffer_length=64K
set-variable = record_buffer=4M
set-variable = sort_buffer=3M
set-variable = table_cache=500
set-variable = thread_stack=128K
set-variable = tmp_table_size=6M
set-variable = wait_timeout=28800
set-variable = myisam_sort_buffer_size=64M
set-variable = ft_min_word_len=3

set-variable = query-cache-size=0
set-variable = query-cache-type=0

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


And no, don't bug me about the mySQL query cache. It sucks. I've tried it, it doesn't work on forums. The query cache flushes the whole table cache if *one* row is updated, having a lot of overhead since there are a lot of INSERTs and UPDATEs, but you should know yourself about your software.

[Dave#]

2500 people online is *alot* for Mysql

I agree replication is probably the best way to go forward the problem is getting two servers large enough to be able to cope.

I wouldn't bother with Sun hardware - I'd probably go with Dual Dual Core Opterons with 8Gb of Ram and try and get some Network storage using raid 10 for speed and resilience.

[eva2000]

strange query cache for most vB forums have provided more of a benefit than a draw back

you're overloaded just on web server right ? MySQL server is using approximately 348 mysql concurrent connections which isn't that much but you're @612 queries/second!

Any hacks or plugins installed on your vB forum ? php/mysql banner ad scripts ? persistent connections in vB and any php/mysql banner ad scripts enabled ?

maybe try this my.cnf


[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
bind-address=192.168.21.1
skip-locking
skip-innodb
skip-name-resolve
back_log = 100
max_connections = 950
key_buffer = 192M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
thread_concurrency = 4
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 32768
preload_buffer_size = 32768
key_cache_age_threshold = 300
key_cache_division_limit = 100
storage_engine = MyISAM
ft_min_word_len=3

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

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
ft_min_word_len=3
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


hardware wise you don't need sun server - you'd be better off with dual opterons read http://www.vbulletin.com/forum/showthread.php?t=160103 and maybe a hardware load balanced web frontend so you can add additional web servers as needed to service the web end load

i.e. web 2x load balanced dual opteron 250 with 4GB ram each and a dual opteron 265 or 270 with 8GB ram running 64bit flavoured linux OS like Suse Entreprise 64bit with 15k scsi raid 10 array for db server

[onkelchen]

I agree, I don't need Sun server. I just wanted to give an example of a single powerful machine, just to say an alternative to load balanced x64 hosts (has anyone experiences with Xeon EMT64 btw?).
Of course we're using some minor modifications, however none of them heavily depend on the database, even the adserver has its own machine.

Currently the mySQL is indeed the bottleneck, an overloaded frontend isn't really a problem though. Load balancing isn't that hard to realize for frontend jobs. It's much more tricky to have a powerful database behind.

I'm using persistent connections yes. I'll later try without.

Using replication I'm in doubt about the master. The whole database depends on a single host, the master. Every write to the database must be on the master, there is certainly the point where this isn't enough anymore (I read in the mySQL documentation there could be multiple master, but this section needs to be better documented ...). However according to the wikitech documentations the whole Wikipedia runs on a single master with 7-8 slaves.