Tag: big

postgresql – Triggering Bitmap heap scan on big table. Can this be avoided somehow?

Context

Brief context, I’ve got 2 “geographic” tables, T1 with 16m rows (measurements) and T2 with ~2k rows (states and cities).

I’m creating a geometry-less cache_areas table with the pre-made calculation of intersection areas (ST_Area) between these two. First I’ve made the cities part (by actually running the postGIS functions), and now I’m doing the states part, briefly, by “cloning” the cities parts from the table itself. This second part was supposed to run faster… but actually isnt. And I caught the script taking long on a SELECT query.

select
  "geog_data"."id",
  "int_area"
from
  "cache_areas"
inner join "geog_data" on
  "geog_data"."id" = "cache_areas"."data_id"
where
  "geompred_id" IN (...) -- 25 items
  and "geog_data"."dataset_id" = 127;

CREATE TABLE public.cache_areas ( -- ~1M rows
  geompred_id int4 NULL,  -- around 2k different ids
  data_id int4 NULL,
  int_area float8 NULL,
  CONSTRAINT cache_areas_fk FOREIGN KEY (geompred_id) REFERENCES observ_app_geometriapredefinida(id)
);
CREATE INDEX cache_areas_data_id_idx ON public.cache_areas USING btree (data_id);
CREATE INDEX cache_areas_geompred_id_idx ON public.cache_areas USING btree (geompred_id);

CREATE TABLE public.geog_data ( -- ~16M rows
  id serial NOT NULL,
  src_id int4 NULL,
  geometry geometry NULL,
  properties jsonb NULL,
  dataset_id int4 NOT NULL,  -- ~ 100 options
  subclass_id int4 NOT NULL, -- not used here, ~10 options
  CONSTRAINT geog_data_pkey PRIMARY KEY (id),
  CONSTRAINT geog_data_dataset_id_bb7e5413_fk_storage_a FOREIGN KEY (dataset_id) REFERENCES storage_app_dataset(id) DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT geog_data_subclass_id_376653cf_fk_storage_a FOREIGN KEY (subclass_id) REFERENCES storage_app_subclass(id) DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX geog_data_dataset_id_bb7e5413 ON public.geog_data USING btree (dataset_id);
CREATE INDEX geog_data_geometry_id ON public.geog_data USING gist (geometry);
CREATE INDEX geog_data_src_id_ccad6d88 ON public.geog_data USING btree (src_id);
CREATE INDEX geog_data_subclass_id_376653cf ON public.geog_data USING btree (subclass_id);

This is the query on my test database, which only has ~1m rows.

Nested Loop  (cost=0.72..8.77 rows=1 width=12)
  ->  Index Scan using geog_data_dataset_id_bb7e5413 on geog_data  (cost=0.29..4.30 rows=1 width=4)
        Index Cond: (dataset_id = 127)
  ->  Index Scan using cache_areas_data_id_idx on cache_areas  (cost=0.43..4.46 rows=1 width=12)
        Index Cond: (data_id = geog_data.id)
        Filter: ((geompred_id >= 1) AND (geompred_id <= 27))

On the full dataset (16M rows) a bitmap scan is triggered before the index scan.

Gather  (cost=1045.21..13834.92 rows=86 width=12)
  Workers Planned: 2
  ->  Nested Loop  (cost=45.21..12826.32 rows=36 width=12)
        ->  Parallel Bitmap Heap Scan on geog_data  (cost=44.78..6715.76 rows=1409 width=4)
              Recheck Cond: (dataset_id = 127)
              ->  Bitmap Index Scan on geog_data_dataset_id_bb7e5413  (cost=0.00..43.93 rows=3382 width=0)
                    Index Cond: (dataset_id = 127)
        ->  Index Scan using cache_areas_data_id_idx on cache_areas  (cost=0.43..4.33 rows=1 width=12)
              Index Cond: (data_id = geog_data.id)
              Filter: ((geompred_id >= 1) AND (geompred_id <= 27))

Ive read something about Bitmap Heap being trigggered when we can’t fit something in memory. Could an index or config change avoid trigerring it? Or is it ok and can’t be much better?

If you need more info I’d be happy to provide.

I/O also seems a bit slow. iotop shows 1600 KB/s read, on a NVMe SSD. Thank you.

command line – Adding a startup script on Big Sur

I’m wanting to install a startup item in OS X Big Sur which runs a shell script as a user on my machine and connects via ssh to a remote machine I maintain. I use ssh keys so no password is required for this script to succeed. It will linger in the background. How would I accomplish this in Big Sur? My understanding is that the old /Library/Startup Items/xxx.plist trick is deprecated. What is used in its place?
Thanks!

unity – Why are .anims so big?

I am in the process of recording dialogue via FaceCapX and an animation recorder script that checks for changes between frames and if there is one it saves the value of 52 blend shapes, as well as head transform rotation and position.

To my limited understanding, the file should be containing something like (int) frame + (double) blend shape value per frame.? Which would lead to something like:

in byte:
4 + (8 * 52 blend shapes) * 60 frames per second * 60 seconds = 1,497 MB per minute.

Anyway, I end up with 18 MB for roughly 15 seconds of recording.

What is missing in my calculation?
Is there a bug in unity .anims?

macos – How to delete a file from Time Machine backups in Big Sur?

I can’t seem to take a screenshot of Time Machine, but I’ll describe it. I go to the Time Machine menu and select “Enter Time Machine”. Then I see the view with the stack of windows. I browse to a file and select it. Now I want to delete it so it doesn’t exist and take up space in backups. There is no option to delete it – either the action menu in the toolbar, or the control-click context menu.

How do I delete a file from Time Machine backups? There used to be a way to do this.

What is the big O for $n^{log n} + n!$?

By intuition $n^{log n}$ should be as the same as $n!$. By Stirling’s approximation, $n!=n^ne^{-n}sqrt{2pi n}$, which makes me think both are also the same. So, is this $O(n!)$ or $O(n^{log n})$?

What is the big O for n^log(n) + n?

By intuition $n^{log(n)}$ should be as the same as n!. By sterling approximation $n!=n^ne^{-n}sqrt{2pi n}$, which makes me think both are also the same. So, is this O(n!) or $O(n^{logn})$?

How big is all of debian buster for amd64?

I can get the size of the debian archive from https://www.debian.org/mirror/size.

Is there a way to get the size of only Debian Buster for amd64? And separately for main, contrib and non-free?

Why MyBatis query big table timeout but fast in Sequel Pro in MySQL 5.7

I am using MySQL 5.7, today this is a long time query stuck me. this is my table DML:

CREATE TABLE `h_round_pump_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
  `tenant_id` bigint(20) NOT NULL COMMENT '',
  `tenant_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `created_time` bigint(20) NOT NULL COMMENT '',
  `updated_time` bigint(20) NOT NULL COMMENT '',
  `app_id` bigint(11) NOT NULL COMMENT '',
  `app_mark` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `app_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `settlement_time` bigint(20) NOT NULL COMMENT '',
  `biz_id` bigint(20) NOT NULL COMMENT '',
  `room_play_id` bigint(20) NOT NULL COMMENT '',
  `room_amount_fmt` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `tenant_revenue` bigint(20) GENERATED ALWAYS AS ((`real_people_revenue` + `robot_profit`)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `biz_id` (`biz_id`,`tenant_id`,`app_id`),
  KEY `round_record_idx` (`settlement_time`,`having_real_people`,`tenant_id`,`app_id`)
) ENGINE=InnoDB AUTO_INCREMENT=907725 DEFAULT CHARSET=utf8mb4;

and this is my query sql:

select *
    from h_round_pump_record 
     WHERE (  settlement_time <= 1601481599000
                  and settlement_time >= 1598889600000
                  and tenant_id = 
                  and app_id in
                  (
                    1
        ) )

to speed up query, I create a index in settlement_time. In Sequel Pro the SQL will return result in 3 seconds, but In apps query using MyBatis, it will take 30s to return, why the MyBatis query takes so long(the sql is the same because I am copied from slow_log table)? This is my slow log settings:

set global slow_query_log=ON
set global log_output='table';
set long_query_time = 5;
select * 
from mysql.slow_log;

this is the log output of MyBatis:

org.springframework.dao.QueryTimeoutException: 
### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
### The error may exist in class path resource (mybatis/mapper/illidan/RoundPumpRecordMapper.xml)
### The error may involve com.sportswin.soa.illidan.hub.dao.RoundPumpRecordMapper.selectByExample-Inline
### The error occurred while setting parameters
### SQL: select           'true' as QUERYID,                id, tenant_id, tenant_name, agent_id, agent_name, created_time, updated_time, app_id,      app_mark, app_name, settlement_time, biz_id, room_play_id, room_amount_fmt, ratio,      tenant_revenue, real_pump_amount, real_people_profit, robot_profit, robot_revenue,      real_people_revenue, having_real_people         from h_round_pump_record                         WHERE (  settlement_time <= ?                                                                and settlement_time >= ?                                                                and tenant_id = ?                                                                and app_id in                   (                     ?                   ) )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
; Statement cancelled due to timeout or client request; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:76) ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at com.sun.proxy.$Proxy178.selectList(Unknown Source) ~(na:na)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57) ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy187.selectByExample(Unknown Source) ~(na:na)
    at com.sportswin.soa.illidan.hub.service.impl.HubRoundPumpRecordService.lambda$page$0(HubRoundPumpRecordService.java:63) ~(classes!/:na)
    at com.github.pagehelper.Page.doSelectPageInfo(Page.java:353) ~(pagehelper-5.1.11.jar!/:na)
    at com.sportswin.soa.illidan.hub.service.impl.HubRoundPumpRecordService.page(HubRoundPumpRecordService.java:62) ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubCommonUtil.getRoundPump(HubCommonUtil.java:164) ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.generateImpl(HubScheduleTask.java:177) ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.generateFilesTask(HubScheduleTask.java:159) ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.lambda$generateExportFile$0(HubScheduleTask.java:103) ~(classes!/:na)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~(na:1.8.0_212)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~(na:1.8.0_212)
    at com.sportswin.soa.misc.config.async.pool.MdcTaskDecorator.lambda$decorate$0(MdcTaskDecorator.java:31) ~(soa-misc-1.0.0-SNAPSHOT.jar!/:na)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~(na:1.8.0_212)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~(na:1.8.0_212)
    at java.lang.Thread.run(Thread.java:748) ~(na:1.8.0_212)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.StatementImpl.checkCancelTimeout(StatementImpl.java:2191) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1020) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1075) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$zlbDqAbk(ClientPreparedStatement.java:370) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$zlbDqAbk$accessor$77RRWgJE(ClientPreparedStatement.java) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement$auxiliary$WZ5P0ECx.call(Unknown Source) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:93) ~(skywalking-agent.jar:6.5.0)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) ~(druid-1.1.9.jar!/:1.1.9)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~(druid-1.1.9.jar!/:1.1.9)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~(mybatis-3.5.2.jar!/:3.5.2)
    at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke(Method.java:498) ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke(Method.java:498) ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at com.github.pagehelper.util.ExecutorUtil.pageQuery(ExecutorUtil.java:180) ~(pagehelper-5.1.11.jar!/:na)
    at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:104) ~(pagehelper-5.1.11.jar!/:na)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at sun.reflect.GeneratedMethodAccessor263.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke(Method.java:498) ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~(mybatis-3.5.2.jar!/:3.5.2)
    at tk.mybatis.orderbyhelper.OrderByHelper.intercept(OrderByHelper.java:115) ~(orderby-helper-0.0.2.jar!/:na)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~(mybatis-3.5.2.jar!/:3.5.2)
    at sun.reflect.GeneratedMethodAccessor414.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke(Method.java:498) ~(na:1.8.0_212)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    ... 19 common frames omitted

What is the next big SEO trend coming in 2020? What are your predictions?

What is the next big SEO trend coming in 2020? What are your predictions?

big o – Prove that the time complexity of a function is O(n^3)

public void function2(long input) {
    long s = 0;

    for (long i = 1; i < input * input; i++){
        for(long j = 1; j < i * i; j++){
            s++;
        }
    }
}

l’m pretty certain that the time complexity of this function is n^3, however if someone could provide a line by line explanation of this, that would be great.