EzDevInfo.com

Vacuum Sqlite database with EntityFramework 6.1

Recently as entertainment I decided to develop a small project to test the benefits of SQlite with EntityFramework provided by the library System.Data.SQLite.

The application has a data synchronization process which over time become obsolete so I decided to delete them from the database. As expected the size of the data base is not reduced by removing table rows, so I decided to run the command VACUUM therein.

After reading this excellent blog SQLite, VACUUM, and auto_vacuum, everything became much clearer to me, especially the fact that the command can not be executed within a transaction.

Like Code First is not yet available, I have to create the tables in the database with scripts, so in the same place I execute the command.

using (var context = new Context())
{
    context.Database.CreateIfNotExists();

    context.Database.ExecuteSqlCommand(
        "CREATE TABLE IF NOT EXISTS \"main\".\"OutgoingMessages\" (\"Id\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\"  datetime NOT NULL ON CONFLICT ROLLBACK,\"Status\"  INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Content\"  BLOB NOT NULL ON CONFLICT ROLLBACK,\"Size\"  INTEGER NOT NULL ON CONFLICT ROLLBACK,\"Hash\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Comment\"  TEXT);" +
        "CREATE TABLE IF NOT EXISTS \"main\".\"IncomingMessages\" (\"Id\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"AccountId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"MessageId\"  TEXT NOT NULL ON CONFLICT ROLLBACK,\"Date\"  datetime NOT NULL,\"Status\"  INTEGER NOT NULL,\"Comment\"  TEXT);");

    context.Database.ExecuteSqlCommand("VACUUM;");
}

I was surprised to receive the following exception:

Additional information: SQL logic error or missing database. Cannot VACUUM from within a transaction.

An exception of type 'System.Data.SQLite.SQLiteException' occurred in EntityFramework.dll but was not handled in user code
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)

What it makes me think that all commands executed by the method ExecuteSqlCommand are processed within a transaction. I am using EntityFramework 6.1.3 and System.Data.Sqlite 1.0.97.0 with .NET Framework 4.5.

QUESTIONS

Am I wrong about that? If so, is there a way of executing the command?

Do I need to reindex after vacuum full on Postgres 9.4

I am using Postgres 9.4.

I just ran vacuum full. I read about the differences between vacuum and vacuum full and considered a lot if I should run vacuum or vacuum full. As far as I can say, I required vacuum full and my db size came down from 48 GB to 24 GB.

Would the old indexes would have become obsolete after vacuum full and do I need to run reindex?

I ran "vacuum full verbose analyze", so analyze is done along with vacuum full.

I read at several places that for Postgres > 9.0, I don't need reindex after vacuum full, but I want to be sure that it is the case.

Does not android sqlite auto_vacuum truncate whole freelist pages?

I got two mmssms.db files from SAMSUNG Glaxy and Huawei C8812 respectively. Running "PRAGMA auto_vacuum;" on these files will give result "1" both. But when I had opened these files using hex editor, I found that the ptrmap pages in these file have freelist page records. Then I pulled these files from devices again, after some sms delete operation. I could found freelist page records in ptrmap pages still. The document says that when the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. How does these freelist page records and freelist pages remained?

Correct order of Postgres maintenance

I have a big table which is suffering from Index bloating because a lot of rows are frequently updated. I'm also in the process of deleting a big number of rows.

What would the correct order of executing the following tasks in order to recover disk space?

• Vacuum, mark dead tuples as free for database reuse, doesn't return space to system.
• Vacuum Full, rewrites table reducing table bloat, returns space to system.
• Reindex, rewrites indexes reducing index bloat, returns space to system.

Using Sqlite3 VACUUM command on Core Data SQLite Persistent store

In our app, we are implementing sharing of partial Core Data SQLite database through network/email. In order to keep the file size small, I have implemented the below method to shrink the Core Data database.

    - (void) shrinkDB
    {
        sqlite3 * database;
        NSString * string = [shareStoreURL path];
        const char * filename = [string cStringUsingEncoding:[NSString defaultCStringEncoding]];
        char *errMsg;
        if (sqlite3_open(filename, &database) == SQLITE_OK)
        {
            NSLog(@"Shrinking...");
            if (sqlite3_exec(database, "VACUUM;", NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog(@"Failed execute VACUUM");
            }
            sqlite3_close(database);
         }
        }

QUESTION: The above code does shrink the database. But Apple says the implementation details of Core Data are subject to change any time. Do you think I would be safe using this method for foreseeable future? Or is there any other better solution?

Postgresql: database is not accepting commands to avoid wraparound data loss

Got the error upon create/delete/update queries:

ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb" HINT: Stop the postmaster and use a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions.

So, the database is blocked and it is only possible to perform SELECT queries.

Database's size 350 GB. 1 table(my_table) has ~1 billion rows.

system: "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit"

postgresq.conf some settings:

effective_io_concurrency = 15           # 1-1000; 0 disables prefetching
autovacuum_vacuum_cost_delay = -1

#vacuum_cost_delay = 0                  # 0-100 milliseconds
#vacuum_cost_page_hit = 1               # 0-10000 credits
#vacuum_cost_page_miss = 10             # 0-10000 credits
#vacuum_cost_page_dirty = 20            # 0-10000 credits
#vacuum_cost_limit = 200 

I do not use prepared transactions. But basic stored procedures are used(which means, automatic tranactions, right?) 50mln times per day.

Сurrently "autovacuum: VACUUM ANALYZE public.my_table (to prevent wraparound)" is perforing, it is almost 12 hours of that query activity.

As far as I understand, the problem with not-vacuumed dead touples, right?

How to resolve this problem and prevent this in the future? Please, help :)

The end of story( ~one month later) Now my big table is partitioned by thousands of tables. Each small table is vacuumed much faster. Autovacuum configuration was set more closer to default. If needed, i could be set to more agressive again, but so far database with billions of rows works pretty well.

So, the problem of the topic should not appear again.

ps now i'm looking at Postgres-XL as a next step of data scalability.

How do I observe this recursive function with GHood or Vacuum?

I'm using GHood to watch the execution of this implementation of the partition function and expected to see a binary tree. Instead I get this tree:

import Debug.Observe

p :: (Observable a, Ord a, Num a) => a -> a -> a
p k n 
  | k > n = 0
  | k == n = 1
  | otherwise = (observe "left" p (k+1) n ) + (observe "right" p k (n-k))

>>> printO $ observe  "p" p 1 3

I've also unsuccessfully tried using lazy natural numbers from Data.Number.Natural as per a suggestion on irc.

When using Vacuum.Cairo I get even less back, just the result of the function run to completion.

>>> m: + System.Vacuum.Cairo
>>> view $ p 1 5

Dump postgres data with indexes

I've got a Postgres 9.0 database which frequently I took data dumps of it.

This database has a lot of indexes and everytime I restore a dump postgres starts background task vacuum cleaner (is that right?). That task consumes much processing time and memory to recreate indexes of the restored dump.

My question is:

1. Is there a way to dump the database data and the indexes of that database?
2. If there is a way, will worth the effort (I meant dumping the data with the indexes will perform better than vacuum cleaner)?
3. Oracle has some the "data pump" command a faster way to imp and exp. Does postgres have something similar?

Thanks in advance, Andre

Can I run a PostgreSQL Vacuum Once every 1-2 minutes?

I am considering various MVCC-capable databases for an upcoming project and PostgreSQL came onto my radar.

The requirements of my program involve a sequence roughly like the following:

1. Read some information from the current version of the database, modify 80-90% of the data and write it back in one or more transactions (imagine something like updating the grid in Conway's Game of Life where both the old and new state of the grid are required).

2. Wait 1-2 minutes after the commit. During this time, clients can issues reads against new data.

3. Repeat.

The databases will be limited to something like 2-4GB.

~90% of the changes are updates to existing objects, ~5% will be new objects and ~5% will be deleted objects.

So my question is, can I reasonably run a plain VACUUM command as step 1.5 once every 1-2 minutes and have PostgreSQL be able to keep up with potentially 2-3+GB of changes being made each time?

How do I know if the statistics of a Postgres table are up to date?

In pgAdmin, whenever a table's statistics are out-of-date, it prompts:

Running VACUUM recommended

The estimated rowcount on the table schema.table deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table.

I've tested it using pgAdmin 3 and Postgres 8.4.4, with autovacuum=off. The prompt shows up immediately whenever I click a table that has been changed.

Let's say I'm making a web-based system in Java, how do I detect if a table is out-of-date, so that I can show a prompt like the one in pgAdmin?

Because of the nature of my application, here are a few rules I have to follow:

1. I want to know if the statistics of a certain table in pg_stats and pg_statistic are up to date.

2. I can't set the autovacuum flag in postgresql.conf. (In other words, the autovacuum flag can be on or off. I have no control over it. I need to tell if the stats are up-to-date whether the autovacuum flag is on or off.)

3. I can't run vacuum/analyze every time to make it up-to-date.

4. When a user selects a table, I need to show the prompt that the table is outdated when there are any updates to this table (such as drop, insert, and update) that are not reflected in pg_stats and pg_statistic.

It seems that it's not feasible by analyzing timestamps in pg_catalog.pg_stat_all_tables. Of course, if a table hasn't been analyzed before, I can check if it has a timestamp in last_analyze to find out whether the table is up-to-date. Using this method, however, I can't detect if the table is up-to-date when there's already a timestamp. In other words, no matter how many rows I add to the table, its last_analyze timestamp in pg_stat_all_tables is always for the first analyze (assuming the autovacuum flag is off). Therefore, I can only show the "Running VACUUM recommended" prompt for the first time.

It's also not feasible by comparing the last_analyze timestamp to the current timestamp. There might not be any updates to the table for days. And there might be tons of updates in one hour.

Given this scenario, how can I always tell if the statistics of a table are up-to-date?

how to automatically determine which tables need a vacuum / reindex in postgresql

i've written a maintenance script for our database and would like to run that script on whichever tables most need vacuuming/reindexing during our down time each day. is there any way to determine that within postgres?

i would classify tables needing attention like this:

• tables that need vacuuming
• tables that need reindexing (we find this makes a huge difference to performance)

i see something roughly promising here

Ruby on Rails: How do I use a Gem? (Vacuum Amazon API)

I found an Amazon Gem that I want to use https://github.com/hakanensari/vacuum/.

I've only ever used gems with in depth tutorials or following a RailsCast. I'm looking for tips on using gems I find online. I'll explain the steps I'm doing, and hopefully someone can give me some ideas on what else I should be doing when using a new gem. Also, if you have a good tutorial or explanation on gems, that would be great too.

I started off examining the Gem on Github, I'll point out the things I took notice of. Let me know if there are things I'm missing I should notice.

Examining Gem on Github

1. Go to the examples and look at "examples/product_advertising/basic_lookup.rb"
2. Follow the required file, and checkout "examples/product_advertising/shared.rb"
3. Notice, I need to install "pry"
4. Notice, the examples expand on the "lib" folder
5. Check out "credentials.yml"
6. Notice a "@req" is instantiated as a new Vacuum object.
7. Then back in basic_lookup.rb, it looks like it's assigning the lookup value, then binding the response to some kind of "pry" view.

Next, I'll try implementing these examples in my own project. Here's where I'm not sure what to do as far, as files are concerned.

Attempt Implementing Example

1. Install vacuum gem

   gem install vacuum

2. Install pry gem

   gem install pry

3. Added "shared.rb" and "credentials.yml" to my "app/controllers" directory
4. Replaced the information in "credentials.yml" with my information

5. Attempt to copy the information from "basic_lookup.rb" into an existing controller

   def amazon
     require File.expand_path('../shared.rb', __FILE__)
   
     res   = @req.look_up '0816614024'
     items = res.find 'Item'
   
     binding.pry
   end
   

6. Create a route

   match '/test' => 'products#amazon'

7. Go to test page and receive the following error

   undefined method 'look_up' for nil:NilClass

I would like to point out, at this point I haven't added the lib folder.

Questions

• I like the credentials.yml is separated out, when I want to add this to my project, where should I save that file?
• I like the shared.rb file, should I just put that in the controller folder?
• Why is it referencing the "lib" folder in "shared.rb"? Do I need to copy that directory into my project?

I appreciate you sticking around and reading all of this. I'm still trying to get a handle on using a gems, so any help or tips are great. Really, I'm just trying to figure out, how do I find any gem and start using it appropriately.

Thanks for any help you can give me!

What is Coding in Vacuum? [closed]

What does the term "Coding in Vacuum" mean? I have heard the term before but I am unable to find anything on Google that is relevant.

Is it possible to issue a "VACUUM ANALYZE " from psycopg2 or sqlalchemy for PostgreSQL?

Well, the question pretty much summarises it. My db activity is very update intensive, and I want to programmatically issue a Vacuum Analyze. However I get an error that says that the query cannot be executed within a transaction. Is there some other way to do it?

Why does $dbh->do('VACUUM') fail with Perl's DBD::SQLite?

I want to do VACUUM at a certain time on a SQLite database under Perl, but it always says

DBD::SQLite::db do failed: cannot VACUUM from within a transaction

So how do I do this?

my %attr = ( RaiseError => 0, PrintError => 1, AutoCommit => 0 );
my $dbh = DBI->connect('dbi:SQLite:dbname='.$file'','',\%attr) 
    or die $DBI::errstr;

I am using AutoCommit => 0. And the error happens while:

$dbh->do('DELETE FROM soap');
$dbh->do('DELETE FROM result');
$dbh->commit; 
$dbh->do('VACUUM');