# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /usr/log/slowquery.log
log_queries_not_using_indexes = 1
#Set General Log
general_log = on
Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.
Locate your postgresql.conf in the datadir
Find the section that looks like this
# ERROR REPORTING AND LOGGING
# - Where to Log -
#log_destination = 'stderr' # Valid values are ...
General log - all queries - see VARIABLE general_log
Slow log - queries slower than long_query_time - slow_query_log_file
Binlog - for replication and backup - log_bin_basename
Relay log - also for replication
general errors - mysqld.err
start/stop - mysql.log (not very interesting) - log_error
InnoDB redo log - iblog*
See the variable basedir and datadir ...
Since this is the type of thing you probably only want to do temporarily, it may be useful to do this from the shell instead of via the config file:
> set global general_log_file = "/var/log/mysql/queries.log";
> set global general_log = "ON";
[wait some time, hit some pages, whatever]
> set global general_log = "OFF";
You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well
In SQL Server 2012 and up, you will get a nice error
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_dboption'.
Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, SQL ...
There is a built-in way to log all statements inside plpgsql functions: auto-explain
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions
Details under this closely related question:
Postgres query plan of a UDF ...
You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space.
, substring([physical_name],1,3) AS [Drive]
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted ...
This means that your binary log format binlog_format is STATEMENT, which is the default binary log format.
STATEMENT-based format logs the statement issued, while ROW-based format logs how individual tables were changed. The problem with STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the ...
Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file.
On most platforms you just gzip them, e.g.
archive_command = 'gzip -c < "%p" > /archive/path/%f"'
On Windows this won't work natively due to the lack of the gzip command. You'll need to ...
SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.
Yeah, this is bubbled up from the OS error the logwriter is running into. In this case it's OS error 170 which is the resource is in use. That's pretty damning that the issue is outside of SQL Server (barring any bad 3rd party DLLs loaded into the address ...
I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:
CREATE TABLE dbo.Users
UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
Username NVARCHAR(128) NOT NULL UNIQUE,
/* , other columns */
CREATE TABLE dbo.Tables
Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout.
Setting log_min_duration_statement=-1 (1st login):
P:\>psql -U postgres -h 126.96.36.199 -d my_db
my_db=# SHOW log_min_duration_statement;
Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again.
Try running this to see how much actual space on your log is taken up.
[TYPE] = A....
Log Sequence Number (LSN) : Log Sequence Numbers correspond to given
position in the log files and typically incremented for each log
record. Innodb uses number of bytes ever written to the log files
however it could be something different. LSNs are often extensively
used in recovery check pointing and buffer management ...
What was suggested to you thus far is what can be done to bring the database to a consistent state.
Here is what you need to know about InnoDB.
First of all here is the InnoDB Architecture in Pictorial Form
Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)?
The Double Write Buffer has the ...
The problem is indeed as @Phil hinted at. To fix it, set the permissions for the _mysql user:
> cd ~
> touch mysql.log
> sudo chown _mysql:wheel mysql.log
Restart MySQL after that and the log file will be used.
Put these two lines in my.cnf.
general_log = on
This will log all queries to the server, from any source, not just PHP/PHPMyAdmin.
Be careful though - enabling the general log can place a heavy load on your server. To be used sparingly for short periods/debugging only.
Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize:
In order for a log backup to be useful for a restore, there needs to ...
I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser):
ALTER ROLE someone SET log_statement TO 'all';
Which will then turn on log_statement every time that that someone connects in the future.
For a single connection which is cooperating with you, you could create this function (as a superuser):
You should be able to find the full text log location with the following code:
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
I don't think it's possible to split them away from the normal SQL Error log as this connect suggests.
In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included.
From https://www.debian.org/doc/debian-policy/ch-files.html :
Log files must be rotated occasionally so that they don't grow
indefinitely. The best way to do this is to install a log rotation
UPDATE: I figured it out!
Because the ola hallengren scripts check for log shipping roles and skip log backups if it finds the current db in either
AND because the databases I have been fighting with recently DID have old log shipping entries that were not proiperly ...
If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out
Then, after you are done with your packet capture, do this:
pt-query-digest tcpdump.out --...
After some major league Googling all over the planet, I think I found the answer.
From these posts I saw something interesting
13679547 Connect @localhost as anonymous on test
140612 12:33:07 3 Connect gk_admin at ...
Should I turn to simple recovery model and truncate?
That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task.
Should I stop mirroring, do what I should do to principal database and then enable mirroring?
The MySQL logs are determined by the global variables such as:
log_error for the error message log;
general_log_file for the general query log file (if enabled by general_log);
slow_query_log_file for the slow query log file (if enabled by slow_query_log);
To see the settings and the logs' locations, run this shell command:
mysql -se "SHOW VARIABLES" | ...
The PostgreSQL Error reporting and logging documentation states:
When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log ...
The write and sync phase mean rather different things (unfortunately). In the middle phase of a checkpoint, it writes out all the dirty buffers from shared buffers. It does this in a throttled way, mainly regulated by checkpoint_completion_target. The time reported here is the time it spent writing those buffers, as well as the time it spend sleeping in ...