ZABBIX Forums  
  #41  
Old 10-10-2014, 16:28
pzabortsev pzabortsev is offline
Senior Member
 
Join Date: Dec 2012
Location: Moscow
Posts: 333
Default

Quote:
Originally Posted by benjamin_regnier View Post
Currently I have a 250 GB database with no slowquery
Could you tell us which hardware do you use for mysql?

and which mysql version and configuration parameters do you use?
Reply With Quote
  #42  
Old 23-03-2015, 22:57
Cardplugin Cardplugin is offline
Junior Member
 
Join Date: Mar 2015
Location: Canada
Posts: 1
Default

Finally I could figure out the problem.

Thanks a lot
Reply With Quote
  #43  
Old 16-03-2017, 12:54
vlam vlam is offline
Senior Member
Zabbix certified specialist
 
Join Date: Jun 2009
Location: South Africa
Posts: 134
Send a message via Skype™ to vlam
Default

I have an similar DB issue where I need to backup my environment but cannot have any downtime. My HA is broken so I need the backup to get that sorted also.

My problem is I have an 1.5TB DB running at this stage

Any help in how to do this with minimal (preferable NO) downtime.
__________________
4 Zabbix Frontend Servers (Load balanced)
2 Zabbix App Servers (HA)
2 Zabbix Database Servers (HA)
18 Zabbix Proxy Servers (HA)
3897 Deployed Zabbix Agents
6161 Values per second
X-Layer Integration
Jaspersoft report Servers (HA)
Reply With Quote
  #44  
Old 06-12-2017, 17:07
pnenov pnenov is offline
Junior Member
 
Join Date: Dec 2017
Posts: 6
Default MySQl schema

the main reason for poor Zabbix performance is the table items
that table is used virtually in each and every mysql query

items | CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) NOT NULL DEFAULT '',
`snmp_oid` varchar(255) NOT NULL DEFAULT '',
`hostid` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`key_` varchar(255) NOT NULL DEFAULT '',
`delay` int(11) NOT NULL DEFAULT '0',
`history` int(11) NOT NULL DEFAULT '90',
`trends` int(11) NOT NULL DEFAULT '365',
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) NOT NULL DEFAULT '',
`units` varchar(255) NOT NULL DEFAULT '',
`multiplier` int(11) NOT NULL DEFAULT '0',
`delta` int(11) NOT NULL DEFAULT '0',
`snmpv3_securityname` varchar(64) NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) NOT NULL DEFAULT '',
`formula` varchar(255) NOT NULL DEFAULT '',
`error` varchar(2048) NOT NULL DEFAULT '',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`valuemapid` bigint(20) unsigned DEFAULT NULL,
`delay_flex` varchar(255) NOT NULL DEFAULT '',
`params` text NOT NULL,
`ipmi_sensor` varchar(128) NOT NULL DEFAULT '',
`data_type` int(11) NOT NULL DEFAULT '0',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) NOT NULL DEFAULT '',
`password` varchar(64) NOT NULL DEFAULT '',
`publickey` varchar(64) NOT NULL DEFAULT '',
`privatekey` varchar(64) NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
`interfaceid` bigint(20) unsigned DEFAULT NULL,
`port` varchar(64) NOT NULL DEFAULT '',
`description` text NOT NULL,
`inventory_link` int(11) NOT NULL DEFAULT '0',
`lifetime` varchar(64) NOT NULL DEFAULT '30',
`snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',
`snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`snmpv3_contextname` varchar(255) NOT NULL DEFAULT '',
`evaltype` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`),
KEY `items_5` (`valuemapid`),
KEY `items_6` (`interfaceid`),
CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),
CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


the statement:

UNIQUE KEY `items_1` (`hostid`,`key_`)

makes impossible this table to be partitioned
second the unique index can not be cached
third the key_ column contents strings which in some occasions are might be very large, including such a column in a unique index (or any other index) is not exactly a very good idea

this table is not easy to be redesigned and as long as it stays this way the performance of zabbix will deteriorate severely in exponential progression regarding the number of records in table items
Reply With Quote
  #45  
Old 07-12-2017, 09:22
aaditya aaditya is offline
Junior Member
 
Join Date: Dec 2017
Posts: 1
Default For reply this

In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it
Reply With Quote
  #46  
Old 08-12-2017, 15:58
pnenov pnenov is offline
Junior Member
 
Join Date: Dec 2017
Posts: 6
Default

Quote:
Originally Posted by aaditya View Post
In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it

indeed, only small companies are using one single database server

Zabbix's schema is lacking enterprise capabilities

I am using galera cluster to host Zabbix' database and i cant take benefit from distributed resources and multithreading capabilities of my cluster because of the way Zabbix schema has been designed - it is virtually impossible to partitioning most of the tables
Many people are using multi disks partitioning approach too and they face the same issue i have

it seems to me the Zabbix's team prefer to cover the needs of the small companies and single users rather to redesign Zabbix in a way to be able to fit the enterprise architecture
The changes they make in the new releases are mostly on the web front-end
Reply With Quote
Reply

Tags
fsfgsfgsgf

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 02:27.