Month: November 2021

Posted on

It Took an Entire Month to Build This Elaborate Treetop Hot Wheels Track That Includes a Powered Elevator

http://img.youtube.com/vi/GXUU58dhDLg/0.jpg

One of the many joyful things the internet has brought us is seeing people take their favorite childhood toys to the extreme. If you thought the Hot Wheels track you built as a kid that spanned half your house was impressive, it’s time to hang your head in shame while watching this truly amazing treetop layout.

Although the track zig-zags through a towering tree and races along branches, the creators of this course explain on Reddit that they didn’t use a single screw or nail to attach the structure to the tree. It was tied on using string so that when the fun was over the track was easy to remove without causing any permanent damage.

All-in-all the folks behind the Backyard Racing channel estimate it took two whole weeks to design the layout of the treetop track and test that all the various obstacles would work, and another two weeks to put it all together. In addition to the track sections that seem perilously perched atop tree branches, there’s also jumps, full loops, tunnels, Kodi the Australian Shepherd, and countless boosters that keeps the Hot Wheels car zooming along the track when gravity power runs out.

Without a doubt the most impressive feature (with the beautiful Fall foliage coming in at a close second, and the popsicle stick swinging bridge just behind that) is the drill-powered gondola lift that takes Hot Wheels cars from the very bottom of the track all the way back to the highest point in the tree again. Operated by a small Lego minifigure at the controls inside, it turns this whole track into a continuous Hot Wheels roller coaster, but more importantly, it means no one has to climb a ladder every time they want to launch a car.

Gizmodo

Posted on

If you insure through USAA, you might want to check the status of your account…

 

… because it appears some of their customers are having problems.  Beans, blogging at Chant du Depart, describes what he found.

So, there I was, Saturday, planning on holiday meals and planning bills (like car tag renewals and such) and while reviewing my bank statement (on-line, I’m not a total Luddite) and lo and behold, something was missing.

Utilities – check.
Cable/Internet – check.
Rent – check.
Death insurance – check.
Scamazon – check.
Food, food, food, food – check, check, check, check.
Drugs (legal variety) – check.
Car insurance – 
Car insurance – 
CAR INSURANCE!!!???? 

Lo, and behold, the wondrous USAA of which I have been a valued and happy member, thanks to Dad and Mum, for a very long time, has not dunned my account for October or November.

. . .

USAA lady – "Hello, how can I help you?"

Me – "Why have you not dunned my account for October and November?  And does this mean I no longer have car insurance?"

USAA lady – "Well, (goes onto short but clearly understandable explanation of how USAA has gone to new computer system and they migrated most data over, like phone numbers, addresses, email addys, shoe sizes, but not billing info)."

Me – "Just my billing info?  Because that would be my usual luck."

USAA lady – "No, a lot of billing info."

Me – "So you’ve been answering a lot of these calls?"

USAA lady – "Yes."  Deep remorseful sigh…

There’s more at the link.

Of course, I immediately called USAA, and found that our insurance premiums had been debited as usual.  However, our account was no longer listed as "verified", which meant I had to do that at once, otherwise our December premiums would not be withdrawn on time.  That’s in hand right now.

Full marks to USAA for sorting out the potential problem as soon as I queried it;  but it’s not so good that I had to find out about it through a third-party blog on the Internet.  If you insure through USAA, you might want to give them a call to make sure that everything’s on the up and up with your account.

Thanks, Beans, for the heads-up.  It was very timely.

Peter

Bayou Renaissance Man

Posted on

Watch: The Infamous ‘WKRP in Cincinnati Turkey Drop!

https://www.louderwithcrowder.com/media-library/image.jpg?id=24807628&width=980

I don’t have many Thanksgiving traditions. One involves a bottle of Pappy I partake in once a year. I sit on the porch with bourbon and a cigar and reflect on the things for which I am most thankful. Mostly bourbon. And content. Sweet, sweet content. I drink my way through whatever lame football matchups are on and wonder what kind of a God would force his children to sit through Joe Buck talking. At some point, I eat. This year I’ll be skipping the Thanksgiving Turkey and instead be making the Thanksgiving Waygu Tri-Tip, smoked over mesquite wood. Try not to be jealous while pretending Aunt Bertha’s turkey isn’t dry AF.

But the day is always started with reliving the infamous WKRP in Cincinnati Turkey Drop of 1978. Since you and I both know you were going to look for this video, too:

They sent Les Nessman to a strip mall, so Herb and Mr. Carlson could throw turkeys out of a helicopter. In 2021, PETA would be involved. MSNBC would claim this was white supremacy. And Les, instead of almost being killed by a man and his two children, would be yelled at for not wearing a mask.

Aaaand the money quote:

As God as my witness, I thought turkeys could fly.

Quite frankly, that was the greatest marketing stunt this side of giving away a hand-etched mug with every annual subscription to the world’s greatest political comedy show.

From all of us here at the Louder with Crowder Dot Com website, have a Happy Thanksgiving!

Get your content free from Big Tech’s filter. Bookmark this website and sign up for our newsletter!


7 BAD Excuses for Alec Baldwin | Louder With Crowder

youtu.be

Louder With Crowder

Posted on

The Evolution of macOS

https://theawesomer.com/photos/2021/11/history_of_macos_t.jpg

The Evolution of macOS

Link

Apple released its first Macintosh computer back in 1984, running one of the earliest graphical user interfaces for personal computers. The guys at Nobel Tech put together a retrospective of every version of the Macintosh operating system, from its first public release, System 0.97 to the latest version of macOS 12, Monterrey.

The Awesomer

Posted on

Swoole vs Roadrunner for Laravel Octane


Laravel Octane is a package from the Laravel team that can massively increase your application’s performance. In a normal PHP application, each HTTP request starts from a completely blank slate. The server will spawn a new PHP process, boot up the framework components and your application code, process the HTTP request, and once the response has been sent to the user it will end the process. Rinse and repeat for every request. This may sound like a huge waste of resources, but it’s actually one of the more compelling reasons to use PHP: you don’t have to care about state between requests which simplifies development. Laravel Octane introduces a very different paradigm. It will boot the framework and your application code once and keep it in memory to re-use between multiple HTTP requests. This comes with large performance improvements at the detriment of the ease of development. Now you need to think about what state is maintained between requests. It’s a tradeoff.

Swoole or Roadrunner?

When you use Laravel Octane you need to decide if you’re going to use the Swoole or Roadrunner web server to serve your application. For somebody who has never been down this path the decision can be overwhelming – after all, there are pros and cons to both and you don’t want to leave useful features on the table by choosing the wrong web server. Below is a comparison between the two based on my own research.

Swoole benefits

Swoole is actually a PHP extension that adds a lot of features to the language that can be used to increase your application’s performance. Due to this, it is considered a lot more featured than Roadrunner. Some of these features include:

  • Concurrent tasks, which allow you to run multiple functions truly asynchronously.
  • Ticks, which allow you to run a function at a specified interval.
  • Octane caching, which adds a ridiculously high performance in-memory cache to your Laravel application.
  • Swoole tables, which is an in-memory data store that is shared between all Swoole processes.

Swoole is also brings higher performance than Roadrunner, as can be seen in this GitHub repository.

Swoole drawbacks

Since Swoole is a PHP extension, you need to specifically compile it into the language during your build step. Depending on how you build and deploy your application this may introduce complexity for you or require you to learn how to build your own PHP installation. Thankfully, a lot of the hard work has been taken care of already. Laravel Sail has Swoole built in to the Docker image for local development, and there are resources online showing you how to get it working in services like Laravel Forge.

Swoole also does not work with Xdebug, the most popular debugging tool in the PHP ecosystem. You can read more about this in the GitHub issue. There was an effort to create a new debugging tool that specifically works with Swoole named yasd, however it is not nearly as mature as Xdebug.

Finally, there are a lot of reports of profiling and monitoring tools such as Datadog and New Relic not working reliably with Swoole due to its use of coroutines and technically running in a CLI environment. This is a departure away from how PHP applications are traditionally deployed, and it seems a lot of these tools have not yet caught up. Here is a good example from the Datadog repository.

Roadrunner benefits

Roadrunner, unlike Swoole, is not a PHP extension but simply a replacement for PHP-FPM. This means it does not need to be compiled into the language itself, making it a lot easier for most people to deploy. In most cases you can simply swap your usage of PHP-FPM directly with Roadrunner and immediately see large performance improvements.

Also unlike Swoole, Roadrunner is reported to work out-of-the-box with tools such as Xdebug. In fact, there is an official documentation page on how to configure your IDE. Profiling and monitoring tools such as Datadog and New Relic, which generally don’t have good support for Swoole, are also reported to be working just fine under applications served with Roadrunner. If you need to monitor your application’s health and performance metrics, this may seal the deal for you provided Roadrunner is giving you the performance your application needs.

Roadrunner drawbacks

There are really only two major drawbacks to using Roadrunner instead of Swoole: it does not add features to the PHP language that would prove useful in an application that demands high performance, and it is not able to serve as high of a request rate as Swoole. Whether you absolutely need that extra bit of performance for your application that Swoole offers is up to you – you must trade it off with the lack of health and performance monitoring, and lack of Xdebug support.

Personally, since I consider health and performance metrics as vital parts of running an application in production, and Roadrunner does give a substantial performance upgrade from PHP-FPM, Roadrunner would be my choice if I was using Laravel Octane.

Laravel News Links

Posted on

Temporary Tables in MySQL – Never Ending Story?

https://www.percona.com/blog/wp-content/uploads/2021/11/Temporary-Tables-MySQL-300×157.pngTemporary Tables MySQL

Temporary Tables MySQLIf you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of reasons. One of them was the need to completely eliminate the need to use the deprecated MyISAM engine, and at the same time introduce more performant and reliable alternatives. Another set of improvements was required related to InnoDB, where it was necessary to lower the overhead of temporary tables using that engine.

For that reason, I decided to gather them in a sort of summary which may help to troubleshoot their usage. Due to vast changes between major MySQL releases, I divided the article by them.

MySQL 5.6

(If you are still using that version, you are encouraged to consider upgrading it soon as it has reached EOL.)

User-Created Temporary Tables

When a table is created using CREATE TEMPORARY TABLE clause, it will use the engine defined by default_tmp_storage_engine (defaults to InnoDB) if not explicitly defined otherwise and will be stored inside the directory defined by the tmpdir variable.

An example one may look like this:

mysql > create temporary table tmp1 (id int, a varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql > show create table tmp1\G
*************************** 1. row ***************************
Table: tmp1
Create Table: CREATE TEMPORARY TABLE `tmp1` (
`id` int(11) DEFAULT NULL,
`a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

But how do you find the file created on disk that stores this table data? While this query may help:

mysql > select table_id,space,name,path from information_schema.INNODB_SYS_DATAFILES join information_schema.INNODB_SYS_TABLES using (space) where name like '%tmp%'\G
*************************** 1. row ***************************
table_id: 21
space: 7
name: tmp/#sql11765a_2_1
path: /data/sandboxes/msb_5_6_51/tmp/#sql11765a_2_1.ibd
1 row in set (0.00 sec)

We don’t see the original table name here. Even by looking at the buffer pool, we still don’t have the real name:

mysql > select TABLE_NAME from information_schema.INNODB_BUFFER_PAGE where table_name like '%tmp%';
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| `tmp`.`#sql11765a_2_1`  |
+-------------------------+
1 row in set (0.07 sec)

Here comes the extension available in the Percona Server for MySQL 5.6 variant – additional information_schema table: GLOBAL_TEMPORARY_TABLES. With that one, we can craft a query that provides a bit more information:

mysql > select SPACE,TABLE_SCHEMA,TABLE_NAME,ENGINE,g.NAME,PATH from information_schema.GLOBAL_TEMPORARY_TABLES g LEFT JOIN information_schema.INNODB_SYS_TABLES s ON s.NAME LIKE CONCAT('%', g.name, '%') LEFT JOIN information_schema.INNODB_SYS_DATAFILES USING(SPACE)\G
*************************** 1. row ***************************
SPACE: 16
TABLE_SCHEMA: test
TABLE_NAME: tmp1
ENGINE: InnoDB
NAME: #sql12c75d_2_0
PATH: /data/sandboxes/msb_ps5_6_47/tmp/#sql12c75d_2_0.ibd
*************************** 2. row ***************************
SPACE: NULL
TABLE_SCHEMA: test
TABLE_NAME: tmp3
ENGINE: MEMORY
NAME: #sql12c75d_2_2
PATH: NULL
*************************** 3. row ***************************
SPACE: NULL
TABLE_SCHEMA: test
TABLE_NAME: tmp2
ENGINE: MyISAM
NAME: #sql12c75d_2_1
PATH: NULL
3 rows in set (0.00 sec)

So at least for the InnoDB temp table, we can correlate the exact table name with the file path.

Internal Temporary Tables

These are ones created by MySQL in the process of executing a query. We don’t have any access to such tables, but let’s see how we can investigate their usage.

This type is created in memory (using MEMORY engine) as long as its size doesn’t exceed either tmp_table_size or max_heap_table_size variables, and if no TEXT/BLOB columns are in use. If such a table has to be stored on disk though, in MySQL 5.6 it will use MyISAM storage and also tmpdir used as a location. Quick example, on 10M rows sysbench table, query producing big internal temporary table:

mysql > SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad;

And we can see the related files growing:

$ ls -lh /data/sandboxes/msb_5_6_51/tmp/
total 808M
-rw-rw---- 1 przemek przemek 329M Sep 29 23:24 '#sql_11765a_0.MYD'
-rw-rw---- 1 przemek przemek 479M Sep 29 23:24 '#sql_11765a_0.MYI'

It may be difficult to correlate a particular temp table and its client connection though. The only information I found is:

mysql > select FILE_NAME,EVENT_NAME from performance_schema.file_summary_by_instance where file_name like '%tmp%' \G
*************************** 1. row ***************************
FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/Innodb Merge Temp File
EVENT_NAME: wait/io/file/innodb/innodb_temp_file
*************************** 2. row ***************************
FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYI
EVENT_NAME: wait/io/file/myisam/kfile
*************************** 3. row ***************************
FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYD
EVENT_NAME: wait/io/file/myisam/dfile
3 rows in set (0.00 sec)

MySQL 5.7

User-Created Temporary Tables

As earlier, the default_tmp_storage_engine variable decides on the engine used. But two changes happened here. InnoDB temporary tables now use a common dedicated shared tablespace – ibtmp1, unless it is compressed. Moreover, we have an additional information_schema view: INNODB_TEMP_TABLE_INFO. Given that, we can get information like below:

mysql > select name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE\G
*************************** 1. row ***************************
name: #sql12cf58_2_5
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
SPACE: 109
PER_TABLE_TABLESPACE: FALSE
IS_COMPRESSED: FALSE
*************************** 2. row ***************************
name: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_file_per_table_110
SPACE: 110
PER_TABLE_TABLESPACE: TRUE
IS_COMPRESSED: TRUE
2 rows in set (0.01 sec)

But again to correlate with a table name, the Percona Server for MySQL extension needs to be used:

mysql > select g.TABLE_SCHEMA, g.TABLE_NAME, name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE join information_schema.GLOBAL_TEMPORARY_TABLES g using (name)\G
*************************** 1. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp1
name: #sql12cf58_2_5
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
SPACE: 109
PER_TABLE_TABLESPACE: FALSE
IS_COMPRESSED: FALSE
*************************** 2. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp3
name: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_file_per_table_110
SPACE: 110
PER_TABLE_TABLESPACE: TRUE
IS_COMPRESSED: TRUE
2 rows in set (0.01 sec)

Alternatively, to see also MyISAM and related .frm files, we can use:

mysql > SELECT g.TABLE_SCHEMA, g.TABLE_NAME, NAME, f.FILE_NAME, g.ENGINE, TABLESPACE_NAME, PER_TABLE_TABLESPACE, SPACE FROM information_schema.GLOBAL_TEMPORARY_TABLES g join performance_schema.file_instances f ON FILE_NAME LIKE CONCAT('%', g.name, '%') left join INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO using (name) left join INFORMATION_SCHEMA.FILES fl on space=FILE_ID order by table_name\G
*************************** 1. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp1
NAME: #sql12cf58_2_5
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_5.frm
ENGINE: InnoDB
TABLESPACE_NAME: innodb_temporary
PER_TABLE_TABLESPACE: FALSE
SPACE: 109
*************************** 2. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYD
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 3. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYI
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 4. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.frm
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 5. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp3
NAME: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.frm
ENGINE: InnoDB
TABLESPACE_NAME: innodb_file_per_table_110
PER_TABLE_TABLESPACE: TRUE
SPACE: 110
*************************** 6. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp3
NAME: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd
ENGINE: InnoDB
TABLESPACE_NAME: innodb_file_per_table_110
PER_TABLE_TABLESPACE: TRUE
SPACE: 110
6 rows in set (0.01 sec)

Internal Temporary Tables

For internal temporary tables in 5.7, it is similar in terms of in-memory ones. But the default engine for on-disk temp tables is defined via a new variable: internal_tmp_disk_storage_engine, which now defaults also to InnoDB, and also the ibtmp1 tablespace is used to store its contents.

Insight into this shared temporary tablespace is pretty limited. We can check its size and how much free space is currently available. An example view was taken during heavy query ongoing:

mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G
*************************** 1. row ***************************
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
ENGINE: InnoDB
TOTAL_EXTENTS: 588
FREE_EXTENTS: 1
extent in MB: 1.00000000
MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

And after the query is finished we can see most of the space is freed (FREE_EXTENTS):

mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G
*************************** 1. row ***************************
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
ENGINE: InnoDB
TOTAL_EXTENTS: 780
FREE_EXTENTS: 764
extent in MB: 1.00000000
MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

However, the tablespace won’t be truncated unless MySQL is restarted:

$ ls -lh msb_5_7_35/data/ibtmp*
-rw-r----- 1 przemek przemek 780M Sep 30 19:50 msb_5_7_35/data/ibtmp1

To see the writing activity (which may turn out to be much higher for a single query than total size growth made by it):

mysql > select FILE_NAME, SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024 as GB_written from performance_schema.file_summary_by_instance where file_name like '%ibtmp%' \G
*************************** 1. row ***************************
FILE_NAME: /data/sandboxes/msb_5_7_35/data/ibtmp1
GB_written: 46.925933837891
1 row in set (0.00 sec)

MySQL 8.0

For simplicity, let’s skip how things worked before 8.0.16 and discuss only how it works since then, as the changes in that matter are quite significant:

  • internal_tmp_disk_storage_engine variable was removed and it is no longer possible to use the MyISAM engine for internal temporary tables
  • shared ibtmp1 table space is no longer used for either temporary table type
  • a pool of new Session Temporary Tablespaces was introduced to handle both user and internal temporary tables on disk and is located by default in the main data directory
  • the new TempTable engine for in-memory tables uses both the space in memory as well as mmapped files on disk

User-Created Temporary Tables

For an example temporary table:

mysql > create temporary table tmp1 (id int, a varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql > select * from information_schema.INNODB_TEMP_TABLE_INFO;
+----------+----------------+--------+------------+
| TABLE_ID | NAME           | N_COLS | SPACE      |
+----------+----------------+--------+------------+
|     1089 | #sqlbbeb3_a_12 |      5 | 4243767289 |
+----------+----------------+--------+------------+
1 row in set (0.00 sec)

We can correlate which file was used from that pool by looking at the space number:

mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
+----+------------+----------------------------+-------+----------+-----------+
| 10 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE   | INTRINSIC |
| 10 | 4243767289 | ./#innodb_temp/temp_9.ibt  | 98304 | ACTIVE   | USER      |
|  0 | 4243767281 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767282 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767283 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767284 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767285 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767286 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767287 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
|  0 | 4243767288 | ./#innodb_temp/temp_8.ibt  | 81920 | INACTIVE | NONE      |
+----+------------+----------------------------+-------+----------+-----------+
10 rows in set (0.00 sec)

But again, no way to look for the table name. Fortunately, Percona Server for MySQL still has the GLOBAL_TEMPORARY_TABLES table, so given the three available system views, we can get better information on user-created temporary tables using various engines, like below:

mysql > SELECT SESSION_ID, SPACE, PATH, TABLE_SCHEMA, TABLE_NAME, SIZE, DATA_LENGTH, INDEX_LENGTH, ENGINE, PURPOSE FROM information_schema.GLOBAL_TEMPORARY_TABLES LEFT JOIN information_schema.INNODB_TEMP_TABLE_INFO USING(NAME) LEFT JOIN INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES USING(SPACE)\G
*************************** 1. row ***************************
  SESSION_ID: 10
      SPACE: 4243767290
        PATH: ./#innodb_temp/temp_10.ibt
TABLE_SCHEMA: test
  TABLE_NAME: tmp3
        SIZE: 98304
DATA_LENGTH: 16384
INDEX_LENGTH: 0
      ENGINE: InnoDB
    PURPOSE: USER
*************************** 2. row ***************************
  SESSION_ID: 13
      SPACE: NULL
        PATH: NULL
TABLE_SCHEMA: test
  TABLE_NAME: tmp41
        SIZE: NULL
DATA_LENGTH: 24
INDEX_LENGTH: 1024
      ENGINE: MyISAM
    PURPOSE: NULL
*************************** 3. row ***************************
  SESSION_ID: 13
      SPACE: NULL
        PATH: NULL
TABLE_SCHEMA: test
  TABLE_NAME: tmp40
        SIZE: NULL
DATA_LENGTH: 128256
INDEX_LENGTH: 0
      ENGINE: MEMORY
    PURPOSE: NULL
*************************** 4. row ***************************
  SESSION_ID: 13
      SPACE: 4243767287
        PATH: ./#innodb_temp/temp_7.ibt
TABLE_SCHEMA: test
  TABLE_NAME: tmp33
        SIZE: 98304
DATA_LENGTH: 16384
INDEX_LENGTH: 0
      ENGINE: InnoDB
    PURPOSE: USER
4 rows in set (0.01 sec)

Similar to ibtmp1, these tablespaces are not truncated apart from MySQL restart.

From the above, we can see that user connection 10 has one open InnoDB temporary table, and connection 13 has three temporary tables using three different engines.

Internal Temporary Tables

While a heavy query is running in connection 10, we can get the following views:

mysql > show processlist\G
...
*************************** 2. row ***************************
    Id: 10
  User: msandbox
  Host: localhost
    db: test
Command: Query
  Time: 108
  State: converting HEAP to ondisk
  Info: SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad

mysql > select * from performance_schema.memory_summary_global_by_event_name where EVENT_NAME like '%temptable%'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                COUNT_ALLOC: 2
                  COUNT_FREE: 0
  SUM_NUMBER_OF_BYTES_ALLOC: 1073741824
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 2
            HIGH_COUNT_USED: 2
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1073741824
  HIGH_NUMBER_OF_BYTES_USED: 1073741824
*************************** 2. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                COUNT_ALLOC: 12
                  COUNT_FREE: 1
  SUM_NUMBER_OF_BYTES_ALLOC: 1074790400
    SUM_NUMBER_OF_BYTES_FREE: 1048576
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 11
            HIGH_COUNT_USED: 11
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1073741824
  HIGH_NUMBER_OF_BYTES_USED: 1073741824
2 rows in set (0.00 sec)

mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES where id=10\G
*************************** 1. row ***************************
     ID: 10
  SPACE: 4243767290
   PATH: ./#innodb_temp/temp_10.ibt
   SIZE: 2399141888
  STATE: ACTIVE
PURPOSE: INTRINSIC
*************************** 2. row ***************************
     ID: 10
  SPACE: 4243767289
   PATH: ./#innodb_temp/temp_9.ibt
   SIZE: 98304
  STATE: ACTIVE
PURPOSE: USER
2 rows in set (0.00 sec)

From the above, we can see the query created a huge temporary table, that first exceeded temptable_max_ram variable and continued to grow in a mmapped file (still TempTable engine), but as also temptable_max_mmap was reached, the table had to be converted to on-disk InnoDB intrinsic table. The same pool of temporary InnoDB tables is used in this case, but we can see the purpose information, depending if the table is external (user-created) or internal.

The mmapped file is not visible in the file system as it has deleted state, but can be watched with lsof:

mysqld  862655 przemek   52u      REG              253,3  133644288  52764900 /data/sandboxes/msb_ps8_0_23/tmp/mysql_temptable.8YIGV8 (deleted)

It is important to know here, that as long as mmapped space has not exceeded, the Created_tmp_disk_tables counter is not incremented even though a file is created on disk here.

Also, in Percona Server for MySQL, the extended slow log, the size of temporary tables when the TempTable engine is used, is not accounted for: https://jira.percona.com/browse/PS-5168 – it shows “Tmp_table_sizes: 0”.

In some use cases, there are problems reported with the TempTable. It is possible to switch back to the old Memory engine via the internal_tmp_mem_storage_engine variable if needed.

References

 

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!

Planet MySQL