Sunday, April 13, 2025

Slow Query Monitoring in MySQL and MariaDB

To enable slow query monitoring in MySQL or MariaDB, run these commands in your client:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

This activates the slow query log and sets it to capture any queries taking longer than 0.5 seconds. You can adjust this threshold based on your performance requirements.

Slow queries will be logged to some file like:

/var/lib/mysql/*-slow.log

You can check or change that with:

SHOW VARIABLES LIKE 'slow_query_log_file';
SET GLOBAL slow_query_log_file = '
/custom/path/slow-queries.log';

To make these changes permanent, add these lines to your my.cnf/my.ini file:

[mysqld]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /custom/path/slow-queries.log

To analyze slow queries, use:

mysqldumpslow /custom/path/slow-queries.log

Wednesday, February 19, 2025

A thought experiment about consciousness

I came up with this thought experiment over the past few years. I'm not completely sure it is original, but here it is:

I will not define consciousness because I think that most definitions of it work fine with the experiment, and if yours doesn't, you'll know fairly soon. We will assume that consciousness does not imply free will (whatever that means) and is compatible with determinism. While I do believe this to be true, it is not a hard requirement for this experiment, but it makes the experiment simpler. At the end, I will go over two ways that the experiment could be modified to lead to the same conclusions even if non-determinism is true.

When I talk about a universe, I am talking about a closed system containing everything. If there are forces outside the universe not just with the potential to affect the things within, but actually affecting them, then by my definition, that is not a universe you are talking about. It would be a universe if you included those outside forces (and anything affecting them) into that universe.

When I say that "a universe contains consciousness," I will intentionally not strictly define what that means. I think your understanding of the phrase likely works with the thought experiment. If it doesn't, tell me how you understand this phrase.

Case 1:

We create a computer simulation of rules similar to those that seem to govern our universe. It has a starting state in its RAM, it applies rules and updates the RAM with the next state. We watch the simulation unfold and let's assume that we are lucky enough and eventually we recognize something very similar to human life and civilization develop in a little corner of it. Wars, love, philosophy, grief, hatred, arts, it's all there. I argue that this universe that we are simulating contains consciousness.

Case 2:

Same as case 1, but we stop and restart the experiment. Because of determinism, it will pass through the same states. I argue that this universe still contains consciousness.

Case 3:

Like case 2, but to speed things up in the second run, we use a huge lookup table as cache: During the first run, we record every state transition on a huge lookup table, with the current state as a key and the new state as a value. On the second run, we do not need to apply the actual rules of the universe anymore; we can just keep looking up the result of the calculations in our lookup table. The RAM passes through the exact same sequence of states, so I'd argue that this universe still contains consciousness. If you believe that actual calculation is required for consciousness to arise, consider that your CPU already uses microcode and lookup tables for many calculations, and some programming languages memoize results of stateless functions. So, even when you think you are calculating, you are actually using a lot of look-ups in caches and pre-computed tables - I'm just expanding this principle. A lookup is no less of an algorithm than any other algorithm that you run so philosophically I see no difference if the lookup brings the same results as the supposedly "proper" calculation.

Case 4:

Like case 3 but instead of keeping the cache as a lookup table with keys and values, we realize that each value is also a key, so we store the table in a compressed form where instead of:

a→b
b→c
c→d

We store:

a→b→c→d

We use that structure as a way to avoid doing the actual calculations. The RAM passes again through the exact same sequence of states, so I argue that this universe still contains consciousness.

Case 5:

Like case 4 but after the first run that populated the cached states, we went manually through all of them and replaced each cached_state with f(cached_state) where f is a reversible function (i.e., there is a 1-1 correspondence between each state and its transformation through f). Since no information is lost, even though the RAM passes through different states than the first run, those states are equivalent to the states of the first run and consciousness must still be contained in this universe. After all, if the computer used Big Endian or Little Endian or a different but equivalent floating point standard where the mantissa is stored before the exponent, it shouldn't really matter to the emulation. Indeed, our computer's current representation is just one of an immense number of possible representations - if changing to another representation could break consciousness, we should be equally concerned that we're already using the wrong one.

Case 6:

Like case 4 (not case 5) but we manually went through every state in the cache and removed the information describing a part of the universe far away from the civilization, one that the civilization never interacts with. I argue that since the RAM otherwise passes through states that show exactly the same civilization evolving, there is still consciousness in this universe, despite a far away part missing.

Case 7:

Like case 6, but the part of the universe that we exclude from the cached state is one that interacts with the civilization. Let's say we removed the moon of their planet. By "remove" I simply mean that the RAM no longer contains information about the moon itself, while the rest of the universe is intact as if the moon still existed: we still see bodies around where the moon should be being gravitationally attracted to it, we still see humans walking in the moonlight, we still see their moon missions going there and coming back with stones. We just have no part of the RAM in the simulation that actually contains the info of what is going on exactly there. In fact, when simulating physical interactions at a very low level, in some cases we may be able to ignore particles that we know cannot interact with other particles until some moment in the future, instead of calculating their position at every moment in time until that interaction. If the rules are elaborate enough to allow for this skip in time where individual particles do not get tracked for a while, what is the problem with coming up with an elaborate rule (the lookup table we created in case 3 is one such very elaborate rule) that allows us to calculate the evolution of a civilization on earth without actually keeping track of the moon frame by frame and only keeping track of the effects it has on earth? I argue that there is still consciousness in this universe.

Case 8:

Based on case 7 and case 5, I conclude that any transformation of the states of the cache that we initially introduced in case 4 does not remove the consciousness from this universe on the second run (the one utilizing the cache) as long as consciousness remains recognizable in the transformed states. If that is the case, then a kind of 2D video of the universe is also one such transformation as long as it shows enough of the civilization to keep consciousness recognizable, and therefore playing back that into the RAM during the second run will mean that this universe still contains consciousness. One might object that a 2D video loses too much information by collapsing a 3D world into a 2D projection. However, consider that instead of removing the moon, we could remove every odd cubic millimeter of the universe from our cached states. The lookup table was created from a full-scale simulation, so even though we're not storing the state of those odd cubic millimeters, their effects are still perfectly captured in how the rest of the universe evolves. If consciousness survives this kind of spatial sampling while the causality that produces it is maintained through our lookup table, then a 2D projection is just another way of encoding the same causal relationships while being selective about what detail we maintain in RAM.

Case 9:

Like case 1 (not case 8) but this time instead of keeping each state of the universe in the same position in the RAM, we don't really use a RAM, we use a write-once memory, so each state has to be written in adjacent locations that can never be erased. I argue that this matters not and is an implementation detail, the universe still contains consciousness.

Case 10:

Like case 9 but we rerun the simulation on the same write-once memory. Due to determinism it would attempt to write the same things in the same locations, but the data is already there so all write operations become no-ops. This brings us to a weird conclusion: A system where the only thing that changes is a register pointing to the current state of the universe (the "now" in the simulated universe) while everything else is static (no changes in the main memory) is still capable of simulating a universe that contains consciousness.

Case 11:

Like case 10 but since the "now" pointer is not really interacting with the universe simulation in a way that has measurable results, we move this pointer to a different computer, and just watch the two computers in operation, one computer containing all the states of this universe, while the other computer fetching from the first computer every state, recalculating the next state and throwing away the result. I do not see any functional difference so these two computers together are simulating a universe that contains consciousness.

Case 12:

Like case 11 but since the results of the calculations of one of the computers are thrown away, we might as well not calculate them. After all in case 3 we decided that not calculating stuff and just looking up the pre-calculated result in a cache is fine, so the calculation itself is not magical in any way. If you somehow knew the previous value of a memory location and chose to only flip the bits that needed to change, rather than erasing and rewriting all bits, you wouldn't say that consciousness was lost. Well, in this case we know that no bits need to be flipped at all, so skipping the calculation entirely is just another optimization. But now the computer with the stored states is equivalent to a tape, or a DVD, as these also contain all the states in sequence, separated physically in your universe. The computer holding the counting pointer is equivalent to a counter circuit or a clock.

Conclusion?

I have a DVD with a documentary, and I have a clock, and they are no more interacting with each other than the two computers in case 12, so... does that mean that the DVD, even when not played in a way creates or contains a universe that contains consciousness? One might object that the DVD wasn't created through the careful process of running a full universe simulation and creating a lookup table - it's just a recording of 2D projections of our universe. However, this objection leads to a paradox: If we actually did make a full simulation of another universe and then modified the lookup table to turn it into a DVD that happens to be bit-for-bit identical to an existing documentary DVD (no matter how improbable such a match would be), would one DVD contain consciousness while the other doesn't, based solely on how each was created?

Further implications:

Pushing even further from our DVD plus clock conclusion: we noted that the clock in case 12 doesn't actually interact with the data - it merely marks a theoretical progression through the states. But if the clock's interaction is purely theoretical then any potential way of traversing these states serves the same purpose. Even imagining the progression through these states would be equivalent, with your brain acting as an over-engineered clock/counter. This leads to an even stranger possibility: if we could imagine a projection that matches, state by state, what would be produced by the careful simulation process described earlier (however improbable such a match would be), would that imagined projection also contain consciousness, and does our imagination therefore create separate consciousness from our own?

Non-determinism:

If you consider determinism a limitation in the above experiment, there are two ways to incorporate non-determinism without changing the experiment's conclusions.

The easy way:

If all the non-deterministic random choices that will be taken by the universe could be included as a blob of data in the initial state of the universe, then non-deterministic universes could be treated as deterministic universes that include a "seed" for their randomness in their initial state, and that seed could be non-deterministic itself, as an inconsequential exception to our rule about universes never interacting with outside forces. After all, the rules of the simulated universe are decided by us, in our universe, so deciding a seed, deterministically or not, to make the simulated universe effectively non-deterministic is not breaking anything in the thought experiment.

The messy way:

If the simple solution doesn't satisfy you, perhaps we can consider all the possible paths that a simulated non-deterministic universe can take as a tree. We can still, in theory, calculate all of them and populate the cache of case 3. We can still convert the cache into a form of a branching, tree-like tape like the one in case 4. We can still modify the "frames" in this tape as described in cases 5 to 8. And we can still lay out the states physically separated in different memory locations instead of overwriting the same location as in case 9. In fact, it may even be more natural to lay them out in space depending on whether we chose depth first or breadth first or something in between when calculating (or looking up in the cache) the next state(s) of the simulated universe. In general, all the cases still apply just fine if the shape of the simulated timeline is a tree, and not really a line. And if at least one path from the root to the leaves contains a human-like civilization, I'd argue that the simulation contains consciousness.

Wednesday, November 13, 2024

Aperito v1.6.1

     Version 1.6.1 of Aperito fixes some typos in some error messages and makes Aperito recognize non-existing types of "keep" and properly fail for them, for example it will fail on "keep poop" but will not fail for "keep shallow" or "keep deep".

 The latest version can be downloaded from here, and the signature from here. Source code is included in the zipfile.

Friday, August 9, 2024

In defense of online console subscriptions

 Widely disliked to the point of being considered predatory, difficult to measure in terms of real cost, and one of the most common points of attack against consoles from PC gamers, paid online multiplayer services in consoles are what I will try to analyze and defend in this article.

Analyzing Nintendo Switch Online Subscription

First, to simplify our analysis, let's focus on the most basic of these subscriptions: The Nintendo Switch Online individual membership, costing $19.99 per year. It doesn't offer much in "extra" content, except for some old Game Boy games that you could easily play on an emulator and which likely cost Nintendo very little to provide. From those $19.99, we need to subtract the actual cost Nintendo incurs to maintain online game servers for you (one extra player). For a very rough estimate, you can rent a Counter-Strike 2 game server (at retail prices) for about $1 per player slot. Of course, you won't be playing just one game, but over time and across 38 million subscribers, the cost is averaged out, and the actual cost per player should be fairly close to that. That still leaves $18.99 from your subscription going directly to their pockets. "Outrageous!" you might say, "To pay $18.99 just for the privilege of playing online." Well, it's not that simple. Let's continue with the analysis.

The Present Value of Subscription Payments

There's a formula in finance that allows us to convert any future payment to a present payment, or even a series of future payments to a single present payment and vice versa.

If C is your future payment (say a single subscription payment), i is the yearly interest rate that Nintendo gets for their savings, and n is the number of years until you make that future payment, then that future payment has a present value (PV), (i.e., it is currently worth to Nintendo):


So, for example, if the yearly interest is 2%, when you start a subscription, the first payment (happening the same day) is worth, obviously, $19.99 to Nintendo at that moment in time. But your next-year payment is worth $19.60. In other words, Nintendo would not care if you want to pay another $19.99 next year, or just pay $39.59 the very first day and be done for two years. Similarly, your third year payment of $20 is worth an equivalent payment of $19.21 if you made it the first day of the first year. So you could pay $58.80 up-front and be done for three years; Nintendo would be unaffected either way.

The Real Cost of an Online-Capable Switch

Assuming a lifetime of 7 years for a Switch console, that would be 7 payments of $20, spaced out over that period. That's equivalent to an up-front payment of $131.96. With an introductory price of $299.99, that means that in effect, Nintendo is selling an offline-only Switch for $299.99 and an online-capable Switch for $431.95. They are just "forcing" those wanting the online version of the Switch to get a loan with 7 installments of $19.99 at 2% interest from Nintendo to pay those extra $131.96 for that version. This is probably done because it's easier to convince your parents to pay another $19.99 per year instead of getting you a $431.95 Switch, even though it's effectively the same cost, just structured differently.

Why Charge More for Online Play?

Where does that leave us? Why does Nintendo charge those who want to play online multiplayer $131.96 more? There are three answers that I can think of:

  1. They are simply copying Sony and Microsoft. I will not consider this as a real answer because it simply changes the question to "Why is Sony charging those who want the online version of PlayStation more?" which cannot be answered by pointing the finger at Nintendo again. We only picked Nintendo for the analysis because it was simpler, but my conclusions should more or less apply to all three consoles.
  2. They charge online players more because they are more "hardcore gamers" and are willing to spend more on their console than those who only play single-player games. That means Nintendo is earning pure profit from these players. We'll analyze this case further down.
  3. Online players are more focused on their favorite online games. A player without a subscription will play one single-player game after another, spending all their time exclusively on finishing games. A player interested enough to buy a subscription for online play is more likely to focus on one of the fewer online games, say Splatoon, and spend most of their time on it, leaving less room for finishing single-player games. I find this answer very likely to be true. Anecdotally, I've been mostly an online player for many years, and I definitely bought fewer games back then, while now I play mostly offline and buy more games, finishing (or dropping) one after another. If this is true in general and not just in my case, then Nintendo is likely charging more up-front to make up for that loss of future income. Often, console hardware itself is sold at a loss, so the way to make up for that loss is by selling games. But if your intention is to buy two games over 7 years (say Splatoon 2 and Splatoon 3), then obviously Nintendo can't have that.
  4. A combination of #2 and #3 could also be the real answer. Analyzing that is the same as analyzing #2, which we'll now do.

Potential Consequences of Banning Subscriptions

Let's assume then that Nintendo is overcharging online players. Is that outrageous? What happens with these profits? What would happen if they were somehow forced to reduce those profits? Imagine that a law gets passed that bans subscriptions for consoles. What now? How does Nintendo react to that? Any of the following, or a combination of them could happen:

  1. The price of the console itself could be increased. 38 million subscribers out of 143 million units sold very naively means that about 26.57% of the sold units were those hypothetical "online" units that effectively cost $431.95, and 73.43% are those offline-only units that cost $299.99. Now these two models are equalized, so the end result would be an online-capable unit for an introductory price of $335.06. The bad thing here is that we may have gone from a situation where the "hardcore online players" were subsidizing cheap consoles for the offline-only players, to a situation where the offline-only players are now subsidizing the costs of the online players.
  2. The price of all games could go up by whatever amount is needed to keep the profits the same as before. Again, the offline-only players would end up paying more in the long run, and the online players would end up paying less. If your subscription effectively costs $131.96 like we showed, paid by 38 out of every 143 users, and the average player purchases 6 games per year over 7 years, then that would be a $0.83 game price increase.
  3. The price of just the online games would go up by whatever amount is needed to keep the profits the same as before. If you are an average online player, then you'd see no difference in your wallet in the long run. If, as I suspect, the online players buy fewer games, say 3 games per year over 7 years, then that would be a $6.28 game price increase, only on the online games.
  4. Nintendo could invest less in research or game development in order to keep their profit the same as before. What this means for the next console or the next game is impossible to predict. It is safe to say that without the current level of R&D, the next console will be less capable and/or more expensive to manufacture.
  5. Nintendo could accept the loss of profit and make smaller dividend payments to their stockholders and/or have their stock decrease in value. The end result of that would be some amount of investment leaving the company and going elsewhere. Needless to say, this will not make their next console better or cheaper; quite the opposite.

Market Dynamics and Subscription Models

Of course, all of the above assume no player movement to or from other platforms. It is possible that the PC gaming market is starving for a new gaming machine with the payment structure that PC gamers are used to, i.e., a big up-front payment for the real cost of the hardware and the OS software, no subscriptions, and low game prices with little to no platform markups. In which case, the moment Nintendo decides to stop their subscription model, they get flooded with new players and don't need to do anything at all to make up for the loss of income.

But it's also possible that it's exactly the other way around: that the up-front cost is already too much for console players, that PC players will never buy a console just because the subscription model was abolished, and the market is actually starving for an even more subscription-based service. For example, remember that hypothetical $431.95 subscription-free online-capable Switch? Well, it's exactly equivalent to $65.43 yearly payments for 7 years with no up-front cost. Or even $5.51 monthly payments for 7 years, no up-front cost. And you can kind of do these things by buying the device with a credit card, though those have much higher interest rates than what we assumed so far.

Hell, you can get a PC effectively on exactly the same subscription model as a Switch. For example, buy this laptop:



and pay up-front $303.28 (about the same as the introductory price of a Switch), pay the other $65.21 with a credit card (23.76% effective yearly interest rate), and then make 7 yearly payments of $19.99 on that credit card.

My guess is that if there was space for gaining meaningful market share by dropping the subscription price or even getting rid of it completely, one of Sony, Nintendo, or Microsoft would have already done it. You'd expect that the first one to do it would immediately steal a good chunk from the other two, especially if they waited for one of the other two to announce a pricing change, like when Sony added two more higher tiers to their subscription service. It's also possible that while their market research may show an average improvement in profit margins if they abolish subscriptions, the standard deviation is too high, meaning there's a good chance of reducing their profits below some threshold that would force them to re-implement subscriptions, with unpredictable effects on their users' nerves and reputation. They would certainly look desperate if they were the only company that had to bring back subscriptions. My guess is that their market research shows the opposite: that moving even more of the cost of the console away from the games and hardware and into subscriptions makes more sense in the current market. At least Sony seems to think so, as they showed by recently adding higher tiers, and both Sony and Microsoft now provide games "for free" as long as you pay the subscription. It looks like this model is working for many people. And with Steam Deck and other PC handhelds on the market, Nintendo has even less reason now to try and capture gamers interested in paying up-front for hardware as long as later costs are low.

Comparing to the Commodore 64

To put things into perspective let's compare to one of the best selling computers ever, that was also widely used for gaming: A Commodore 64 would cost $395 to months after release. A floppy drive would about a year after release cost $300. That's a total of $2275.96 in today's money. A 21" CRT uses about 100W, the Commodore 64 uses 21W, for a total of 121W. At $0.076/KWh in 1982 and with 2 hours of gameplay per day, that's $6.71 per year or $22.36 in today's money, more than your yearly Nintendo subscription. Commodore 64 games typically cost $30 around 1984 (really hard to find original pricing information) which is $92.50 in today's money. A Switch uses 8W and a modern monitor of similar size uses 25W for a total of 33W. At 2 hours of gameplay and at $0.1643/KWh, that's $3.96 per year on top of your $19.99 subscription. Or if you play handheld, $0.96 per year on top of the subscription. So a Commodore 64 was 488% more expensive than a Switch, its games cost 54% more than those of the Switch, and the costs for playing it 2 hours per day were about 7% less than those of a Switch on a 21" monitor, or around 6% more expensive compared to a Switch played handheld. And it was not online.

 Commodore 64
(adj. for infl.)
Switch
(some adj. for infl.)
Hardware cost$2275.96$386.58
Game cost$92.50$60
730 hours of electricity cost$22.36$3.96 (on a monitor)
$0.96 (handheld)
Online multiplayer costN/A$19.99

Conclusion - Fix It Yourself

If you're still certain that Nintendo would actually increase their profit by offering the option to pay the full price up front, I recommend you put your money where your mouth is and offer that service yourself on the market, and become rich. Sell Switches with an add-on service: Your clients pay an extra $131.96 up-front, and you cover their Nintendo subscription costs for 7 years. You should be able to sell more consoles than anyone offering just the console.

Otherwise, don't fuss. As we saw, any up-front cost is equivalent to some recurring cost and vice versa, whether it's paid per game you buy, or per month, or per year. And there are more options than there ever were. But "don't fuss" doesn't mean don't ask console manufacturers to improve their offers. Just don't accuse them of not choosing the price structure of your choice. Convert everything to a present value and compare the different offers. Then use a credit card to move up-front payments to the future, or deposit money up-front in an account to bring future payments to the present.

Wednesday, April 24, 2024

Voyager Manager v0.1

I've written around seven programs for my HP 12c. I've manually combined them into a big "multiprogram" which starts with a jump table: a bunch of GTO commands, each one jumping to the entry point of one of the actual programs. The reason for that is that the HP 12c has only one contiguous program area. Fortunately, the calculator allows you to manually execute a GTO command before pressing R/S to start the execution. But instead of having to remember the starting address of each program, I just put them on the GTO instructions on the jump table so I just need to manually do GTO 003 and press R/S to start the third program, because address 003 has the GTO to the starting point of the third program. 

But managing this multiprogram is very tedious. When a program is placed in a different starting address in memory, all the GTO targets of that program need to be updated and offset correctly to continue working. So I made a program called Voyager Manager that lets me compile any number of programs into one such multiprogram, taking care of the GTOs and the jump table automatically. It works for my programs but it's still a very early version. It comes with GNU/GPLv3 source code, Lua binaries for Windows included, and a library that includes my programs for the HP 12c to get you started.

You can download Voyager Manager from here.

Popular Posts