Ad blocker detected: Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker on our website.
Post general support questions here that do not specifically fall into the Linux or Windows categories.
Moderators: Developers , Moderators
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Tue Oct 31, 2006 3:03 pm
Please scroll to the bottom, thanks !
I tried to log into Cacti for the first time in a couple of hours, and it took me to that screen which looks like I had no database.
So, I fired up Webmin and could not view any of my databases. I got this error:
Code: Select all
DBI connect failed : Too many connections
So, I enabled "large tables" and set the max connections to 300, instead of the default 100. I had this issue before, but I figured that since I was using the same login credentials for Syslog as I was for the Cacti DB, that was the problem. I now have two accounts for that, but I got this error again out of nowhere.
Seems to be working fine, now. I was wondering what the recommended "max connections" should be, since I do not want this to happen when it is in usage with the IT/MIS departments. I have this in production mode right now.
Thank you!
Last edited by
harner on Mon Nov 06, 2006 11:15 am, edited 1 time in total.
rony
Developer/Forum Admin
Posts: 6022 Joined: Mon Nov 17, 2003 6:35 pmLocation: Michigan, USA
Contact:
Post
by rony Tue Oct 31, 2006 3:27 pm
Well, one account can and will have multiple connections. Unless connection pooling is being done with apache for the mysql connections, every end web user will generate 1 connection.
[size=117][i][b]Tony Roman[/b][/i][/size]
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Tue Oct 31, 2006 3:35 pm
Okay, thanks for the information, Tony.
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Wed Nov 01, 2006 8:47 am
I checked out Cacti this morning and it gave me that screen where it wants me to add a database again. I checked out the databases via Webmin and phpMyAdmin. I got this error in Webmin:
Code: Select all
DBI connect failed : Too many connections
phpMyAdmin:
Last night I set the max connections setting to 500. I just set it to 1000 and restarted the server, and it still says too many connections!
If I keep probing at them, eventually I can see the tables in the databases. Any suggestions?
After running
mysqladmin -u root -p var ext stat ver I got this:
Code: Select all
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| 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 |
| 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 | 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_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| 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_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| 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_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/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 | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| 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_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/mysqld.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 | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 258048 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /var/lib/mysql/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 524280 |
| 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 | EST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 262144 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.18 |
| version_comment | SUSE MySQL RPM |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+----------------------------+
+-----------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------+-------------+
| Aborted_clients | 118 |
| Aborted_connects | 5737 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 33591609653 |
| Bytes_sent | 38206769867 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1121 |
| 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 | 249 |
| Com_dealloc_sql | 0 |
| Com_delete | 318423 |
| 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 | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 321019 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 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_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 842 |
| 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 | 667898964 |
| Com_set_option | 60 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 2 |
| Com_show_charsets | 15 |
| Com_show_collations | 15 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1 |
| Com_show_databases | 33 |
| Com_show_errors | 0 |
| Com_show_fields | 499 |
| Com_show_grants | 3 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 112 |
| 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 | 2 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 14 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 411 |
| Com_show_triggers | 0 |
| Com_show_variables | 32 |
| 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 | 236 |
| Com_unlock_tables | 0 |
| Com_update | 11282 |
| 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 | 7058 |
| Created_tmp_disk_tables | 569 |
| Created_tmp_files | 21 |
| Created_tmp_tables | 1744 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 343249 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 43031 |
| Handler_read_key | 1489112 |
| Handler_read_next | 1515189 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 324926 |
| Handler_read_rnd_next | 4876956 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 11913 |
| Handler_write | 837320 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| 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 | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13271 |
| Key_blocks_used | 682 |
| Key_read_requests | 9086593 |
| Key_reads | 5517 |
| Key_write_requests | 762573 |
| Key_writes | 690566 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 101 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 122 |
| Open_streams | 0 |
| Open_tables | 62 |
| Opened_tables | 1603 |
| 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 | 668554484 |
| Rpl_status | NULL |
| Select_full_join | 170 |
| Select_full_range_join | 0 |
| Select_range | 142 |
| Select_range_check | 0 |
| Select_scan | 15070 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 14 |
| Slow_queries | 280 |
| Sort_merge_passes | 8 |
| Sort_range | 1144 |
| Sort_rows | 354246 |
| Sort_scan | 829 |
| Table_locks_immediate | 1481644 |
| Table_locks_waited | 3478 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 100 |
| Threads_created | 7057 |
| Threads_running | 75 |
| Uptime | 64047 |
+-----------------------------------+-------------+
Uptime: 64047 Threads: 100 Questions: 668556255 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 62 Queries per second avg: 10438.526
mysqladmin Ver 8.41 Distrib 5.0.18, for suse-linux on x86_64
streaker69
Cacti Pro User
Posts: 712 Joined: Mon Mar 27, 2006 10:35 amLocation: Psychic Amish Network Administrator
Post
by streaker69 Wed Nov 01, 2006 9:51 am
I'm not a MySQL pro, but it would seem that you have the Max_User_Connections set to 0 while you're attempting to connect 101 times.
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Wed Nov 01, 2006 11:43 am
I can't find where to set the max_user_connections value so I set the users to Superusers. It seems to be working...for now.
gandalf
Developer
Posts: 22383 Joined: Thu Dec 02, 2004 2:46 amLocation: Muenster, Germany
Contact:
Post
by gandalf Wed Nov 01, 2006 1:01 pm
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Wed Nov 01, 2006 3:43 pm
I did see this page earlier, very resourceful. This addresses the "max_connections" value, which I did edit. However, I'm concerned now with the max_users_connections value.
Also, the page refers to
as a way to just add another connection if need be, but the users have to be in Superuser mode. I made my users superusers in MySQL, now I want to add this script in.
Code: Select all
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
set-variable = max_connections=500
So, could I just replace it like this:
set-variable = max_connections+1
And then be on my way? The MySQL guide is great, but the examples (or lack there of) are not that great and they don't go too far into depth.
By the way, anyone else have this problem? Thanks to all!
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Thu Nov 02, 2006 9:30 am
So, I've been trying things off and on, and I have max_connections+1=500 right now. Still doesn't work. In the command line I tried this and got:
Code: Select all
chpas-cacti:~ # mysql cacti
ERROR 1040 (00000): Too many connections
Is there some kind of hidden configuration file somewhere that I can modify? This is starting to get old! Thanks.
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Mon Nov 06, 2006 11:05 am
* U P D A T E *
Attachments
mysql service not running, but poller is @ 100% snapshot3.png (290.5 KiB) Viewed 4090 times
while running mysql and poller snapshot2.png (291.33 KiB) Viewed 4092 times
rony
Developer/Forum Admin
Posts: 6022 Joined: Mon Nov 17, 2003 6:35 pmLocation: Michigan, USA
Contact:
Post
by rony Mon Nov 06, 2006 12:04 pm
That is normal behavior.
[size=117][i][b]Tony Roman[/b][/i][/size]
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Mon Nov 06, 2006 12:15 pm
100% cpu utilization is a bit high, isn't it?
rony
Developer/Forum Admin
Posts: 6022 Joined: Mon Nov 17, 2003 6:35 pmLocation: Michigan, USA
Contact:
Post
by rony Mon Nov 06, 2006 12:31 pm
During a polling cycle... Nope...
[size=117][i][b]Tony Roman[/b][/i][/size]
harner
Cacti User
Posts: 75 Joined: Tue Oct 17, 2006 10:21 amLocation: PA, USA
Post
by harner Mon Nov 06, 2006 12:34 pm
Okay, cool. I wonder if since it's a dual core if it is only using 100% of one core or both.
gandalf
Developer
Posts: 22383 Joined: Thu Dec 02, 2004 2:46 amLocation: Muenster, Germany
Contact:
Post
by gandalf Mon Nov 06, 2006 2:17 pm
harner wrote: Okay, cool. I wonder if since it's a dual core if it is only using 100% of one core or both.
For a cactid poller, using 2 or more processes, it should not be _that_ difficult to get all processors to work for them ...
Reinhard
Users browsing this forum: No registered users and 1 guest
