BIG BIG environments problems...

Post general support questions here that do not specifically fall into the Linux or Windows categories.

Moderators: Moderators, Developers

Post Reply
Author
Message
User avatar
blackstrip
Posts: 16
Joined: Tue Sep 10, 2013 10:45 pm

BIG BIG environments problems...

#1 Post by blackstrip » Mon Sep 21, 2015 12:58 am

Hi guys,

my target:
4000+ hosts, 70 000+ data sources, 300 000+ data items
one month per-5-minutes statistics in RRA
5 minutes poller interval

now i have:
1 DB server(intel xeon e5-2625 20cpus x 2.99Ghz, 32G mem, 800GB hdd space with RAID 5)
1 NFS server for RRA store(nas host, 3TB space with RAID 5)
1 WEB server for service cacti web
10 POLLER server with Spine for poller(intel xeon x5550 4cpus x 2.67Ghz, 8G mem)

my current software architecture:
All in LAN.
10 poller server: CentOS 6.6 x86_64 + cacti0.8.8a + spine + multipoller plugins, assign RRA path to NFS server for share
1 DB server: CentOS 6.6 x86_64 + MySQL server 5.6 + mysql datadir in btrfs filesystem.

problem:
a lot empty poller data in RRA, and graph data in blank.
can everybody tell me why? is database performance problem? how to fix?
:cry:
Log below:

Code: Select all

9/17/2015 01:40:32 AM - SYSTEM STATS: Time:30.3218 Method:spine Server:hl-poller11 Processes:6 Threads:15 Hosts:200 HostsPerProcess:34 DataSources:994 RRDsProcessed:784
09/17/2015 01:45:06 AM - SPINE: Poller[15] FATAL: Connection Failed, Error:'1040', Message:'Too many connections' (Spine thread)
09/17/2015 01:45:06 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:06 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:09 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:10 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:10 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:10 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:10 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:45:11 AM - PHPSVR: Poller[0] ERROR: Input Expected, Script Server Terminating
09/17/2015 01:50:01 AM - POLLER: Poller[0] Pollerserver: hl-poller11 Maximum runtime of 298 seconds exceeded. Exiting.

User avatar
phalek
Cacti Guru User
Posts: 2711
Joined: Thu Jan 31, 2008 6:39 am
Location: Kressbronn, Germany
Contact:

Re: BIG BIG environments problems...

#2 Post by phalek » Mon Sep 21, 2015 9:08 am

Your mysql server isn't prepared for the number of connections coming in. Check your my.cnf for the max_connections setting.

Code: Select all

# vi /etc/my.cnf
max_connections = xxx
Then also look at this post:
http://forums.cacti.net/viewtopic.php?f=5&t=40262

And check your I/O on the NFS servers. It may be hitting the roof ....
Greetings,
Phalek
---
Need more help ? Read the Cacti documentation or my new Cacti 1.x Book
Need on-site support ? Look here Cacti Workshop
Need professional Cacti support ? Look here CereusService
---
Plugins : CereusTransporter | CereusReporting | nmidWebService | nmidSmokeping | nmidWeb2 |

Code: Select all

CereusServer Master:  SYSTEM STATS: Time:2.5621 Method:spine Processes:1 Threads:16 Hosts:446 HostsPerProcess:446 DataSources:14683 RRDsProcessed:7573
CereusServer Agent:   SYSTEM STATS: Time:27.4840 Method:spine Processes:1 Threads:8 Hosts:16 HostsPerProcess:16 DataSources:114576 RRDsProcessed:48061
[/size]

User avatar
blackstrip
Posts: 16
Joined: Tue Sep 10, 2013 10:45 pm

Re: BIG BIG environments problems...

#3 Post by blackstrip » Tue Sep 22, 2015 4:09 am

phalek wrote:Your mysql server isn't prepared for the number of connections coming in. Check your my.cnf for the max_connections setting.

Code: Select all

# vi /etc/my.cnf
max_connections = xxx
Then also look at this post:
http://forums.cacti.net/viewtopic.php?f=5&t=40262

And check your I/O on the NFS servers. It may be hitting the roof ....
Hi phalek,

thx for your help. RRD writting performance improvement is very helpful.
i think is my mysql architecture problem, because max_connections = 2000, already very large.
and i still don't know how to adjust. :(

my.cnf below:

Code: Select all

    [client]
    port            = 3306
    socket          = /db_data/mysql.sock

    [mysqld]
    #datadir	    = /var/lib/mysql
    datadir	    = /db_data
    #socket	    = /var/lib/mysql/mysql.sock
    socket          = /db_data/mysql.sock
    port            = 3306
    user            = mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    # applies only when running as root
    #memlock                        = 1
    # Skip reverse DNS lookup of clients
    skip-name-resolve
    
    sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    table_open_cache               = 3072
    table_definition_cache         = 4096
    max_heap_table_size            = 64M
    tmp_table_size                 = 64M
    max_connections                = 2000
    max_user_connections           = 2000
    max_allowed_packet             = 500M
    net_write_timeout              = 60
    net_read_timeout		   = 60
    thread_cache_size              = 32
    query_cache_size               = 64M
    wait_timeout = 120
    interactive_timeout = 120
    # InnoDB
    default-storage-engine	   = InnoDB

    # 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
    innodb_buffer_pool_size        = 22G

    # number of CPU cores dedicated to the MySQL InnoDB backend 
    innodb_buffer_pool_instances   = 12

    innodb_data_file_path          = ibdata1:128M:autoextend
    innodb_file_per_table          = 1
    innodb_log_file_size           = 512M
    innodb_log_files_in_group      = 2

    # MyISAM
    myisam_recover                 = backup,force

    # Logging
    log_warnings                   = 2
    log_error                      = /var/log/mysql/error.log

    slow_query_log                 = 1
    slow_query_log_file            = /var/log/mysql/mysql-slow.log
    long_query_time                = 1
    log_queries_not_using_indexes  = 1
    min_examined_row_limit         = 20

    # Binary Log / Replication
    server_id                      = 1
    log-bin                        = mysql-bin
    binlog_cache_size              = 1M 
    sync_binlog                    = 8
    binlog_format                  = row
    expire_logs_days               = 7
    max_binlog_size                = 128M 
    relay-log                      = /var/log/mysql/slave-relay.log
    relay-log-index                = /var/log/mysql/slave-relay-log.index 

    [mysqldump]
    quick
    single-transaction
    max_allowed_packet             = 16M

    [mysql]
    no_auto_rehash

    [myisamchk]
    key_buffer                     = 512M
    sort_buffer_size               = 512M
    read_buffer                    = 8M
    write_buffer                   = 8M

    [mysqld_safe]
    open-files-limit               = 8192
    log-error                      = /var/log/mysql/error.log
    #pid-file			   = /var/run/mysqld/mysqld.pid
    pid-file                      = /db_data/mysqld.pid

User avatar
phalek
Cacti Guru User
Posts: 2711
Joined: Thu Jan 31, 2008 6:39 am
Location: Kressbronn, Germany
Contact:

Re: BIG BIG environments problems...

#4 Post by phalek » Tue Sep 22, 2015 5:06 am

Change the 2000 to e.g. 3000:

Code: Select all

max_connections                = 3000
    ma_user_connections           = 3000
Then restart the mysql daemon
Greetings,
Phalek
---
Need more help ? Read the Cacti documentation or my new Cacti 1.x Book
Need on-site support ? Look here Cacti Workshop
Need professional Cacti support ? Look here CereusService
---
Plugins : CereusTransporter | CereusReporting | nmidWebService | nmidSmokeping | nmidWeb2 |

Code: Select all

CereusServer Master:  SYSTEM STATS: Time:2.5621 Method:spine Processes:1 Threads:16 Hosts:446 HostsPerProcess:446 DataSources:14683 RRDsProcessed:7573
CereusServer Agent:   SYSTEM STATS: Time:27.4840 Method:spine Processes:1 Threads:8 Hosts:16 HostsPerProcess:16 DataSources:114576 RRDsProcessed:48061
[/size]

User avatar
blackstrip
Posts: 16
Joined: Tue Sep 10, 2013 10:45 pm

Re: BIG BIG environments problems...

#5 Post by blackstrip » Wed Sep 23, 2015 10:33 pm

phalek wrote:Change the 2000 to e.g. 3000:

Code: Select all

max_connections                = 3000
    ma_user_connections           = 3000
Then restart the mysql daemon
OK, thx, i will make this test. :D

stefanbrudny
Cacti User
Posts: 102
Joined: Thu Jan 19, 2012 11:52 am

Re: BIG BIG environments problems...

#6 Post by stefanbrudny » Wed Sep 30, 2015 5:34 pm

Huh, how bieg is that... I failed on 119k nad THATS big.

watch out. Cacti spine dies, everybody here says that all is fine, whereas it is broken.

Analyze this post if you have a minute http://forums.cacti.net/viewtopic.php?f=21&t=54036

Bug was reported and resolved without even tracing
http://bugs.cacti.net/view.php?id=2536

in big setup i would separate mysql on different host. I feel that is race for resources in such scale and load.

stefanbrudny
Cacti User
Posts: 102
Joined: Thu Jan 19, 2012 11:52 am

Re: BIG BIG environments problems...

#7 Post by stefanbrudny » Wed Sep 30, 2015 5:38 pm

Actually,cacti should never be considered good for production quality,

And im saying this despite i like it much and have done some several large installs.

Alternatives however, are limited. Let me know when you find one.

Post Reply