下载文档原格式
数据库性能监控与调试工具推荐数据库在现代应用中扮演着至关重要的角色，它们存储着关键的数据，支持着应用程序的正常运行。
然而，数据库的性能问题可能会导致应用程序的运行速度变慢，甚至发生故障。
为了解决这些问题，数据库性能监控与调试工具成为了不可或缺的工具。
本文将向大家推荐几款经典的数据库性能监控与调试工具，并对其特点和优缺点进行分析。
1. MySQL Performance MonitorMySQL Performance Monitor是一款用于监控和优化MySQL数据库性能的专业工具。
它提供了全面的性能监控和调试功能，通过对数据库的各项指标进行实时监测，帮助用户快速定位和解决性能问题。
该工具可以提供实时监测图表、警报通知、查询分析等功能，帮助用户全面了解数据库的运行状态和性能瓶颈。
优点：提供全面的性能监控与调试功能，易于使用；支持实时监测图表和警报通知，方便用户及时发现和解决问题；具有强大的查询分析功能，可深入分析数据库的性能问题。
缺点：该工具只适用于MySQL数据库，不支持其他类型的数据库；商业版的功能较为强大，免费版功能相对有限。
2. Oracle Enterprise ManagerOracle Enterprise Manager是一款适用于Oracle数据库的全面性能监控和调试工具。
它可以监控和管理Oracle数据库实例、表空间、性能指标等，并提供了丰富的报表、警报和分析功能，帮助用户全面了解数据库的健康状况和性能瓶颈。
此外，Oracle Enterprise Manager还提供了数据库的自动管理功能，可以根据实际情况自动调整数据库的参数配置，提升数据库的性能表现。
优点：适用于Oracle数据库，提供全面的性能监控和调试功能；具有丰富的报表和警报功能，方便用户进行数据库性能分析；支持数据库的自动管理功能，提供智能化的性能优化策略。
缺点：该工具只适用于Oracle数据库，不支持其他类型的数据库；商业版功能较为强大，免费版功能相对有限。
iblinkinfo initialize状态-回复IBLinkInfo是一种用于初始化的状态。
这个状态在计算机科学领域中非常重要，因为它是在启动或重启计算机时执行的一个关键过程。
在本文中，我们将探讨IBLinkInfo的初始化状态，并一步一步回答与这个主题相关的问题。
首先，让我们了解一下IBLinkInfo是什么。
IBLinkInfo是InfiniBand连接的信息。
InfiniBand是一种高速网络技术，通常用于数据中心和超级计算机之间的通信。
IBLinkInfo记录了每个连接的详细信息，例如端口号、速率、状态等。
在计算机启动时，操作系统会初始化IBLinkInfo状态。
这个过程通常包括以下步骤：1. 检测硬件：首先，操作系统会检测计算机上的InfiniBand适配器和端口。
它会查找适配器是否已正确安装，并确定每个适配器上的端口数量。
2. 配置驱动程序：一旦硬件检测完毕，操作系统会加载并配置与InfiniBand适配器和端口通信的驱动程序。
这些驱动程序负责与硬件通信，并提供操作系统和应用程序使用InfiniBand技术的接口。
3. 发现和识别：初始化过程将包括对连接的发现和识别。
操作系统会发送特定的命令以获取连接的信息。
适配器会响应这些命令，并将连接的详细信息返回给操作系统。
这些信息包括连接的速率、协议版本、链路状态等。
4. 链路训练：在获取连接信息后，操作系统会与每个连接进行链路训练。
链路训练是一种校准过程，用于调整连接的参数以确保高性能和可靠性。
训练的过程包括发送和接收比特，以测试链路的稳定性和可用性。
5. 错误检测和纠正：在完成链路训练后，操作系统会对连接进行错误检测和纠正。
这个过程包括使用纠错码来检测和修复任何传输错误。
如果检测到错误，操作系统将尝试重新发送数据，直到没有错误为止。
6. 启用连接：最后，操作系统将启用连接，使其可以被应用程序使用。
在启用连接之前，操作系统可能需要进行额外的配置和调整，以确保连接的性能和稳定性。
数据库驱动程序的性能对比与选择建议现如今，数据库在各个行业和领域中都扮演着重要的角色。
而数据库驱动程序作为连接数据库和应用程序的重要组成部分，对系统的性能和稳定性起着至关重要的作用。
在选择数据库驱动程序时，我们需要考虑多个因素，其中性能是其中重要的一个方面。
本文将着重对数据库驱动程序的性能进行对比和分析，并为读者提供选择建议。
一、性能对比在进行数据库驱动程序的性能对比之前，首先需要了解数据库驱动的类型。
常见的数据库驱动有JDBC驱动、ODBC驱动、驱动等。
本文将以JDBC驱动为例进行对比。
1. 连接池性能连接池是应对高并发场景下数据库连接请求过多导致系统崩溃的常见解决方案。
而不同的数据库驱动程序对连接池的支持程度和性能表现有所不同。
一般而言，JDBC驱动程序的连接池性能较为出色，其具有较高的连接复用和性能优化水平。
2. 执行效率数据库驱动程序的执行效率对整个系统的性能有着明显的影响。
不同的数据库驱动程序在执行SQL语句时的表现也差异较大。
一般而言，JDBC驱动程序的执行效率较高，尤其是对于复杂的查询语句和大批量数据操作。
3. 并发处理能力在高并发的场景下，数据库驱动程序的并发处理能力成为一个关键指标。
较为成熟的JDBC驱动程序一般具有较高的并发处理能力，能够有效处理并发请求并保证系统的性能稳定。
4. 扩展性与适配性数据库平台的选择多种多样，而好的数据库驱动程序应该具备较高的扩展性和适配性，能够适应不同的数据库平台，并提供良好的兼容性和灵活性。
二、选择建议选择合适的数据库驱动程序对于系统的性能和稳定性至关重要。
以下是在选择数据库驱动程序时的一些建议：1. 考虑数据库本身的特性数据库的类型、版本和特性会对数据库驱动程序的选择产生直接影响。
在选择数据库驱动程序时，需要确保该驱动程序能够完全兼容目标数据库，并且具备针对目标数据库的优化策略。
2. 考虑应用场景不同的应用场景对数据库驱动程序的性能需求也不同。
infobright: 基于mysql的数据仓库综述摘要：infobright 是基于mysql的，但不装mysql亦可，因为它本身就自带了一个。
mysql可以粗分为逻辑层和物理存储引擎，infobright主要实现的就是一个存储引擎，但因为它自身存储逻辑跟关系型数据库根本不同，所以，它不能像InnoDB那样直接作为插件挂接到mysql，它的逻辑层是mysql的逻辑层加上它自身的优化器。
关键词：infobright；MySql整合；数据仓库；高数据压缩比一、技术概况Infobright进行复杂的查询时，通过其专利的知识网格（Knowledge Grid）和综合优化技术，能够实现优异的性能表现。
数据在导入Infobright之后将被高度压缩并以“数据块（Data Pack）”的形式存储，于此同时，知识网格会自动创建一个非常紧凑的元数据，其中包含了统计数据和数据块之间的关系信息。
因此当接收到查询时，Infobright查询优化器可以通过元数据智能的决定哪些数据块与查询请求相关，并将之解压。
基于知识网格技术，Infobright不需要专门对数据进行划分，也不需要建立索引，从而节省查询处理时间，提升响应速度。
基于列的数据架构 Infobright实施、管理非常简便，不需要人工调试、数据分区和创建索引，有效的降低了管理成本，此外Infobright不需要运行在特殊的专用硬件上，同时它的数据压缩比很高（10:1—40:1），比任何其他数据仓库系统对存储设备的占用都小。
这些特性让Infobright为企业IT部门降低了预算上的负担。
二、功能Infobright 企业版——分析型高性能数据仓库在随时在线的今天，需要进行业务分析的数据规模惊人增长。
商业决策需依托海量数据的实时访问，企业才能保证正确方向。
Infobright是一个基于独特的专利知识网格技术的列式数据库，能够降低您90%的管理工作量。
使用Infobright不需要创建特殊的数据库模式，无需创建和维护索引，无需对数据进行分区，甚至不需要手动调整，知识网格就会在原始数据导入时，自动创建和维护数据，并用以优化每一个查询。
BFD技术白皮书BFD技术白皮书华为技术有限公司Huawei Technologies Co., Ltd.目录1前言 (2)2技术介绍 (4)2.1协议概述 (4)2.2报文格式 (4)2.3检测模式 (7)2.4发送周期及检测时间 (9)2.5参数修改 (10)2.6会话建立 (11)2.6.1会话初始化过程 (11)2.6.2会话建立过程 (13)3BFD的标准化 (16)4典型应用 (17)4.1应用于快速重路由 (17)4.2应用于媒体网关与核心网的可靠连接 (19)5结束语 (20)附录A 参考资料 (21)附录B 缩略语 (21)BFD技术白皮书摘要：BFD（双向转发检测）是一套用来实现快速检测的国际标准协议，提供一种轻负荷、持续时间短的检测。
与以往的其他”HELLO”检测机制相比，具有许多独到的优势。
华为公司已经在数通产品上实现了BFD技术，并提供整套解决方案。
关键词：BFD、快速检测1 前言网络设备一个越来越重要的特征是，要求对相邻系统之间通信故障进行快速检测，这样在出现故障时可以更快的建立起替代通道或倒换到其他链路。
目前，一些硬件如SDH等可以提供这个功能，但是对于很多硬件或者软件无法提供这个功能，比如以太网，还有一些无法实现路径检测，比如转发引擎或者接口等，无法实现端到端的检测，在目前的网络一般采用慢Hello机制，尤其在路由协议中，在没有硬件帮助下，检测时间会很长（例如：OSPF需要2秒的检测时间，ISIS需要1秒的检测时间），这对一些电信级业务来说时间太长了，当数据速率到吉比特时，缺陷感应时间长代表着大量数据的丢失，并且对于不允许路由协议的节点没有办法检测到链路的状态。
同时，在现有的IP网络中不具备秒以下的间歇性故障修复功能，而传统路由架构在对实时应用（如语音）进行准确故障检测方面能力有限，伴随着VoIP应用的激增，实现快速网络故障检测和修复越发显得必要。
BFD协议的出现，为上述问题提出了一种解决方案，BFD能够在系统之间的任何类型通道上进行故障检测，这些通道包括直接的物理链路，虚电路，隧道，MPLS LSP，多跳路由通道，以及非直接的通道。
initdb 参数initdb命令是MySQL数据库中的一个重要命令，主要用于初始化数据库。
在日常数据库管理中，我们需要熟练掌握initdb命令的各种参数，以便在实际场景中发挥其作用。
本文将详细介绍initdb命令的参数用法和实战应用。
1.概述initdb命令的作用initdb命令的主要作用是对数据库进行初始化，包括创建数据库、创建表、设置权限等。
在MySQL数据库安装完成后，需要使用initdb命令来创建初始数据库。
2.解析initdb命令的主要参数initdb命令的语法如下：```initdb [options]```其中，options为可选参数，下面详细介绍一些常用的参数：（1）--default-authentication-plugin作用：指定默认的身份验证插件。
用法：--default-authentication-plugin=plugin_name（2）--auth-plugin作用：指定用于身份验证的插件。
用法：--auth-plugin=plugin_name（3）--basedir作用：指定MySQL安装目录。
用法：--basedir=directory（4）--datadir作用：指定数据目录。
用法：--datadir=directory（5）--default-authentication-plugin作用：指定默认的身份验证插件。
用法：--default-authentication-plugin=plugin_name （6）--encrypt作用：启用加密功能。
用法：--encrypt（7）--exclude-file作用：排除特定文件。
用法：--exclude-file=file_name（8）--innodb作用：启用InnoDB存储引擎。
用法：--innodb（9）--large-prefix作用：启用大前缀功能。
用法：--large-prefix（10）--no-root-password作用：不设置root用户密码。
lightdbforpostgresql⽇志详解 log_min_messages：控制服务器⽇志级别，总控参数，log_min_error_statement要⼤于等于log_min_messages时，SQL语句才会被记录（默认ERROR，⾜够）。
默认是WARNING，每个级别的定义如下： log_min_duration_statement：本质上的作⽤是记录慢⽇志，记录信息少，建议使⽤（LightDB默认采⽤此）。
log_duration则是为每个SQL语句记录执⾏时长，⾮常的耗性能，如果客户端使⽤扩展查询协议，则会记录解析、绑定、执⾏三个阶段的时间。
所以，⼀般⽣产应该开log_min_duration_statement，关log_duration。
log_error_verbosity：log_min_messages控制了级别，log_error_verbosity控制对于每个要记录的⽇志，记录的详细程度，例如上下⽂、参数等，取值为TERSE, DEFAULT, 和VERBOSE。
TERSE⽐DEFAULT少DETAIL, HINT, QUERY, CONTEXT，VERBOSE记录了SQLSTATE错误码以及记录⽇志的函数、c源⽂件名、以及⾏号，类似LOG4J。
其实现在elog.c send_message_to_server_log⽅法中，详细的错误⽇志结构体为struct ErrorData。
如下：/** ErrorData holds the data accumulated during any one ereport() cycle.* Any non-NULL pointers must point to palloc'd data.* (The const pointers are an exception; we assume they point at non-freeable* constant strings.)*/typedef struct ErrorData{int elevel; /* error level */bool output_to_server; /* will report to server log? */bool output_to_client; /* will report to client? */bool show_funcname; /* true to force funcname inclusion */bool hide_stmt; /* true to prevent STATEMENT: inclusion */bool hide_ctx; /* true to prevent CONTEXT: inclusion */const char *filename; /* __FILE__ of ereport() call */int lineno; /* __LINE__ of ereport() call */const char *funcname; /* __func__ of ereport() call */const char *domain; /* message domain */const char *context_domain; /* message domain for context message */int sqlerrcode; /* encoded ERRSTATE */char *message; /* primary error message (translated) */char *detail; /* detail error message */char *detail_log; /* detail error message for server log only */char *hint; /* hint message */char *context; /* context message */char *backtrace; /* backtrace */const char *message_id; /* primary message's id (original string) */char *schema_name; /* name of schema */char *table_name; /* name of table */char *column_name; /* name of column */char *datatype_name; /* name of datatype */char *constraint_name; /* name of constraint */int cursorpos; /* cursor index into query string */int internalpos; /* cursor index into internalquery */char *internalquery; /* text of internally-generated query */int saved_errno; /* errno at entry *//* context containing associated non-constant strings */struct MemoryContextData *assoc_context;} ErrorData;/** Write error report to server's log*/static voidsend_message_to_server_log(ErrorData *edata){StringInfoData buf;initStringInfo(&buf);saved_timeval_set = false;formatted_log_time[0] = '\0';log_line_prefix(&buf, edata);appendStringInfo(&buf, "%s: ", _(error_severity(edata->elevel)));if (Log_error_verbosity >= PGERROR_VERBOSE)appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode));if (edata->message)append_with_tabs(&buf, edata->message);elseappend_with_tabs(&buf, _("missing error text"));if (edata->cursorpos > 0)appendStringInfo(&buf, _(" at character %d"),edata->cursorpos);else if (edata->internalpos > 0)appendStringInfo(&buf, _(" at character %d"),edata->internalpos);appendStringInfoChar(&buf, '\n');if (Log_error_verbosity >= PGERROR_DEFAULT){if (edata->detail_log){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("DETAIL: "));append_with_tabs(&buf, edata->detail_log);appendStringInfoChar(&buf, '\n');}else if (edata->detail){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("DETAIL: "));append_with_tabs(&buf, edata->detail);appendStringInfoChar(&buf, '\n');}if (edata->hint){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("HINT: "));append_with_tabs(&buf, edata->hint);appendStringInfoChar(&buf, '\n');}if (edata->internalquery){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("QUERY: "));append_with_tabs(&buf, edata->internalquery);appendStringInfoChar(&buf, '\n');}if (edata->context && !edata->hide_ctx){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("CONTEXT: "));append_with_tabs(&buf, edata->context);appendStringInfoChar(&buf, '\n');}if (Log_error_verbosity >= PGERROR_VERBOSE){/* assume no newlines in funcname or filename... */if (edata->funcname && edata->filename){log_line_prefix(&buf, edata);appendStringInfo(&buf, _("LOCATION: %s, %s:%d\n"), /*记录发⽣位置*/edata->funcname, edata->filename,edata->lineno);}else if (edata->filename){log_line_prefix(&buf, edata);appendStringInfo(&buf, _("LOCATION: %s:%d\n"),edata->filename, edata->lineno);}}if (edata->backtrace){log_line_prefix(&buf, edata);appendStringInfoString(&buf, _("BACKTRACE: "));append_with_tabs(&buf, edata->backtrace);appendStringInfoChar(&buf, '\n');}}⽰例2021-08-28 18:53:24.959351T pgbench zjh@postgres localhost(40546) client backend idle 00000 [2021-08-28 18:53:24 CST] 0 [143033] DEBUG: 00000: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/02021-08-28 18:53:24.959351T pgbench zjh@postgres localhost(40546) client backend idle 00000 [2021-08-28 18:53:24 CST] 0 [143033] LOCATION: ShowTransactionStateRec, xact.c:53512021-08-28 18:53:24.959351T pgbench zjh@postgres localhost(40546) client backend idle 00000 [2021-08-28 18:53:24 CST] 0 [143033] STATEMENT: begin2021-08-28 18:53:24.959379T pgbench zjh@postgres localhost(40546) client backend idle 00000 [2021-08-28 18:53:24 CST] 0 [143033] LOG: 00000: statement: begin2021-08-28 18:53:24.959379T pgbench zjh@postgres localhost(40546) client backend idle 00000 [2021-08-28 18:53:24 CST] 0 [143033] LOCATION: exec_simple_query, postgres.c:10442021-08-28 18:53:24.959403T pgbench zjh@postgres localhost(40546) client backend BEGIN 00000 [2021-08-28 18:53:24 CST] 0 [143033] LOG: 00000: parse tree:2021-08-28 18:53:24.959403T pgbench zjh@postgres localhost(40546) client backend BEGIN 00000 [2021-08-28 18:53:24 CST] 0 [143033] DETAIL: {QUERY:commandType 5:querySource 0:canSetTag true:utilityStmt ?:resultRelation 0:hasAggs false:hasWindowFuncs false:hasTargetSRFs false:hasSubLinks false:hasDistinctOn false:hasRecursive false:hasModifyingCTE false:hasForUpdate false:hasRowSecurity false:cteList <>:rtable <>:jointree <>:targetList <>:override 0:onConflict <>:returningList <>:groupClause <>:groupingSets <>:havingQual <>:windowClause <>:distinctClause <>:sortClause <>:limitOffset <>:limitCount <>:limitOption 0:rowMarks <>:setOperations <>:constraintDeps <>:withCheckOptions <>:stmt_location 0:stmt_len 0}2021-08-28 18:53:24.959403T pgbench zjh@postgres localhost(40546) client backend BEGIN 00000 [2021-08-28 18:53:24 CST] 0 [143033] LOCATION: elog_node_display, print.c:852021-08-28 19:01:46.087410T @ autovacuum worker 00000 [2021-08-28 19:01:45 CST] 0 [143957] DEBUG: 00000: "pgbench_history": found 0 removable, 21724 nonremovable row versions in 139 out of 139 pages 2021-08-28 19:01:46.087410T @ autovacuum worker 00000 [2021-08-28 19:01:45 CST] 0 [143957] DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 23145There were 0 unused item identifiers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.2021-08-28 19:01:46.087410T @ autovacuum worker 00000 [2021-08-28 19:01:45 CST] 0 [143957] CONTEXT: while scanning relation "public.pgbench_history"LightDB 21.3将增加参数 log_retention_days 控制⾃动清理⽇志⽂件参考：pg⽇志总结详解 https:///AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.htmlhttps:///docs/current/runtime-config-logging.html。
Infobright Evaluation Report Infobright Community Edition version 3.3.2-beta Calpont InfiniDB Community Edition 1.5Percona Inc, 02 August 2010Version 1.0 – Authored by Justin SwanhartTable of Contents Summary (3)Aspects evaluated (3)Software used (3)Evaluation Results (4)DDL and datatype support (4)Loading Data (4)Compression and data size (5)Ease of installation and security evaluation (5)Ability to query over large data sets (6)Query Time Chart (6)Conclusions (7)Appendix – Query Times and Query list (8)q#1 (9)q#2 (9)q#3 (9)q#4 (10)q#5 (11)q#6 (12)q#7 (13)q#8 (14)q#9 (15)q#10 (15)q#11 (16)q#12 (16)q#13 (16)q#14 (16)q#15 (17)q#16 (17)q#17 (17)q#18 (17)q#19 (17)q#20 (17)q#21 (17)q#22 (18)q#23 (18)q#24 (18)q#25 (19)q#26 (19)q#27 (20)q#28 (21)q#29 (22)Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 2SummaryIn accordance with the Infobright “statement of work”, Percona evaluated the functional characteristics of Infobright Community Edition.Our work was performed in two different series of tests. The first test was an evaluation of the ICE functionality via a set of tests designed to execute the features which Infobright most wanted to test. These tests were provided to Todd and a number of bug reports were filed based on the results of the testing. These tests were done on a small synthetic dataset consisting of only 256 rows.The second set of testing was done on a 900GB data set provided by Infobright. This data was loaded into both Infobright CE and InfiniDB CE. A series of 29 test queries, the bulk of which were provided by Infobright were executed against both MySQL databases using the 'mysqltest' MySQL testing binary.This report is focused on the second phase of testing, comparing ICE with InfiniDB.Aspects evaluated●DDL and datatype support●Time to set up the database and load the data●Size of the loaded database on disk and compression●Ease of installation and security●Ability to query over large data sets without errors or crashes●29 queries over the carsales databaseSoftware used●Infobright Community Edition 3.3.2-beta – LOAD DATA INFILE for loading●InfiniDB 1.5 GA – cpimport utility for loading●mysqltest 5.1.45 (not distributed with InfiniDB binaries)●PHP and bash scripts – wrappers for tests and for timing the data loadingPercona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 3Evaluation ResultsDDL and datatype supportInfobright supports a number of MySQL data types which are not supported by InfiniDB. The data types below list the type supported by ICE, and what datatype was used on InfiniDB as substitute:●Year – smallint was used instead●Time – char(10) was used instead●Tinytext – varchar(255)●NOT NULL – NULL was used insteadInfiniDB does not support comments in DDL statements. All 'lookup' comments had to be stripped out of DDL before the table could be created in InfiniDB.Neither database gives very good error messages when an unsupported datatype is used. The end user experience could be improved by listing the column name and data type that was not supported. Ideally each unsupported feature could be enumerated in warnings so that all the problems could be identified at once.Loading DataInfobright CE supports the MySQL “LOAD DATA INFILE” syntax, but it does not do so in a way that is compatible with the default MySQL settings,which can be confusing.I extracted each of the compressed files provided by Infobright and loading them individually with LOAD DATA INFILE. InfiniDB has a custom loader which is invoked using the 'cpimport' utility. This utility uses a 'job file' system and the jobs must be created by another utility called 'colxml'. Overall the complexity of loading is much higher on InfiniDB than on Infobright.In both cases,the end user experience might be better if the loader was more compatible with MySQL, including support for escape characters and optional text enclosures.Loading TimesInfobright Community 3.3.2-beta28.07 hours total / avg of 1110 seconds per file InfiniDB 1.5 19.01 hours total / avg of 764 seconds per filePercona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 4Compression and data sizeInfobright Community Edition compresses data significantly when compared to the size of the input data. InfiniDB does not appear to compress data. In fact, the size on disk of the InfiniDB database is slightly larger than the size of the source material.Size of software and data on disk (905GB source data)Infobright Community 3.3.2-beta122GBInfiniDB 1.5 934GBEase of installation and security evaluationSince both database engines are distributed in both binary and source form. I tested the installation of the databases from RPM files. Both databases had to be installed as the 'root' user. Both databases install their files into a sub=directory of /usr/local. There were additional scripts to run to install the database on InfiniDB, but ICE worked out of the box. InfiniDB uses the standard MySQL port of 3306, but ICE runs on an alternate port by default which can be confusing.Since ICE supports LOAD DATA INFILE, there is no additional security work which needs to be configured to give end users the ability to load data into the database. Any MySQL user with adequate permissions can load files. The ICE loader can read from named pipes.While InfiniDB supports loading data with LOAD DATA INFILE,I found the performance to be unacceptable and instead chose to use the combination of tools: colxml and cpimport. The first utility creates an XML “job” file, and the second uses said files to load data. I was unable to get the loading utility to support loading more than one file into a table in a single job file. I used a symlink, a shell script and a hand crafted job file to load each file into the database. To further complicate matters, the job files must be placed into a particular directory, which complicates security from a unix perspective. InfiniDB requires the use of terminal aliases by default, which is inconvenient.Comparison (all points are out of five)Security Ease of loading/use Infobright Community 3.3.2-beta54InfiniDB 1.5 33Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 5Ability to query over large data setsICE was able to successfully execute all 29 of the test queries which were executed against it. InfiniDB could not execute a large number of the queries. InfiniDB does not support all of the MySQL aggregate functions, and it requires non-default values to run certain queries, or errors such as 'not enough memory for aggregation' can occur. Overall ICE appears to be able to run substantially more queries than InfiniDB due to two factors. First, ICE supports more data types and second, ICE supports more aggregate functions. The results on InfiniDB are questionable, as it gives wrong results for even a simple SELECT COUNT(*) with no where clause.Query Time ChartQueries that could not be run on InfiniDB appear as negative values on the chart.Some queries may have taken longer than 2000 seconds. Please see chart below.Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 6ConclusionsOverall, on the given data set and for the given queries, ICE performed better and could execute more queries than InfiniDB. Query results differed from ICE and InfiniDB due to bugs, data type differences and/or precision differences. In my opinion ICE is easier to use, performs better and returns more accurate results than InfiniDB at the current time.We have not evaluated the newest release of ICE, version 3.4, but we look forward to doing so in the next round of testing.Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 7Appendix – Query Times and Query listzero time means an error prevented the query from completingPercona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 8Query Listq#1select dim_dates.trans_year as c0,sum(sales_commission) as m0from fact_sales, dim_dateswhere fact_sales.trans_date=dim_dates.trans_dateand dim_dates.trans_year=2005group by c0;q#2select dim_dates.trans_year as c0,sum(sales_commission) as m0from dim_dates, fact_saleswhere fact_sales.trans_date=dim_dates.trans_dateand dim_dates.trans_year=2005group by c0;q#3select count(distinct(car_colour)) from fact_sales;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 9q#4-- sorted_resultselectd.dealer_name,sum(f.dlr_trans_amt)fromfact_sales f,dim_dealers d,dim_msa mdwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwheretd.trans_month='JANUARY'andtd.trans_year=2001)andf.msa_id = md.msa_idandmd.msa_name in('GREEN BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 10q#5-- sorted_resultselectd.dealer_name,sum(f.dlr_trans_amt) as salefromfact_sales f,dim_dealers d,dim_cars mdwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwheretd.trans_month='JANUARY'andtd.trans_year=2001)andmd.make_id = f.make_idandmd.make_name = 'ACURA'group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 11q#6selectd.dealer_name,sum(f.dlr_trans_amt) as totalfromfact_sales f,dim_dealers d,dim_cars md,dim_msa mktwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwheretd.trans_month='JANUARY'andtd.trans_year=2001)andmd.make_id = f.make_idandmkt.msa_id = f.msa_idandmkt.msa_name in('GREEN BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')andmd.make_name = 'ACURA'group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 12q#7selectd.dealer_name,sum(f.dlr_trans_amt) as totalfromfact_sales f,dim_dealers d,dim_cars md,dim_msa mktwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwheretd.trans_year = 2001)andmd.make_id = f.make_idandmkt.msa_id = f.msa_idandmkt.msa_name in('GREEN BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')andmd.make_name = 'ACURA'group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 13q#8-- sorted_resultselectd.dealer_name,sum(f.dlr_trans_amt) as totalfromfact_sales f,dim_dealers dwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwheretd.trans_year=2001andtd.trans_month in ('JANUARY','FEBRUARY','MARCH'))andd.dealer_state in ('OH','NY','MI','CA','NV')group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 14q#9selectd.dealer_name,sum(f.dlr_trans_amt),sum(f.sales_commission),sum(f.sales_discount)fromfact_sales f,dim_dealers d,dim_cars cwheref.trans_date between '2001-01-01' and '2001-01-01'andc.make_id = f.make_idandd.dealer_id = f.dealer_idandc.make_name = 'ACURA'group byd.dealer_name;q#10select dealer_id, msa_id, dlr_trans_amt from fact_sales order by 1, 2, 3 limit 2000;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 15q#11SELECT car_year,car_colour,sales_person,count(DISTINCT dim_dealers.dealer_id) AS dealer_cntFROM fact_sales, dim_dealersWHERE fact_sales.dealer_id = dim_dealers.dealer_idAND car_colour <> 'YELLOW'AND sales_person NOT LIKE 'RA%'AND car_year IN (2000, 2003, 2005)AND fact_sales.make_id NOT IN (SELECT make_idFROM dim_carsWHERE model_name LIKE 'E%X%')GROUP BY car_year, car_colour, sales_personORDER BY dealer_cnt DESC, car_year, car_colour, sales_person;q#12select AVG(sales_commission) from fact_sales where trans_year=2002;q#13select COUNT(sales_commission) from fact_sales where trans_year=2002;q#14select MAX(sales_commission) from fact_sales where trans_year=2002;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 16q#15select MIN(sales_commission) from fact_sales where trans_year=2002;q#16select STD(sales_commission) from fact_sales where trans_year=2002;q#17select STDDEV_POP(sales_commission) from fact_sales where trans_year=2002;q#18select STDDEV_SAMP(sales_commission) from fact_sales where trans_year=2002;q#19select STDDEV(sales_commission) from fact_sales where trans_year=2002;q#20select SUM(sales_commission) from fact_sales where trans_year=2002;q#21select VAR_POP(sales_commission) from fact_sales where trans_year=2002;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 17q#22select VAR_SAMP(sales_commission) from fact_sales where trans_year=2002;q#23select VARIANCE(sales_commission) from fact_sales where trans_year=2002;q#24-- sorted_resultselectd.dealer_state,sum(f.dlr_trans_amt)fromfact_sales f,dim_dealers d,dim_dates ddwheref.dealer_id = d.dealer_idandf.trans_date = dd.trans_dateand dd.trans_year = 2005group byd.dealer_state;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 18q#25select AVG(sales_commission),COUNT(sales_commission),MAX(sales_commission),MIN(sales_commission),STD(sales_commission),STDDEV_POP(sales_commission),STDDEV_SAMP(sales_commission),STDDEV(sales_commission),SUM(sales_commission),VAR_POP(sales_commission),VAR_SAMP(sales_commission),VARIANCE(sales_commission),COUNT(0) cnt_1,COUNT(*) cnt_2from fact_sales;q#26-- sorted_resultselect year(f.trans_date), dealer_name, sum( sales_commission * dlr_trans_amt )from fact_sales fjoin dim_dealers using (dealer_id)where(trans_year in (2004,2005))group by year(f.trans_date),dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 19q#27selectd.dealer_name,sum(f.dlr_trans_amt) as salefromfact_sales f,dim_dealers d,dim_cars md,dim_dates ddwheref.dealer_id = d.dealer_idandf.trans_date = dd.trans_dateanddd.trans_month='JANUARY'anddd.trans_year=2001andmd.make_id = f.make_idandmd.make_name IN('HONDA','ACURA','OLDSMOBILE')group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 20q#28-- sorted_resultselectmkt.msa_name,md.make_name,d.dealer_name,sum(f.dlr_trans_amt) as totalfromfact_sales f,dim_dealers d,dim_cars md,dim_msa mkt,dim_dates ddwheref.dealer_id = d.dealer_idandf.trans_date = dd.trans_dateanddd.trans_year=2004andmd.make_id = f.make_idandmkt.msa_id = f.msa_idandmkt.msa_name not in('GREEN BAY','AMARILLO','GREELEY','ERIE','OKLAHOMA CITY','ROCHESTER','COLUMBIA','DURHAM','AKRON','CHATTANOOGA')group by1,2,3;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 21q#29selectd.dealer_name,sum(f.dlr_trans_amt) as totalfromfact_sales f,dim_dealers dwheref.dealer_id = d.dealer_idandf.trans_date in(selecttd.trans_datefromdim_dates tdwhere(td.trans_year=2001 or td.trans_year=2002 or td.trans_year < 2001)andtd.trans_month in ('JANUARY','FEBRUARY','MARCH'))andd.dealer_state not in ('OH','NY','MI','CA','NV')group byd.dealer_name;Percona Inc, 4125 Mohr Ave, Suite H, Pleasanton, CA 94566, USA+1 (888) 401-3401info@ 22。
