I have read about MySQL. In my opinion, I see two log types as very similar. MySQL saves how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of two log types.
MySQL Logs – Difference Between Transaction Log and Redo Log
MySQLtransaction-log
Related Solutions
There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY
data type.
Prior to MySQL 5.1.35, functions like POINT()
didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this:
INSERT INTO t1 (pt_col) VALUES (GeomFromWKB(Point(1,2)));
But now, as in your example, this works:
INSERT INTO t1 (pt_col) VALUES(Point(1,2));
To the developers' credit, when they changed Point()
and similar functions to (more sanely) return GEOMETRY
objects, they allowed GeomFromWKB()
and similar functions to actually accept either WKB or MySQL Geometry data as input even though the functions are intended to accept WKB as input.
The fact that the 1st method works (in spite of being technically wrong) on newer servers and the 2nd method doesn't work at all prior to MySQL 5.1.35 might explain why examples were written using the approach you've seen -- to avoid the issue entirely. Otherwise... I've got nothing, here.
Concatenating and then parsing text seems intuitively slower and more error-prone than functions that accept proper variables as input, so I can't think of any reason to craft concatenated strings and use the text-based functions.
http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-values.html#gis-wkb-functions
http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-35.html
Seems like an appropriate way to do it.
Create a logging table:
CREATE TABLE dbo.LogSpace
(
dt DATETIME NOT NULL DEFAULT SYSDATETIME(),
dbname SYSNAME,
log_size_mb DECIMAL(22,7),
space_used_percent DECIMAL(8,5),
[status] BIT
);
Do this before and after your load:
INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status])
EXEC sp_executesql N'DBCC SQLPERF(LogSpace) WITH NO_INFOMSGS;';
Optionally, remove any rows not related to this specific database:
DELETE dbo.LogSpace WHERE dbname <> N'yourdb';
Then you can compare the before and after size/space used for any given date, or for all dates you have collected.
;WITH x AS
(
SELECT dbname, dt,
duration = DATEDIFF(SECOND, LAG(dt) OVER
(PARTITION BY dbname ORDER BY dt), dt),
[current] = space_used_percent,
previous = LAG(space_used_percent) OVER
(PARTITION BY dbname ORDER BY dt),
rn = ROW_NUMBER() OVER
(PARTITION BY dbname ORDER BY dt),
log_size_mb
FROM dbo.LogSpace
)
SELECT * FROM x WHERE rn % 2 = 0;
Keep in mind that checkpoints that happen during your process can actually make log space be re-used; I remember doing some performance testing recently and after certain operations the space_used_percent
actually went down. So you may want to take the max observed over a few days (and maybe run it more often - in which case you want a slightly different query, that doesn't assume pairs of consecutive rows are related to any specific activity), rather than just relying on how it ended up after the load.
Also make sure that the autogrow settings for the log file are reasonable - you don't want 1MB or 10%, but you don't want 10GB, either. Since an autogrow event for a log file will (a) make all transactions wait and (b) does not benefit from instant file initialization, you want a good balance between how many times the log file has to grow during an abnormal operation like your data cleanup, and how long it takes any individual growth event to happen. If that event was recent enough, you can review these events in the default trace to see how long it took then.
Best Answer
REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.
The transaction log is, by default- it can be changed- the two files located in $DATADIR called
ib_logfile0
andib_logfile1
. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is
*hostname*-bin.index
and several*hostname*-bin.00001
, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log.More info on the manual: REDO log, binary log.