(2016-11-16, 01:26 AM)metulburr Wrote: [ -> ]Quote:Socket errors are nothing to do with *any* of that. It's that the service just hasn't started right. Restart. Fixed.
even when it reoccurs over and over? It doesnt happen when i jsut start mysql, it repetitively occurs throughout regular use.
Really? After heavy load?
not sure i just come back to the forums and there is a mysql error. So i had to put a cron job to restart otherwise there would be down time.
(2016-11-16, 01:12 AM)VoIP Wrote: [ -> ]Actually the issue with MySQL is there is a "table_cache" that stores a ton of queries that have been generated by other users, and thus it creates less memory for the database. Hence the 2002. It's like in computers, when you restart you reset the Rams cache.
I'd suggest trying a "show status" on your mysql console.
If needed edit your my.cnf file in /etc/mysql/my.cnf
Modify the following within there: "Key_Buffer_size" and "Query_Cache_Size" This will allow more to be indexed and more to be stored in ram. If your capping on ram, try increasing your swap file.
show stats
mysql> SHOW STATUS;
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 141 |
| Bytes_sent | 185 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| 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_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| 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 | 229 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_pages_data | 836 |
| Innodb_buffer_pool_bytes_data | 13697024 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 6 |
| Innodb_buffer_pool_pages_free | 7354 |
| Innodb_buffer_pool_pages_misc | 2 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 36358 |
| Innodb_buffer_pool_reads | 837 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 10 |
| Innodb_data_fsyncs | 11 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 15896576 |
| Innodb_data_reads | 847 |
| Innodb_data_writes | 15 |
| Innodb_data_written | 200704 |
| Innodb_dblwr_pages_written | 6 |
| Innodb_dblwr_writes | 2 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 1 |
| Innodb_log_writes | 3 |
| Innodb_os_log_fsyncs | 7 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 2048 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 836 |
| Innodb_pages_written | 6 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 1 |
| Innodb_rows_read | 1451 |
| Innodb_rows_updated | 1 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13043 |
| Key_blocks_used | 353 |
| Key_read_requests | 54658 |
| Key_reads | 352 |
| Key_write_requests | 100 |
| Key_writes | 93 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 2 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 387 |
| Open_streams | 0 |
| Open_table_definitions | 318 |
| Open_tables | 321 |
| Opened_files | 955 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 31 |
| Qcache_free_memory | 15869832 |
| Qcache_hits | 337 |
| Qcache_inserts | 372 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 405 |
| Qcache_queries_in_cache | 115 |
| Qcache_total_blocks | 288 |
| Queries | 1476 |
| Questions | 2 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_heartbeat_period | 0.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 838 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 1 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
| Uptime | 209 |
| Uptime_since_flush_status | 209 |
+------------------------------------------+-------------+
312 rows in set (0.01 sec)
my current config for my.cnf is
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
and i dont see a Key_Buffer_size, but i do see two entries of key_buffer
Quote:E486: Pattern not found: Key_Buffer
# * Fine Tuning
#
key_buffer = 16M
[isamchk]
key_buffer = 16M
also if/when i reset these, do i need to reset mysql to take effect?
mysql> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "my_db_name" ORDER BY (data_length + index_length) DESC;
+---------------------------------+-----------+
| Table | Size (MB) |
+---------------------------------+-----------+
| mybb_posts | 5.54 |
| mybb_edithistory | 5.52 |
| mybb_spamlog | 1.57 |
| mybb_privatemessages | 1.03 |
| mybb_templates | 0.80 |
| mybb_tasklog | 0.55 |
| mybb_threadsread_users | 0.33 |
| mybb_threads | 0.31 |
| mybb_adminlog | 0.29 |
| mybb_proactivity | 0.26 |
| mybb_users | 0.21 |
| mybb_sessions | 0.19 |
| mybb_datacache | 0.17 |
| mybb_google_seo | 0.16 |
| mybb_themestylesheets | 0.15 |
| mybb_settings | 0.14 |
| mybb_post_likes | 0.09 |
| mybb_backup_posts | 0.09 |
| mybb_threadsread | 0.08 |
| mybb_moderatorlog | 0.08 |
| mybb_searchlog | 0.07 |
| mybb_threadsubscriptions | 0.07 |
| mybb_mailerrors | 0.06 |
| mybb_tapatalk_push_data | 0.06 |
| mybb_helpdocs | 0.06 |
| mybb_forumsread | 0.05 |
| mybb_dvz_shoutbox | 0.05 |
| mybb_userfields | 0.02 |
| mybb_backup_threads | 0.02 |
| mybb_tapatalk_users | 0.02 |
| mybb_attachments | 0.01 |
| mybb_forums | 0.01 |
| mybb_myprofilevisitors | 0.01 |
| mybb_reputation | 0.01 |
| mybb_pollvotes | 0.01 |
| mybb_settinggroups | 0.01 |
| mybb_announcements | 0.01 |
| mybb_themes | 0.01 |
| mybb_questionsessions | 0.01 |
| mybb_warnings | 0.01 |
| mybb_polls | 0.01 |
| mybb_pages | 0.01 |
| mybb_adminoptions | 0.01 |
| mybb_modtools | 0.01 |
| mybb_smilies | 0.01 |
| mybb_reportedcontent | 0.01 |
| mybb_forumpermissions | 0.00 |
| mybb_promotionlogs | 0.00 |
| mybb_usergroups | 0.00 |
| mybb_myprofilecomments | 0.00 |
| mybb_maillogs | 0.00 |
| mybb_attachtypes | 0.00 |
| mybb_backup_threadsubscriptions | 0.00 |
| mybb_buddyrequests | 0.00 |
| mybb_tasks | 0.00 |
| mybb_templategroups | 0.00 |
| mybb_mycode | 0.00 |
| mybb_badwords | 0.00 |
| mybb_threadratings | 0.00 |
| mybb_mailqueue | 0.00 |
| mybb_banfilters | 0.00 |
| mybb_stats | 0.00 |
| mybb_forumsubscriptions | 0.00 |
| mybb_adminsessions | 0.00 |
| mybb_favorites | 0.00 |
| mybb_questions | 0.00 |
| mybb_usertitles | 0.00 |
| mybb_profilefields | 0.00 |
| mybb_spiders | 0.00 |
| mybb_awaitingactivation | 0.00 |
| mybb_threadprefixes | 0.00 |
| mybb_icons | 0.00 |
| mybb_promotions | 0.00 |
| mybb_warningtypes | 0.00 |
| mybb_helpsections | 0.00 |
| mybb_adminviews | 0.00 |
| mybb_extended_polls | 0.00 |
| mybb_mybblog_articles | 0.00 |
| mybb_mybblog_tags | 0.00 |
| mybb_templatesets | 0.00 |
| mybb_calendars | 0.00 |
| mybb_mybblog_comments | 0.00 |
| mybb_backup_pollvotes | 0.00 |
| mybb_extended_pollvotes | 0.00 |
| mybb_backup_polls | 0.00 |
| mybb_moderators | 0.00 |
| mybb_events | 0.00 |
| mybb_warninglevels | 0.00 |
| mybb_backup_attachments | 0.00 |
| mybb_massemails | 0.00 |
| mybb_delayedmoderation | 0.00 |
| mybb_joinrequests | 0.00 |
| mybb_captcha | 0.00 |
| mybb_threadviews | 0.00 |
| mybb_calendarpermissions | 0.00 |
| mybb_groupleaders | 0.00 |
| mybb_banned | 0.00 |
+---------------------------------+-----------+
97 rows in set (0.02 sec)
My VPS has 1GB RAM, and 30GB HArd disk space, and the whole purpose of this server is to run this forum only. Does that mean i should bump up the MB to 1024?
like i just got this error
[
attachment=37797]
For doing a search, then when i refresh its fixed. I have this happen quite often. I get it so much (As well as other users) that i had to change class_error to give more info to refresh and contact info if it doesnt go away
It's beyond the scope of our support really, you'd be better off asking Digital Ocean. We're not sysadmins and MySQL configs aren't really our thing.
Quote:It's beyond the scope of our support really, you'd be better off asking Digital Ocean. We're not sysadmins and MySQL configs aren't really our thing.
thanks, i appreciate the update then.
Im currently pursuing the possibility of running out of RAM on my VPS and mysql going beserk as a result of it.
In case someone else is having the same issues....
I made a swap 2x the size of the RAM as well as inflating some values in my.cnf. But i am still awaiting to see if mysql errors shows up or not. Its hard to debug as its random when mysql errors occur, and a refresh fixes them it seems.
UPDATE: nope guess that was not the issue.