How much (estimated percentage) is going to worsen the response-time of the database if the logging level is lowered to "DEBUG". ?
VoltDB Logging Level
loggingvoltdb
Related Solutions
At startup, VoltDB is going to send a round-trip message from the first node of the cluster to all joining nodes of the cluster. This message queries the local time at each node.
For each joining node, the difference between its local time and the first node's local time is computed. This includes clock skew and network latency. In fact, it's possible for clock skew and network latency to cancel each other out, but not super likely.
Across all joining nodes, VoltDB reports the maximum difference between clocks.
In this case, VoltDB is telling you that for some pair of nodes including a joining node and the first node you started (the startup leader), the difference between the compared clocks was 120ms, which is a lot for machines on NTP that are in the same datacenter.
You can check network latency using ping between nodes, but make sure you're using the same network interface you've setup VoltDB to run on.
Yes, we did this type of thing at a company I worked at a few years ago. Even though we did it only for error logging, it was the same general concept. You just need to write the SELECT statement that includes all of the input parameters, use FOR XML
, and store that in a variable that can be inserted into your Execution
table in a new field: Parameters XML NULL
. And then you can scrap the ExecutionParameter
table.
Try the following as it shows three ways of generating the XML: Attribute-based, Element-based, and Element-based including NULL elements. The default behavior of FOR XML
is to indicate NULL
values by not including the attribute or element. But if you are doing Element-based XML and want the parameters to show even if they are NULL
, then you need to specify XSINIL
in the FOR XML
clause. The ability to include an indicator for NULL
is not available in Attribute-based XML.
Please note that there is not a huge difference between Attributed-based XML and the default Element-based XML (default = does not include NULL
items) in terms of storage size (as shown in the example below). When viewing the XML it does appear that there is a lot more "bloat" with Element-based, and that would be the case if storing the data in an NVARCHAR
field or text file. But the XML
datatype uses an optimized method of storing the data internally, in a way that cannot be seen, but is clearly indicated in the resulting DATALENGTH
output.
There is, however, a slight increase in size when using the XSINIL
option for Element-based XML and it needs to keep track of NULL
elements which are excluded from the other two types. The increase in size is due to a one-time hit for declaring the xsi
namespace in the root element, and then a per-NULL
element hit.
DECLARE @Param1 INT = 5,
@Param2 DATETIME = NULL,
@Param3 NVARCHAR(50) = N'Test < some & XML chars "',
@Param4 DATETIME = '2016-04-09';
DECLARE @Parameters XML;
SELECT @Parameters = (
SELECT @Param1 AS [Param1],
@Param2 AS [Param2],
@Param3 AS [Param3],
@Param4 AS [Param4]
FOR XML RAW(N'Params') -- Attribute-based XML (NULL attributes are missing)
);
SELECT @Parameters, DATALENGTH(@Parameters); -- 183
SELECT @Parameters = (
SELECT @Param1 AS [Param1],
@Param2 AS [Param2],
@Param3 AS [Param3],
@Param4 AS [Param4]
FOR XML RAW(N'Params'), ELEMENTS -- Element-based XML (NULL elements are missing)
);
SELECT @Parameters, DATALENGTH(@Parameters); -- 185
SELECT @Parameters = (
SELECT @Param1 AS [Param1],
@Param2 AS [Param2],
@Param3 AS [Param3],
@Param4 AS [Param4]
FOR XML PATH(N'Params'), ELEMENTS XSINIL -- Element-based XML (NULL elements included)
);
SELECT @Parameters, DATALENGTH(@Parameters); -- 434
Returned XML values:
<!-- Attribute-based -->
<Params Param1="5" Param3="Test < some & XML chars ""
Param4="2016-04-09T00:00:00" />
<!-- Element-based -->
<Params>
<Param1>5</Param1>
<Param3>Test < some & XML chars "</Param3>
<Param4>2016-04-09T00:00:00</Param4>
</Params>
<!-- Element-based including NULL elements -->
<Params xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Param1>5</Param1>
<Param2 xsi:nil="true" />
<Param3>Test < some & XML chars "</Param3>
<Param4>2016-04-09T00:00:00</Param4>
</Params>
Additional notes:
For
SchemaName
andProcedureName
columns, I would usesysname
as the datatype since that is how it is defined in the system tables. And be sure to use lower-case forsysname
as it is an alias that lives inmaster
and servers with a default collation that is binary or case-sensitive won't be able to find that alias if it is not all lower-case. Your current server might be case-insensitive, but it is still a good habit to get into when usingsysname
.For the
ExecutionError
table, be sure to includeINT
columns to capture:ERROR_LINE()
,ERROR_NUMBER()
,ERROR_STATE()
, andERROR_SEVERITY()
.No, you don't want to use
SQL_VARIANT
as that prevents you from storing the LOB types:VARCHAR(MAX)
,NVARCHAR(MAX)
,VARBINARY(MAX)
, andXML
.No, you can't use
NVARCHAR(8000)
becauseNVARCHAR
has a top size of 4000, unless you useMAX
.
Related Question
- How to use trigger in VoltDB
- SQL Server Logging Optimization and Maintenance
- MySQL Logging – Why MySQL Doesn’t Log Queries Anymore
- MongoDB – How to Change Logging Level
- T-sql – Massive Transaction Logs – what are the options
- MySQL – Non-Deterministic Functions and Row-Based Binary Logging
- Sql-server – Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work
- Mariadb – Log to disk all queries that waited for a lock over a period of time
Best Answer
I work at VoltDB. I wouldn't recommend setting the overall logging level to DEBUG except perhaps for a DEV environment for limited tests. It's hard to say what % the performance impact would be. Everything might be a bit slower, but there could be a lot more GC triggered which can have an unpredictable impact.
You can adjust the level for specific loggers, if you have an idea of the area of functionality where you want to see more detail. We have a howto guide on configuring logging here: https://github.com/VoltDB/voltdb/blob/master/examples/HOWTOs/ConfigureLogging.md