[MariaDB discuss] Re: TCMalloc with Maria and THPs

[Gordan Bobic via discuss]

All of my environments always have THP disabled.

On Wed, May 8, 2024 at 3:38 PM Simon Avery
 wrote:
>
> Thank you Gordan.
>
> Do you recall if your testing environment had THP's enabled or disabled?
>
>
> -Original Message-
> From: Gordan Bobic via discuss 
> Sent: Wednesday, May 8, 2024 11:00 AM
> To: Simon Avery 
> Cc: discuss@lists.mariadb.org
> Subject: [MariaDB discuss] Re: TCMalloc with Maria and THPs
>
> FWIW, my own testing showed recently that tcmalloc provides a small but 
> measurable performance improvement over the standard allocator.
> jemalloc was slightly worse.
> The difference between the three is sufficiently small that I don't think 
> there is a compelling reason to use a non-standard allocator, unless you have 
> a very specific workload that measurably and repeatably benefits from one 
> over the others.
>
> On Wed, May 8, 2024 at 12:44 PM Simon Avery via discuss 
>  wrote:
> >
> > Hello
> >
> > I’m looking to test tcmalloc for use on our ~80 Maria 10.11.[6-7] servers, 
> > running either Rocky 8/9 or Debian 12.
> >
> > A few years ago we moved to using jemalloc which helped resolve or reduce 
> > many of the memory leaks on these servers from using the distro’s default 
> > malloc.  We still have a couple of machines where Maria eventually uses all 
> > the memory until oomkiller steps in – although we’ve largely mitigated that 
> > by adopting weekly restarts for them.
> >
> > Thanks to Sergei Golubchik for his most useful post here on the 5th on the 
> > subject of memory leaks, which has got us looking at tcmalloc and also 
> > raises the question of Transparent Huge Pages. Sergei cites several 
> > reliable sources recommending turning them off for MariaDb and similar 
> > databases.
> >
> >
> >
> > Now – my question;
> >
> > tcmalloc’s documentation on tuning at
> > https://google.github.io/tcmalloc/tuning.html
> >
> >
> >
> > says “TCMalloc heavily relies on Transparent Huge Pages (THP).” And 
> > explains it’s built and tested with THPs enabled and seems to strongly 
> > recommend leaving them on.
> >
> > That contradicts the above articles, leaving me confused about which path 
> > is best practice.
> >
> > It seems that THP’s should be on as advised by tcmalloc, but also off when 
> > running MariaDb.
> >
> > The true answer possibly depends on which has the highest priority in terms 
> > of database-specific performance and the ability to manage the memory 
> > tightly.
> >
> > What are others’ views, please?  I’m searching in particular for best 
> > practice and real world experiences with tcmalloc.  Also – if it’s unlikely 
> > to improve upon jemalloc.
> >
> >
> >
> > Simon Avery
> >
> > Linux Sysadmin: ATASS Sports
> >
> > Oxygen House | Grenadier Road, Exeter Business Park | EX1 3LH
> >
> > t: 01392 440 400
> >
> > e: simon.av...@atass-sports.co.uk
> >
> >
> >
> > www.atass-sports.co.uk
> >
> > Follow us on X: @atassSports
> >
> >
> >
> > NOTICE
> > This email and any attachments confidential and intended solely for
> > the use of the individual to whom it is addressed. If you are not the
> > intended recipient be advised that you have received this email in
> > error and that any use, dissemination, forwarding, printing or copying
> > of this email is strictly prohibited. Please notify the sender
> > immediately. ATASS Ltd is incorporated in England and Wales with
> > company number 04807405. See our website for further details and our
> > privacy policy
> >
> >
> >
> > ___
> > discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send
> > an email to discuss-le...@lists.mariadb.org
> ___
> discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an 
> email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: TCMalloc with Maria and THPs

[Gordan Bobic via discuss]

FWIW, my own testing showed recently that tcmalloc provides a small
but measurable performance improvement over the standard allocator.
jemalloc was slightly worse.
The difference between the three is sufficiently small that I don't
think there is a compelling reason to use a non-standard allocator,
unless you have a very specific workload that measurably and
repeatably benefits from one over the others.

On Wed, May 8, 2024 at 12:44 PM Simon Avery via discuss
 wrote:
>
> Hello
>
> I’m looking to test tcmalloc for use on our ~80 Maria 10.11.[6-7] servers, 
> running either Rocky 8/9 or Debian 12.
>
> A few years ago we moved to using jemalloc which helped resolve or reduce 
> many of the memory leaks on these servers from using the distro’s default 
> malloc.  We still have a couple of machines where Maria eventually uses all 
> the memory until oomkiller steps in – although we’ve largely mitigated that 
> by adopting weekly restarts for them.
>
> Thanks to Sergei Golubchik for his most useful post here on the 5th on the 
> subject of memory leaks, which has got us looking at tcmalloc and also raises 
> the question of Transparent Huge Pages. Sergei cites several reliable sources 
> recommending turning them off for MariaDb and similar databases.
>
>
>
> Now – my question;
>
> tcmalloc’s documentation on tuning at 
> https://google.github.io/tcmalloc/tuning.html
>
>
>
> says “TCMalloc heavily relies on Transparent Huge Pages (THP).” And explains 
> it’s built and tested with THPs enabled and seems to strongly recommend 
> leaving them on.
>
> That contradicts the above articles, leaving me confused about which path is 
> best practice.
>
> It seems that THP’s should be on as advised by tcmalloc, but also off when 
> running MariaDb.
>
> The true answer possibly depends on which has the highest priority in terms 
> of database-specific performance and the ability to manage the memory tightly.
>
> What are others’ views, please?  I’m searching in particular for best 
> practice and real world experiences with tcmalloc.  Also – if it’s unlikely 
> to improve upon jemalloc.
>
>
>
> Simon Avery
>
> Linux Sysadmin: ATASS Sports
>
> Oxygen House | Grenadier Road, Exeter Business Park | EX1 3LH
>
> t: 01392 440 400
>
> e: simon.av...@atass-sports.co.uk
>
>
>
> www.atass-sports.co.uk
>
> Follow us on X: @atassSports
>
>
>
> NOTICE
> This email and any attachments confidential and intended solely for the use 
> of the individual to whom it is addressed. If you are not the intended 
> recipient be advised that you have received this email in error and that any 
> use, dissemination, forwarding, printing or copying of this email is strictly 
> prohibited. Please notify the sender immediately. ATASS Ltd is incorporated 
> in England and Wales with company number 04807405. See our website for 
> further details and our privacy policy
>
>
>
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Possible Security Bug

[Gordan Bobic via discuss]

Many thanks.

On Sun, Mar 24, 2024 at 4:35 PM Sergei Golubchik  wrote:
>
> Hi, Gordan,
>
> mariadb.org has a standard https://mariadb.org/.well-known/security.txt
> page, that contains:
>
> Contact: https://hackerone.com/mariadb
> Contact: secur...@mariadb.org
> Encryption: https://mariadb.org/security_at_mariadb_org.asc
> Acknowledgements: https://hackerone.com/mariadb/thanks
> Policy: https://mariadb.org/about/security-policy/
> Signature: https://mariadb.org/.well-known/security.txt.sig
> Release Packages Signature: 
> https://mariadb.org/mariadb_release_signing_key.asc
>
> In other words, you are advised to use either hackerone or
> secur...@mariadb.org email.
>
> Regards,
> Sergei
> Chief Architect, MariaDB Server
> and secur...@mariadb.org
>
> On Mar 24, Gordan Bobic via discuss wrote:
> >
> > What is an appropriate forum for discussing what seems to be a
> > security bug that I ran into?
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Possible Security Bug

[Gordan Bobic via discuss]

Hi,

What is an appropriate forum for discussing what seems to be a
security bug that I ran into?
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: MariaDB's Percona Tookit Fork Source Code Repository?

[Gordan Bobic via discuss]

Many thanks.

On Wed, Mar 13, 2024 at 3:08 PM Daniel Bartholomew  wrote:
>
> On Mon, 11 Mar 2024 16:04:09 +0200
> Gordan Bobic via discuss  wrote:
>
> > That repository doesn't seem to contain any of the tools listed here:
> >
> > https://mariadb.com/docs/server/ref/tools6.0/cli/
> >
> > Where can those be found?
>
> They're no longer being developed/worked on but the final release of the
> mariadb-tools package can be found at:
>
> https://downloads.mariadb.com/Tools/rhel/8/x86_64/rpms/mariadb-tools-6.0.2.2-1.x86_64.rpm
>
> https://downloads.mariadb.com/Tools/apt/pool/main/m/mariadb-tools/mariadb-tools_6.0.2.2-1_amd64.deb
>
> Thanks.
>
> --
> Daniel Bartholomew, MariaDB Documentation & Release Manager
> MariaDB | https://mariadb.com
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: MariaDB's Percona Tookit Fork Source Code Repository?

[Gordan Bobic via discuss]

That repository doesn't seem to contain any of the tools listed here:

https://mariadb.com/docs/server/ref/tools6.0/cli/

Where can those be found?

On Mon, Mar 11, 2024 at 3:40 PM Ian Gilfillan via discuss
 wrote:
>
> The first link in that page is working - the other links were added more 
> recently and you're correct, they no longer work, so I've removed those.
>
> On Mon, Mar 11, 2024 at 3:24 PM Gordan Bobic via discuss 
>  wrote:
>>
>> Hi,
>>
>> I noticed references to these tools:
>> https://mariadb.com/kb/en/mariadb-tools/
>> but the github links are all 404.
>>
>> Where can all of the sources for these be downloaded from?
>> ___
>> discuss mailing list -- discuss@lists.mariadb.org
>> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] MariaDB's Percona Tookit Fork Source Code Repository?

[Gordan Bobic via discuss]

Hi,

I noticed references to these tools:
https://mariadb.com/kb/en/mariadb-tools/
but the github links are all 404.

Where can all of the sources for these be downloaded from?
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Back again for more help

[Gordan Bobic via discuss]

Your options are:

1) Skip the transaction and hope for the best
2) Verify that the data is the same on both sides (look at
pt-table-checksum and pt-table-sync from percona toolkit
3) Re-seed the slave from scratch


On Sat, Feb 17, 2024 at 3:49 PM Ken Wright via discuss
 wrote:
>
> Now that you kind people have pointed me in the right direction as far
> as log-bin was concerned, I've moved on to trying to replicate the
> databases from the primary to the secondary.
>
> I've created the connection profile and started the secondary
> connection, but when I ran
>
> show slave 'master01' status\G
>
> I got the following error:
>
> Last_Errno: 1062
> Last_Error: Could not execute Write_rows_v1 event on table
> databasename.oc_filecache; Duplicate entry '110' for key 'PRIMARY',
> Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's
> master log master-bin.0#, end_log_pos ##
>
> How do I resolve this duplicate entry error?  And do I need to do the
> same resolution on the primary as well?
>
> Ubuntu Server 23.10, mariadb version 15.1 Distrib 10.11.6-MariaDB
>
> Ken
> --
> If you ever think international affairs make sense, remember this:
> because a Serb shot an Austrian in Bosnia, Germany invaded Belgium.
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Slow log and processlist.memory_used

[Gordan Bobic via discuss]

In processlist, there is a column called memory_used.
Is there a way to make the peak value for a query get logged into slow log?
Failing that, does the performance_schema provide any such info for
previously run statements to go with statement digests?

Or is the only place where this can be found in the
information_schema.processlist table while the query is running?
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Replication Speed / Lag 10.3.31 vs 10.3.39

[Gordan Bobic via discuss]

On Sun, 14 Jan 2024, 04:39 Michael Caplan via discuss, <
discuss@lists.mariadb.org> wrote:

> Hello,
>
> I'm trying to troubleshoot a performance a replication issue between two
> version of MariaDB.  I know that we are using two EOL versions, but I want
> to rule out all issues before prioritizing migrating to a new version.
>
> I have built a new dedicated replication server (B) to replace the current
> dedicated replica (A).  A and B are both running identical OSes and
> versions and near identical hardware in a RAID 10 configuration.  The main
> difference is that B has newer disks that slightly outperform A.  I
> benchmarked both A and B to validate this.  Both A and B are configured
> identically (other then server-id).
>
> Replica A (and primary)  is running MariaDB 10.3.31.  Replica B is
> running  10.3.39.
>
> A few other things to note:
>
>- B is not feeding off of the primary, but A -- that is until we
>sunset A.
>- A is handing moderate read only traffic
>- B is just running as a replica with no other responsibilities
>
>
> I have external monitoring to alert when either hits 500+
> Seconds_Behind_Master.  Since standing B up, I am seeing it fall behind a
> few times a day.   The interesting thing is that A is not falling behind
> from the primary.
>
> Upon further examination, a scheduled job that executes hundreds of
> similar delete operations off the same table (that has approx. 3 million
> records) is where B is largely getting hung up.  Something like this:
>
> DELETE FROM table WHERE x = 80 AND y = 4
>
>
> The table has an index on just x.
>
> Running *ANALYZE FORMAT=JSON SELECT * FROM table WHERE x = 80 AND y =
> 4*  from B show a striking difference in total time spent:
>
> REPLICA B:
> {
>   "query_block": {
> "select_id": 1,
> "r_loops": 1,
> *"r_total_time_ms": 490.13,*
> "table": {
>   "table_name": "table",
>   "access_type": "ref",
>   "possible_keys": ["table_index"],
>   "key": "table_index",
>   "key_length": "4",
>   "used_key_parts": ["x"],
>   "ref": ["const"],
>   "r_loops": 1,
>   "rows": 79,
>   "r_rows": 31,
>   "r_total_time_ms": 490.08,
>   "filtered": 100,
>   "r_filtered": 100,
>   "index_condition": "table.y = '4'"
> }
>   }
> }
>
> vs:
>
> REPLICA A:
> {
>   "query_block": {
> "select_id": 1,
> "r_loops": 1,
> *"r_total_time_ms": 106.37,*
> "table": {
>   "table_name": "table",
>   "access_type": "ref",
>   "possible_keys": ["table_index"],
>   "key": "table_index",
>   "key_length": "4",
>   "used_key_parts": ["x"],
>   "ref": ["const"],
>   "r_loops": 1,
>   "rows": 1146482,
>   "r_rows": 31,
>   "r_total_time_ms": 106.34,
>   "filtered": 100,
>   "r_filtered": 100,
>   "index_condition": "table.y = '4'"
> }
>   }
> }
>
>
> That is a *significant* difference performance wise.  From a hardware
> point of view, B should have a slight edge over A.  But the contrary is
> true by a large margin.
>
>
> As a short term "fix", I added a secondary index: *create index
> table_index2 on table(x, y);   *This significantly speeds up this
> operation, and, for now, seems to deal with much of the experienced
> replication lab in B:
>
> SERVER B:
> {
>   "query_block": {
> "select_id": 1,
> "r_loops": 1,
> *"r_total_time_ms": 0.253,*
> "table": {
>   "table_name": "table",
>   "access_type": "ref",
>   "possible_keys": [
> "table_index",
> "table_index2"
>   ],
>   "key": "table_index2,
>   "key_length": "8",
>   "used_key_parts": ["x", "y"],
>   "ref": ["const", "const"],
>   "r_loops": 1,
>   "rows": 31,
>   "r_rows": 31,
>   "r_total_time_ms": 0.2037,
>   "filtered": 100,
>   "r_filtered": 100
> }
>   }
> }
>
>
> This seems reasonable as a *general optimization*, but provides little
> comfort in understanding the root cause of my performance issue with B.
> I'm hesitant to retire A in favor of B until I can get to the bottom of
> this.
>
> The question I have for the community is: *what would you recommend that
> I do to get further perspective on the issue to determine a course of
> action?*
>

Give the explain uses the same index on both, cold buffer pool could be one
possible explanation.

Did you make sure configuration is the same on both servers, particularly
innodb_buffer_pool% and innodb_log% settings?

Otherwise:

- Use binlog_format=ROW on all servers

- Make sure all your tables have primary keys defined

- It sounds like you should have that secondary index anyway, on all the
servers

- if a lot of rows match, use pt-archiver instead, which will delete
matching rows in small batches by primary keys, which will minimize lag.

- increase slave_parallel_threads (but not too high)

>
___
discuss mailing list -- discuss@lists.mariadb.org
To 

[MariaDB discuss] Re: Repeatable read anomalies

[Gordan Bobic via discuss]

On Mon, Jan 1, 2024 at 10:55 AM Guillaume Lefranc via discuss
 wrote:

> MySQL and derivatives are DB systems which are used mainly by websites, small 
> or big. Although I did have some customers back in the day at MariaDB in 
> payment systems but they are generally aware of the complexity and are able 
> to retrace any errors, financial institutions will often use commercial 
> database systems and/or have some transaction processing and checking in 
> place.

That may have been true 20 years ago, but I don't think it is still
true today. Over the past decade I have worked with many financial
institutions who use MySQL and MariaDB in production environments to
handle all kinds of non-website workloads, including payment
processing.
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Redo log and tablespace flushing

[Gordan Bobic via discuss]

On Sun, Nov 19, 2023 at 11:36 PM Kristian Nielsen
 wrote:
>
> Gordan Bobic via discuss  writes:
>
> > That would still leave the edge case of a few seconds after it does
> > eventually write the checkpoint, would it not? I am effectively
> > looking at a case of "never write a checkpoint".
>
> Yes. I'm thinking that Marko's suggestion to clear the newest checkpoint
> (not both of them) would eliminate this edge case (with all of the caveats
> that Marko mentioned).

Which one is the more recent one? The first or second? If establishing
which is more recent requires reading it, how do I parse these blocks
and what am I looking for?

> > The objective is to gain a bit of performance on the master node where
> > being a few seconds behind after a dirty shutdown is not a palatable
> > option.
>
> But doesn't InnoDB already handle this by itself? That's the whole point of
> the write-ahead log and checkpointing. Database operations only need to wait
> for the durable write of redo log records. Durable write of buffer pool
> pages happens in the background, nothing needs to wait for it (except
> checkpointing, which shouldn't stall things as long as the redo log is
> sufficiently large). So where do you gain the performance with this idea?

By removing the flushing overhead from the tablespace path and
offloading that overhead to the storage back end.
The difference may be relatively small, but it seems there is some
improvement that could be had by just doing a heavily write-cached
offload to the order-preserving back end.

> Don't get me wrong, I think it's cool to push performace to the limits (and
> beyond). I'm just curious what the mechanism would be that would make this
> increase performance over what the write-ahead log / checkpointing already
> achieves. What is the storage you're using, how does it improve the page
> flushing internally over what InnoDB itself can achieve?

ZFS - it preserves write ordering (based on the flushing calls it
receives), and if we can run datadir with sync=disabled and only
ib_logfile* and binlogs on a path with sync=standard, it should
provide some improvement, e.g. by making those writes asynchronously
performed in the background, we could turn up the compression without
anything having to wait the extra time for these flushes and
potentially causing a stall.

I'll admit this is a somewhat obscure case of relying on side-effects
- where I still need InnoDB to emit all of the fsync() calls but am
actively proposing to lie to InnoDB about it in hope that the redo log
replay will save me from a dirty shutdown because data loss on the
back end is limited to seconds while WAL is sized to absorb tens of
minutes of writes.
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Redo log and tablespace flushing

[Gordan Bobic via discuss]

On Sun, Nov 19, 2023 at 5:23 PM Kristian Nielsen
 wrote:
>
> Gordan Bobic via discuss  writes:
>
> > Thanks for this. Is there a way to force replay of the entire redo log on
> > an unclean shutdown even if the checkpoint in the redo log says it was
> > flushed to tablespace?
>
> This won't help you if the part of the redo log you need was overwritten by
> new records due to the cyclic nature of the redo log.

I am working here based on the assumption that the time taken to
overwrite the circular buffer (typically sized to absorb the peak
daily hour of writes) is going to be vastly greater than the amount of
data that could be lost to lying about tablespace sync (~5 seconds).
So unless something spectacularly anomalous happens, I think there
should be plenty of margin for error there.

> > I'm exploring the idea of running datadir on storage that preserves write
> > ordering but runs with the equivalent of nobarrier. It will still flush in
> > the background every X seconds where X is configurable, so I am hoping to
> > use the redo log to keep my data crash-safe even though I am lying about
> > tablespace write flushes, because write ordering will be preserved despite
> > running with the equivalent of nobarrier.
>
> If write-ordering is preserved (but it has to preserved between log writes
> and data writes as well), then you will be crash-safe, because the situation
> will be the same as if a full-durable system crashed X seconds ago. You will
> lose the last X seconds of commit, but data will be consistent, similar to
> --innodb-flush-log-at-trx-commit=2 (or 0).

Yes, I already do this on the slaves
(innodb_flush_log_at_trx_commit=1, sync_master_info=1) with storage
that preserves write ordering but lies about having committed to
stable storage. That part of the setup is pretty bulletproof. Slaves
just restart replicating from a point a few seconds before and
everything is consistent.

> What goal are you trying to achieve here? Some performance gains, or the
> ability to use main storage with some non-standard write semantics?

The objective is to gain a bit of performance on the master node where
being a few seconds behind after a dirty shutdown is not a palatable
option.

> You can configure InnoDB to have a huge redo log and perhaps there are also
> some options to reduce the frequency of checkpoints.

Well, the traditional rule of thumb has been to size the redo log to
absorb the daily peak hour of writes.
I [refer to tune it to be sized so that checkpoint age never gets too
close to the limit (log size).
Unfortunately, the latter option is impossible with the redo log
checkpointing changes since 10.5+ (it never flushes anything at all
until it reaches the high water mark), but that's for a different
conversation thread.

> That should in practice
> avoid the problem with needed redo log being overwritten.

That would still leave the edge case of a few seconds after it does
eventually write the checkpoint, would it not? I am effectively
looking at a case of "never write a checkpoint".

> But it's obviously
> not something that InnoDB was designed to support.

I don't go down rabbit holes like this because it's easy and everybody
does it. :-)
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Redo log and tablespace flushing

[Gordan Bobic via discuss]

On Sun, Nov 19, 2023 at 3:42 PM Marko Mäkelä  wrote:
>
> > Thanks for this. Is there a way to force replay of the entire redo log on 
> > an unclean shutdown even if the checkpoint in the redo log says it was 
> > flushed to tablespace?
>
> You can overwrite the newer checkpoint block, so that recovery is
> forced to use the older one. Before MariaDB 10.8, the two checkpoint
> blocks are 512 (0x200) bytes starting at ib_logfile0 offset 0x200 and
> 0x600. Starting with 10.8, the checkpoint blocks are 64 bytes starting
> at ib_logfile0 offset 0x1000 and 0x2000. Obviously, do not try this on
> any important data, or experiment on a copy of the data. It is
> possible that the recovery will fail in various ways if the section of
> the log between the older checkpoint and the logical end of the log
> has been overwritten. The InnoDB WAL file is cyclic: checkpoints
> "truncate" the head and the tail (new log records) is not supposed to
> overwrite the head. If you are moving the head backwards by discarding
> the latest checkpoint, there will be no guarantee that no overwrite
> took place.
>
> Another way to experiment would be to run mariadb-backup --backup
> while a server is executing a write heavy workload. When you --prepare
> the backup, it will start from the LSN of the checkpoint that was the
> latest when the backup started. When the backup finishes, the server’s
> log file may already be several checkpoints ahead of the backup.

I think what I'm looking for is an option to ignore checkpoints, scan
the entire redo log and replay everything from lowest to highest
available LSN.
From what you are saying, if I zero out
bytes 512-1023 and bytes 1536-2047
That will force a full log scan / replay? Did I understand that correctly?

> > I'm exploring the idea of running datadir on storage that preserves write 
> > ordering but runs with the equivalent of nobarrier. It will still flush in 
> > the background every X seconds where X is configurable, so I am hoping to 
> > use the redo log to keep my data crash-safe even though I am lying about 
> > tablespace write flushes, because write ordering will be preserved despite 
> > running with the equivalent of nobarrier.
>
> I can't comment much on that. It could be a good idea to execute some
> kind of "pull the plug" testing during a write workload. Perhaps that
> could be arranged more easily in a virtualized environment.

Yes, obviously this would need some extreme testing, that goes without
saying. I just wanted to make sure my idea wasn't outright retarded
before I went down this particular rabbit hole.
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Redo log and tablespace flushing

[Gordan Bobic via discuss]

Thanks for this. Is there a way to force replay of the entire redo log on
an unclean shutdown even if the checkpoint in the redo log says it was
flushed to tablespace?

I'm exploring the idea of running datadir on storage that preserves write
ordering but runs with the equivalent of nobarrier. It will still flush in
the background every X seconds where X is configurable, so I am hoping to
use the redo log to keep my data crash-safe even though I am lying about
tablespace write flushes, because write ordering will be preserved despite
running with the equivalent of nobarrier.



On Sat, 18 Nov 2023, 22:13 Marko Mäkelä via discuss, <
discuss@lists.mariadb.org> wrote:

> Hi Kristian,
>
> Thank you for your excellent reply. I thought that some additional
> details might be worth mentioning.
>
> On Sat, Nov 18, 2023 at 8:03 PM Kristian Nielsen via discuss
>  wrote:
> > The redo log is of finite size, and cycles. InnoDB regularly does a
> > checkpoint, to ensure that all tablespace data up to a certain point has
> > been durably written.
>
> The writes to each data file must be made durable by fdatasync() or
> fsync() before the log checkpoint can be advanced. There are two
> checkpoint headers near the start of ib_logfile0, for remembering the
> last 2 checkpoint LSNs and the corresponding log file offsets.
> Recovery or mariadb-backup --backup will choose the larger checkpoint
> LSN as the starting point.
>
> One more aspect to crash recovery is the InnoDB doublewrite buffer,
> which protects against torn page writes. When it is enabled, any data
> page writes would first be written to the doublewrite buffer (128
> pages in the InnoDB system tablespace), and upon write completion, to
> the final destination. In that way, if the process is killed during
> the "main" write, it should be possible to find an intact version of
> the page in the doublewrite buffer. This buffer is not used by
> mariadb-backup; it would simply retry reading pages when it encounters
> a checksum mismatch.
>
> Marko
> --
> Marko Mäkelä, Lead Developer InnoDB
> MariaDB plc
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Redo log and tablespace flushing

[Gordan Bobic via discuss]

I have a question that may seem somewhat obscure, but what I really
want to know is how the disk flushing and crash safety work.

Do tablespace commits get explicitly flushed during normal runtime operation?

If we have a write that successfully commits to the redo log and to
the binlog, but the tablespace loses, say, 5 seconds worth of commits
in an unclean shutdown, would crash recovery deal with it? Is
replaying the redo log followed by binlog based recovery sufficient to
put the tablespace(s) into a consistent state even if the redo+binary
logs are in terms of on-disk state a few seconds ahead of the
tablespaces?

On other words, provided that write ordering is preserved (ordering as
guided by flush calls), can I do the equivalent of
LD_PRELOAD=libeatmydata on the tablespace operations safely as long as
the redo and binary logs are fsync()-ed reliably?
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Speeding up a query: how to send 3 million rows faster?

[Gordan Bobic via discuss]

I have to say that surprises me. MyISAM became slower even on
read-only workloads around MySQL 5. One of the reasons was that MyISAM
uses the OS page cache for data caching and only caches index data
internally. Which means that unless you have a covering index, you end
up with more context switching to retrieve the row data.
But maybe InnoDB has since been "enhanced" to the point where it is no
longer outright faster on every workload.

On Wed, Nov 1, 2023 at 12:14 PM Ivan Krylov via discuss
 wrote:
>
> В Tue, 31 Oct 2023 11:45:52 +0200
> Vassilis Virvilis  пишет:
>
> > If you change the table format to MyISAM do you get better
> > performance?
>
> I almost missed your message, and that would have been a shame, because
> if I change the table format to MyISAM, I get the query results in ~3.5
> seconds, which is faster than any other result I've been getting with
> MariaDB. Many thanks!
>
> This is the largest table in our database, but it's also the easiest to
> recreate, so we might just switch it to MyISAM with no apparent
> downsides.
>
> --
> Best regards,
> Ivan
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Speeding up a query: how to send 3 million rows faster?

[Gordan Bobic via discuss]

I would say that is definitely worthy of a bug report. A really
interesting bug, too.

On Tue, Oct 31, 2023 at 1:15 PM Ivan Krylov via discuss
 wrote:
>
> To summarise:
>
>  * "Sending data" is a red herring. MariaDB sets stage_sending_data
>before calling do_select(), which may perform a lot of additional
>work _besides_ serialising and sending data to the client.
>
>  * Various sacrifices to the query optimiser spirit, such as creating
>an additional synthetic primary key column, can be made, but there
>is no good reason for them to be working. InnoDB has a perfectly
>good hidden rowid that it should be able to use.
>
>  * There is a difference between a query plan that seems to
>automatically use an index and a query plan that is _forced_ to use
>an index:
>
> MariaDB [spmodel]> analyze select
> ->  mtr.prob, mtr.lower_id, mtr.upper_id
> -> from mol_trans mtr
> -> where (
> ->  mtr.species_id=6115
> ->  and mtr.wl_vac > 766.0
> ->  and mtr.wl_vac < 883.0
> ->  and mtr.flag = 1
> -> )
> -> order by mtr.wl_vac\G
> *** 1. row ***
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: ref
> possible_keys: spid_flag_wl
>   key: spid_flag_wl
>   key_len: 7
>   ref: const,const
>  rows: 14025100
>r_rows: 28417908.00
>  filtered: 100.00
>r_filtered: 10.64
> Extra: Using where
> 1 row in set (1 min 48,719 sec)
>
> MariaDB [spmodel]> analyze select
> ->  mtr.prob, mtr.lower_id, mtr.upper_id
> -> from mol_trans mtr
> -> force index(spid_flag_wl) -- The only difference!
> -> where (
> ->  mtr.species_id=6115
> ->  and mtr.wl_vac > 766.0
> ->  and mtr.wl_vac < 883.0
> ->  and mtr.flag = 1
> -> )
> -> order by mtr.wl_vac\G
> *** 1. row ***
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: range
> possible_keys: spid_flag_wl
>   key: spid_flag_wl
>   key_len: 16
>   ref: NULL
>  rows: 6260712
>r_rows: 3024559.00
>  filtered: 100.00
>r_filtered: 100.00
> Extra: Using where
> 1 row in set (11,086 sec)
>
>The latter can be much faster than the former. Since FORCE INDEX
>improves the performance of this query on everything ranging from
>MariaDB-10.3.39-0+deb10u1 to MySQL 5.6 on Windows 10, we'll be using
>this.
>
>  * Is any of this worth reporting as a query optimiser bug? The
>contents of the table are the result of preprocessing a CC-BY-SA-4.0
>dataset, so the only thing stopping me from reporting this is the
>dump being 2 GB in size. My perf-fu is probably not enough to find
>the source of the problem (if it is a problem) by myself.
>
>  * The last but not the least, thanks to Gordan Bobic for valuable
>advice!
>
> --
> Best regards,
> Ivan
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Speeding up a query: how to send 3 million rows faster?

[Gordan Bobic via discuss]

10.3 was a really good, solid, predictable release. Beyond 10.4 I have
seen a lot of weirdness which is why I have been avoiding it wherever
reasonably possible.
Additionally, version inflation on the whole leads to performance
deflation, even without execution plan differences. This has been the
case since the dawn of time.
So if 10.3 works well for you and it isn't in an untrusted
environment, I would stick with it while you can.

On Mon, Oct 30, 2023 at 1:29 PM Ivan Krylov  wrote:
>
> On Mon, 30 Oct 2023 08:10:02 +0200
> Gordan Bobic  wrote:
>
> > That is really weird. Just having a visible vs. invisible PK should
> > not have made any difference at all. In InnoDB there is always a PK,
> > if you don't define one, an invisible 48-bit integer one will be
> > defined for you.
>
> Thank you for confirming this!
>
> The strangeness continues. I've built MariaDB-11.1.2 and transferred
> the SQL dump of the table in question there. With the original table
> definition, I get the following query plan:
>
> CREATE TABLE `mol_trans` (
>   `species_id` int(11) DEFAULT NULL,
>   `wl_vac` double DEFAULT NULL,
>   `upper_id` int(11) DEFAULT NULL,
>   `lower_id` int(11) DEFAULT NULL,
>   `prob` double DEFAULT NULL,
>   `flag` tinyint(4) DEFAULT NULL,
>   KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
>  PARTITION BY LIST (`species_id`)
> (PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB,
>  PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB,
>  PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB,
>  PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB);
>
> explain select
>  mtr.prob, mtr.lower_id, mtr.upper_id
> from mol_trans mtr
> where (
>  mtr.species_id=6115
>  and mtr.wl_vac > 766.0
>  and mtr.wl_vac < 883.0
>  and mtr.flag = 1
> )
> order by mtr.wl_vac\G
>
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: ALL
> possible_keys: spid_flag_wl
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 26559953
> Extra: Using where; Using filesort
>
> Note that it doesn't even use the index now. This SELECT takes upwards
> of 1 minute to complete.
>
> If I remove the partitions *and* add an explicit synthetic primary key,
> I get a query plan that uses the index:
>
> CREATE TABLE `mol_trans_3` (
>   `species_id` int(11) NOT NULL,
>   `wl_vac` double DEFAULT NULL,
>   `upper_id` int(11) DEFAULT NULL,
>   `lower_id` int(11) DEFAULT NULL,
>   `prob` double DEFAULT NULL,
>   `flag` tinyint(4) DEFAULT NULL,
>   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>   PRIMARY KEY (`id`),
>   KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
> ) ENGINE=InnoDB AUTO_INCREMENT=81305699 DEFAULT CHARSET=utf8mb3
> COLLATE=utf8mb3_general_ci
>
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: range
> possible_keys: spid_flag_wl
>   key: spid_flag_wl
>   key_len: 15
>   ref: NULL
>  rows: 6083086
> Extra: Using index condition
>
> This query just executed on my machine in 21 seconds.
>
> Finally, if I go back to the original table and force the use of the
> index, I get a third query plan:
>
> explain select
>  mtr.prob, mtr.lower_id, mtr.upper_id
> from mol_trans mtr force index (spid_flag_wl)
> where (
>  mtr.species_id=6115
>  and mtr.wl_vac > 766.0
>  and mtr.wl_vac < 883.0
>  and mtr.flag = 1
> )
> order by mtr.wl_vac\G
>
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: range
> possible_keys: spid_flag_wl
>   key: spid_flag_wl
>   key_len: 16
>   ref: NULL
>  rows: 6266474
> Extra: Using where
>
> ...and the query now completes in 28 seconds.
>
> ANALYZE TABLE mol_trans PERSISTENT FOR ALL doesn't seem to lead to
> further improvements. Anything else I could do to speed this up?
>
> With MariaDB-10.3.39-0+deb10u1, I somehow get the result in 10-20
> seconds using the mol_trans_3 table, no matter whether the query
> specifies FORCE INDEX or not.
>
> --
> Best regards,
> Ivan
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Speeding up a query: how to send 3 million rows faster?

[Gordan Bobic via discuss]

That is really weird. Just having a visible vs. invisible PK should not
have made any difference at all. In InnoDB there is always a PK, if you
don't define one, an invisible 48-bit integer one will be defined for you.

Reasoning behind my approach was that secondary keys point at the PK, and
the table is clustered in PK. So what I proposed should have avoided the
additional key dereference that might have save about half of the time.

So if explicit vs. implicit PK makes a 10x difference, and it is not due to
buffer pool being hot vs. cold, that is most definitely a critical
performance bug you just stumbled upon.


On Sun, 29 Oct 2023, 21:08 Ivan Krylov via discuss, <
discuss@lists.mariadb.org> wrote:

> On Sun, 29 Oct 2023 18:12:53 +0200
> Gordan Bobic  wrote:
>
> > ALTER TABLE mol_trans
> > DROP INDEX spid_flag_wl,
> > ADD COLUMN id int unsigned auto_increment,
> > ADD PRIMARY KEY (species_id, flag, wl_vac, id);
>
> I wasn't able to add a primary key like this. My version of MariaDB
> only allows the id column in the beginning of the compound primary key.
> Additionally, trying to add a primary key to an existing table results
> in an error message complaining about the index for the table being
> corrupted (and rolling back the implicit transaction).
>
> I tried recreating the table with PRIMARY KEY (species_id, flag,
> wl_vac, upper_id, lower_id). By itself, the combination of upper_id and
> lower_id must be unique, and it's our mistake that we didn't properly
> declare them as foreign keys into a different table. Unfortunately,
> this didn't improve the performance.
>
> What _did_ improve performance was creating the id column with the type
> INT UNSIGNED AUTO_INCREMENT and setting it to be the primary key. With
> the index spid_flag_wl(species_id, flag, wl_vac) recreated, the EXPLAIN
> output now looks a bit differently:
>
>id: 1
>   select_type: SIMPLE
> table: mtr
>  type: range
> possible_keys: spid_flag_wl
>   key: spid_flag_wl
>   key_len: 16
>   ref: NULL
>  rows: 5487882
> Extra: Using index condition
>
> ...and I get my 3024559 rows in slightly more than 6 seconds.
>
> So many thanks for giving me a pointer in the direction that eventually
> helped solve my problem, even if I don't fully understand why it works.
> Is the lesson here to always create a synthetic primary key for a table?
>
> I've also tried recreating the table with the "natural" primary key of
> (upper_id, lower_id), but it takes much longer to reinsert the rows (it
> still isn't complete after tens of minutes, and previously reinsert
> would be done in just a few minutes), probably because the rows don't
> go in the natural order by upper_id and lower_id at all.
>
> --
> Best regards,
> Ivan
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Speeding up a query: how to send 3 million rows faster?

[Gordan Bobic via discuss]

On Sun, Oct 29, 2023 at 5:03 PM Ivan Krylov via discuss
 wrote:
>
> Dear MariaDB users,
>
> (I've asked the same question at
> , so feel free to ignore this
> one if you've already seen the other.)
>
> Our scientific application needs to store and query fundamental
> parameters for a number of molecules. There are 2 to 28 million rows
> per molecule, but the number of molecules is expected to stay small
> (currently 4). Here's our schema:
>
> CREATE TABLE `mol_trans` (
>   `species_id` int(11) DEFAULT NULL,
>   `wl_vac` double DEFAULT NULL,
>   `upper_id` int(11) DEFAULT NULL,
>   `lower_id` int(11) DEFAULT NULL,
>   `prob` double DEFAULT NULL,
>   `flag` tinyint(4) DEFAULT NULL,
>   KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
>  PARTITION BY LIST (`species_id`)
> (PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB,
>  PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB,
>  PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB,
>  PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB)
>
> (The partitions are here to make it easier to drop a whole molecule if
> needed, which would otherwise be a painful large DELETE.)
>
> I'm using the admittedly old MariaDB 10.3.39 from Debian Buster for
> tests (connecting via the UNIX domain socket using the command line
> client), but we've been seeing the same problem on MySQL 5.6 and
> MariaDB 10.11 on Windows 10.
>
> The following query currently takes 1m 7s on my computer, as measured
> by running `time echo "$QUERY" | mysql "$DATABASE" >/dev/null`:
>
> select
>   mtr.prob,
>   mtr.lower_id,
>   mtr.upper_id
> from
>   mol_trans mtr
> where (
>   mtr.species_id=6115
>   and mtr.wl_vac > 766.0
>   and mtr.wl_vac < 883.0
>   and mtr.flag = 1
> )
> order by mtr.wl_vac;

Try this:

ALTER TABLE mol_trans
DROP INDEX spid_flag_wl,
ADD COLUMN id int unsigned auto_increment,
ADD PRIMARY KEY (species_id, flag, wl_vac, id);

That should avoid a secondary key dereference and shave maybe half of
the execution time off.
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: InnoDB durability

[Gordan Bobic via discuss]

Forgot the main point - isn't the MariaDB "fix" for this bug:
tc_heuristic_recover = COMMIT
?


On Fri, Sep 29, 2023 at 10:53 PM Gordan Bobic  wrote:
>
> Interesting bug.
>
> I have to ask though - if you aren't running with sync_binlog=1, if
> the server crashes, you will have to re-initialize all of the
> downstream replicas from a consistent backup.
> That's annoying when your database is 100GB. It is something to be
> avoided at all cost if your database is 20TB.
> So if there's a flush missing, sure, that needs to be fixed. The point
> I'm making is that sync_binlog=1 should be considered as critical for
> data safety as innodb_flush_log_at_trx_commit=1.
>
>
> On Fri, Sep 29, 2023 at 10:36 PM Federico Razzoli via discuss
>  wrote:
> >
> > Recently Laurynas commented this bug and he brought it up on social media:
> > https://bugs.mysql.com/bug.php?id=75519
> >
> > I was surprised to read thar, from his comments, this 5.6 bug is still in 
> > MySQL 8.0.
> >
> > Did anyone test whether it is in MariaDB too? If so, which versions?
> >
> > Thanks,
> > Federico
> >
> >
> > --
> > Federico Razzoli
> > Vettabase Ltd director & founder, Database Consultant
> > https://vettabase.com
> > federico.razz...@vettabase.com
> > +44 7739 427279
> > Telegram: federico_razzoli
> > Skype: fede.razzoli
> >
> > The information in this e-mail and any attachments may be confidential.
> > If you received it by mistake, please inform the sender.
> > Unless you are the intended recipient or his/her representative you are not 
> > authorised to, and must not, read, copy, distribute, use or retain this 
> > message or any part of it.
> > ___
> > discuss mailing list -- discuss@lists.mariadb.org
> > To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: InnoDB durability

[Gordan Bobic via discuss]

Interesting bug.

I have to ask though - if you aren't running with sync_binlog=1, if
the server crashes, you will have to re-initialize all of the
downstream replicas from a consistent backup.
That's annoying when your database is 100GB. It is something to be
avoided at all cost if your database is 20TB.
So if there's a flush missing, sure, that needs to be fixed. The point
I'm making is that sync_binlog=1 should be considered as critical for
data safety as innodb_flush_log_at_trx_commit=1.


On Fri, Sep 29, 2023 at 10:36 PM Federico Razzoli via discuss
 wrote:
>
> Recently Laurynas commented this bug and he brought it up on social media:
> https://bugs.mysql.com/bug.php?id=75519
>
> I was surprised to read thar, from his comments, this 5.6 bug is still in 
> MySQL 8.0.
>
> Did anyone test whether it is in MariaDB too? If so, which versions?
>
> Thanks,
> Federico
>
>
> --
> Federico Razzoli
> Vettabase Ltd director & founder, Database Consultant
> https://vettabase.com
> federico.razz...@vettabase.com
> +44 7739 427279
> Telegram: federico_razzoli
> Skype: fede.razzoli
>
> The information in this e-mail and any attachments may be confidential.
> If you received it by mistake, please inform the sender.
> Unless you are the intended recipient or his/her representative you are not 
> authorised to, and must not, read, copy, distribute, use or retain this 
> message or any part of it.
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Increasing RAM usage until oom

[Gordan Bobic via discuss]

Have you checked whether:

1) Your theoretical max memory usage exceeds ~90% of RAM?

Start with:

SELECT ((@@innodb_buffer_pool_size + @@innodb_log_buffer_size +
@@key_buffer_size + @@query_cache_size + @@max_connections *
(@@bulk_insert_buffer_size + @@join_buffer_size + @@read_buffer_size +
@@read_rnd_buffer_size + @@sort_buffer_size + @@tmp_table_size)) / 1024 /
1024 / 1024) AS max_memory_GB;

2) Are you using /tmp/ for temporary tables and is your /tmp/ mounted as
tmpfs?

On Thu, 7 Sep 2023, 14:17 Marco Dickert - evolver group via discuss, <
discuss@lists.mariadb.org> wrote:

> Hi Sergei,
>
> a quick follow-up on this topic. We still experience the RAM usage
> problems.
> After your suggestion to upgrade MariaDB to the latest 10.5, we switched
> to the
> community repository for debian
> (https://dlm.mariadb.com/repo/mariadb-server/...). This is what we did
> since:
>
>   * upgrade MariaDB to 10.5.21
>   * upgrade MariaDB to latest 10.6
>   * switch to jemalloc2 library on 10.6
>   * upgrade MariaDB to latest 10.11 (system malloc)
>   * switch to jemalloc2 library on 10.11
>
> We ran every setup for several days, to check if there is any improvement
> on the
> RAM usage front, but nothing really changed. We noticed that when we use
> jemalloc2, the gaps between the OOM-kills is a bit bigger, but the general
> problem persists. Also, we couldn't determine a real "trigger" for the
> behaviour.
>
> Our application has a very regular usage pattern. There is a peak of
> activity in
> the morning (08-10 a.m.), a steady baseline until about 10 p.m., and very
> low
> activity at night (see the attached graph 1). The OOM incidents happen
> both in
> the morning and in the evening. The second graph shows the RAM usage (RSS)
> of
> the mariadbd process over the last three days. Third, I attached a text
> file
> showing the timestamps of the OOM kills.
>
> Unfortunately we are not quite sure which is the best way to debug this
> further.
> We took a look at our queries, but couldn't determine a problem there. Our
> frontend application does not use queries with JOINs. Every request
> triggers
> only a few SELECTs and UPDATEs. Two tables we write to are relatively big
> (10
> and 12 GB).  On average every frontend request triggers about 4-8 queries.
>
> Are there any other metrics we could observe to get a hint to why this
> happens?
> Or has anyone another idea on how to get a grip on what is going on?
>
> Am 2023-08-04 19:13:44 schrieb Sergei Golubchik:
> > Hi, Marco,
> >
> > Please, try the latest 10.5 release, there were few bugs with those
> > symptoms fixed.
> >
> > On Aug 04, Marco Dickert - evolver group via discuss wrote:
> > > Hi folks,
> > >
> > > we experience a RAM issue with MariaDB (version
> > > 10.5.19-MariaDB-0+deb11u2-log) on a standard Debian bullseye system.
> > > The problem is that over time MariaDB uses more and more RAM, until
> > > the kernel's oom-killer terminates it.
> > >
> > Regards,
> > Sergei
> > VP of MariaDB Server Engineering
> > and secur...@mariadb.org
>
> --
> Kind regards,
> Marco Dickert
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Rocksdb as a replacement for toku?

[Gordan Bobic via discuss]

On Fri, Aug 25, 2023 at 9:40 AM Marko Mäkelä  wrote:

> Like Gordan has said in this thread, you might just let the file
> system handle compression if you need it. But, there is no free lunch.
> I suppose that ZFS would not support O_DIRECT.

Doesn't support it YET.
But if the concern is double-caching things, then ZFS has a solution
for that - setting primarycache=metadata.
Obviously buffered write require an extra memcopy, so things will get
faster when O_DIRECT implementation lands, but this is generally not
where significant bottlenecks are at the moment, especially when you
can set sync=disabled and still preserve write ordering (which makes
it safer than disabling innodb_flush_log_at_trx_commit, sync_binlog,
sync_master_info, and similar).

> In any case, with file
> system compression, page writes would become more than a simple matter
> of sending the data to a DMA controller. You could also let the
> storage layer handle compression. I was really impressed by the
> performance of ScaleFlux when we tested it some years ago.

ZFS compression performance is fast enough that it isn't really a
problem. In many cases (spinning rust, slower SSDs), it often makes
things faster because disk throughput is a bigger bottleneck than the
compression cost.
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Rocksdb as a replacement for toku?

[Gordan Bobic via discuss]

Or you could just put it on ZFS and let it do all of the difficult
stuff for you. :-)

On Thu, Aug 24, 2023 at 6:40 PM pslawek83  wrote:
>
> Hi Marko, thanks for the response ... so for the DOCS part
>
> > https://mariadb.com/kb/en/innodb-page-compression/
> > When InnoDB page compression is used, InnoDB may still write the compressed 
> > page to the tablespace file with the original size of the uncompressed 
> > page, which would be equivalent to the value of the innodb_page_size system 
> > variable. This is done by design, because when InnoDB's I/O code needs to 
> > read the page from disk, it can only read the full page size. However, this 
> > is obviously not optimal.
> > This punch hole technique allows InnoDB to read the compressed page from 
> > disk as the full page size, even though the compressed page really takes up 
> > less space on the file system.
>
> So as far as i understand if compression is enabled the server will write eg. 
> 40% utilized page which will later be decompressed when read, but this 
> compression/decompression won't have any advangage if there's no punch hole 
> support. The only difference will be that the block will be filled with zeros 
> for the empty space at the end. Or is it possible for PAGE_COMPRESSION to 
> still be used without punch hole (just not that effective for example)?
>
> Also tested this row compression, and yes page compression seems much better. 
> Is it possible to get rid of punch hole by just re-mapping the pages inside 
> these innodb files? I was thinking about this... if it's possible to change 
> the data structure to divide the file into 512b blocks while the beginning of 
> each block will mark page number (0x for blocks which are part of 
> previous page). To increase page size we'd need to add another page with same 
> number at the end of the file, later continuous recycled blocks can be added 
> to a list and used to store smaller pages. To make this atomic, first whole 
> block would be allocated and written with 0xfffe as a dummy id and then 
> this placeholder id could be replaced with real block number).
>
> The problem with this could be that the file would get 100% fragmented over 
> time (but probably will work ok with NVME). The mapping could be read back by 
> reading whole datafile during server start and it'd need 125MB of memory for 
> 1TB of compressed data to store the mapping in memory for fast access.
>
> What you thinik? Would it be easy to add it on top of current code (if 
> there's any API maybe, so for example i can do this) and would it even make 
> sense?
>
> Best,
> Slawomir.
>
> Dnia 21 sierpnia 2023 07:08 Marko Mäkelä  
> napisał(a):
>
> On Sun, Aug 20, 2023 at 6:12 PM pslawek83 via discuss
>  wrote:
>
> Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make 
> some reading. Not sure if I fully understand how that works... is that 
> correct?
> 1. Impossible without punch hole as if we don't have it the server will just 
> write partially used pages which will take full page size anyway. But in docs 
> it seems it described its use with punch hole disabled. What's the reason to 
> still have page compression without punch hole?
>
> Can you please point to the misleading part of the documentation so
> that it can be corrected?
>
> 2. After enabling compression each page needs to be trimmed using punch hole, 
> so if we have 1 TB database and 64k blocks, the database files will have over 
> 15 million fragments (1/64000)
>
> That is correct.
>
> So if we get like 50% compression and then we'll be able to put another 1TB 
> of data into these holes we'll be having close to 31 million file fragments. 
> I'm not sure if under such conditions the FS will be able to even allocate 
> space efficiently anymore, copy these files, etc. Or im not getting something?
>
> For what it is worth, around the same time when the page_compressed
> was being developed for MariaDB Server 10.1, something similar was
> being worked on at Oracle, I think for MySQL 5.7. To my understanding,
> the same hardware partner worked with both companies. The obvious
> answer to 
> https://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.html
> would have been https://en.wikipedia.org/wiki/Fusion-io_NVMFS if it
> had worked without kernel panics or existed in a mainline kernel. I
> don’t even know if it ever was open sourced.
>
> 3. I was testing compression of ~1.5GB table with nice results on default 
> 16kb page size. One thing i noticed is that l was always getting exactly the 
> same compressed size using `du`. However the table read/copy times were 
> different from 40s for uncompressed to 80-400 seconds depending on algo. Is 
> that possible that is because page size is too low?
>
> As far as I remember, most compression algorithms that are derivatives
> of the LZ77 family have an input buffer size of 32 KiB. From that
> point of view, it would seem to make sense to use 

[MariaDB discuss] Re: Rocksdb as a replacement for toku?

[Gordan Bobic via discuss]

Larger pages will yield higher compression ratios, as will zstd (slightly
slower than lz4).

Mileage varies, but on some of my databases, between the 64KB pages (zfs
recordsize) and zstd, my compression ratios approximately doubled, from
about 1.8:1 to a little under 4:1. This will all heavily depend on the type
of data you are storing, though.

zstd has adjustable compression, so if you have tons of CPU to spare, you
could turn it up from the default of 3 (probably optimal cost/size
compromise) to high teens for a compression boost.

On Wed, 16 Aug 2023, 14:34 Reinis Rozitis,  wrote:

> > From: Gordan Bobic via discuss 
> >
> > One of the best compromises is to configure InnoDB with 64KB pages, put
> it on ZFS with recordsize=64K, and enable zstd compression on ZFS.
>
> Sorry for hijacking the thread a bit. After the discontinuation of tokudb
> I chose the innodb+zfs route (bad previous/early experience with rocks,
> also million knobs to adjust for your workload etc) but at that time didn't
> think of increasing the Innodb page size to 64KB - do you have any (even
> rough) numbers of 16 vs 64KB (on zfs)?
>
> Also, what would be the benefit of zstd vs lz4? Higher compression ratio?
>
> rr
>
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Rocksdb as a replacement for toku?

[Gordan Bobic via discuss]

Large pages (64KB vs default 16KB) significantly improve the compression
ratio with page compression, at the cost of inducing misalignment with the
storage stack.

One of the best compromises is to configure InnoDB with 64KB pages, put it
on ZFS with recordsize=64K, and enable zstd compression on ZFS. That leaves
both compression and alignment to ZFS to take care of. Throughput and
compression in that combination are both excellent, a much better
compromise than myrocks IMVHO.



On Tue, 15 Aug 2023, 13:29 jocelyn fournier, 
wrote:

> Hi!
>
> I confirm I was also my choice, the migration from TokuDB to InnoDB with
> row compression is a really good option.
>
> BR,
>
> > Le 15 août 2023 à 12:52, Gordan Bobic via discuss <
> discuss@lists.mariadb.org> a écrit :
> >
> > You may find that InnoDB with row compression and 64KB pages compares
> relatively favourably, if that is an option for you.
> >
> > On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, <
> discuss@lists.mariadb.org> wrote:
> > Hi Guys, i'm looking for tokudb replacement and i'm again thinking about
> rocksdb. Is it mature enough to be used as an engine for statistical tables
> (several billions rows, around 2TB in size?).
> > - Is it (will it be?) possible to have something like
> single-file-per-table (or single dir per table?)
> > - Is it shared per all tables, so all tables are still using single set
> of files (can single crashed table/row lead to all tables being unusable?)
> > - Will partitioning the table have performance benefits like with other
> engines? Can I do instant TRUNCATE PARTITION for easy data retention?
> >
> > Thanks
> > ___
> > discuss mailing list -- discuss@lists.mariadb.org
> > To unsubscribe send an email to discuss-le...@lists.mariadb.org
> > ___
> > discuss mailing list -- discuss@lists.mariadb.org
> > To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Rocksdb as a replacement for toku?

[Gordan Bobic via discuss]

You may find that InnoDB with row compression and 64KB pages compares
relatively favourably, if that is an option for you.

On Tue, 15 Aug 2023, 11:44 pslawek83 via discuss, 
wrote:

> Hi Guys, i'm looking for tokudb replacement and i'm again thinking about
> rocksdb. Is it mature enough to be used as an engine for statistical tables
> (several billions rows, around 2TB in size?).
> - Is it (will it be?) possible to have something like
> single-file-per-table (or single dir per table?)
> - Is it shared per all tables, so all tables are still using single set of
> files (can single crashed table/row lead to all tables being unusable?)
> - Will partitioning the table have performance benefits like with other
> engines? Can I do instant TRUNCATE PARTITION for easy data retention?
>
> Thanks
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[MariaDB discuss] Re: Increasing RAM usage until oom

[Gordan Bobic via discuss]

IIRC binlog_cache_size is not per session, but tmp_table_size is more
or less per session, and tmp_table_size is usually quite large
compared to other things.

On Fri, Aug 4, 2023 at 4:27 PM Marco Dickert - evolver group via
discuss  wrote:
>
> Hi folks,
>
> we experience a RAM issue with MariaDB (version 10.5.19-MariaDB-0+deb11u2-log)
> on a standard Debian bullseye system. The problem is that over time MariaDB 
> uses
> more and more RAM, until the kernel's oom-killer terminates it.
>
> The server is a VMware guest with 8G RAM, 2G swap and 4 cores. The most
> important parameter, innodb_buffer_pool_size, is set to 2G. I know this is 
> quite
> low for this system, but I want to have proof that MariaDB consumes 
> defininitely
> more RAM than allowed.
>
> According to the calculation in this comment [1], the RAM usage shouldn't 
> exceed
> 4.2G. Here is the result, including the raw configuration values:
>
> ```
> MariaDB [(none)]> SELECT ROUND(
> -> ( @@GLOBAL.key_buffer_size
> ->  + @@GLOBAL.query_cache_size
> ->  + @@GLOBAL.tmp_table_size
> ->  + @@GLOBAL.innodb_buffer_pool_size
> ->  + @@GLOBAL.innodb_log_buffer_size
> ->  + @@GLOBAL.max_connections * (
> ->  @@GLOBAL.sort_buffer_size
> ->+ @@GLOBAL.read_buffer_size
> ->+ @@GLOBAL.read_rnd_buffer_size
> ->+ @@GLOBAL.join_buffer_size
> ->+ @@GLOBAL.thread_stack
> ->+ @@GLOBAL.binlog_cache_size)
> -> ) / 1024 / 1024, 1) `total MB`;
> +--+
> | total MB |
> +--+
> |   4220.0 |
> +--+
> 1 row in set (0,001 sec)
>
> MariaDB [(none)]> SELECT @@GLOBAL.key_buffer_size, 
> @@GLOBAL.query_cache_size
> , @@GLOBAL.tmp_table_size, @@GLOBAL.innodb_buffer_pool_size
> , @@GLOBAL.innodb_log_buffer_size, @@GLOBAL.max_connections
> , @@GLOBAL.sort_buffer_size, @@GLOBAL.read_buffer_size
> , @@GLOBAL.read_rnd_buffer_size, @@GLOBAL.join_buffer_size
> , @@GLOBAL.thread_stack, @@GLOBAL.binlog_cache_size \G
> 1 row in set (0,000 sec)
> *** 1. row ***
> @@GLOBAL.key_buffer_size: 10485760
>@@GLOBAL.query_cache_size: 536870912
>  @@GLOBAL.tmp_table_size: 16777216
> @@GLOBAL.innodb_buffer_pool_size: 2147483648
>  @@GLOBAL.innodb_log_buffer_size: 134217728
> @@GLOBAL.max_connections: 512
>@@GLOBAL.sort_buffer_size: 2097152
>@@GLOBAL.read_buffer_size: 131072
>@@GLOBAL.read_rnd_buffer_size: 262144
>@@GLOBAL.join_buffer_size: 262144
>@@GLOBAL.thread_stack: 299008
>   @@GLOBAL.binlog_cache_size: 32768
> ```
>
> However, MariaDB uses up to 5.4G so far. And it keeps increasing. Last time 
> the
> oom-killer was invoked, MariaDB used 10G total, and 7.4G rss (given in 4k
> pages):
>
> ```
> [Mi Jul 26 16:01:06 2023] Tasks state (memory values in pages):
> [Mi Jul 26 16:01:06 2023] [  pid  ]   uid  tgid total_vm  rss 
> pgtables_bytes swapents oom_score_adj name
> ...
> [Mi Jul 26 16:01:06 2023] [3462912] 1 3462912  2753196  1895256 20762624  
>  472509 0 mariadbd
> ...
> [Mi Jul 26 16:01:06 2023] 
> oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mariadb.service,task=mariadbd,pid=3462912,uid=1
> [Mi Jul 26 16:01:06 2023] Out of memory: Killed process 3462912(mariadbd) 
> total-vm:11012784kB, anon-rss:7581024kB, file-rss:0kB,shmem-rss:0kB, 
> UID:1 pgtables:20276kB oom_score_adj:0
> ```
>
> The MariaDB knowledge base [2] says that the problem probably correlates with
> one of the following options:
>
> ```
> ++--++
> | variable_name  | default_value| variable_value |
> ++--++
> | INNODB_IO_CAPACITY_MAX | 18446744073709551615 | 2000   |
> | QUERY_CACHE_LIMIT  | 1048576  | 67108864   |
> | MAX_CONNECTIONS| 151  | 512|
> | TABLE_OPEN_CACHE_INSTANCES | 8| 7  |
> | LONG_QUERY_TIME| 10.00| 5.00   |
> | KEY_BUFFER_SIZE| 134217728| 10485760   |
> | QUERY_CACHE_SIZE   | 1048576  | 536870912  |
> | INNODB_LOCK_WAIT_TIMEOUT   | 50   | 80 |
> | INNODB_LOG_BUFFER_SIZE | 16777216 | 134217728  |
> | HOST_CACHE_SIZE| 128  | 654|
> | INNODB_FT_MIN_TOKEN_SIZE   | 3| 2  |
> | WAIT_TIMEOUT   | 28800| 600|
> | INNODB_FAST_SHUTDOWN   | 1| 0 

[MariaDB discuss] Re: Advice on Replication

[Gordan Bobic via discuss]

The only way I can think of to achieve this is to create a separate schema
and use triggers that selectively copy only the records you want into this
new schema and replicate only this new schema. You will have to use
binlog_format=ROW.

On Thu, 13 Jul 2023, 04:25 Andrew Gable via discuss, <
discuss@lists.mariadb.org> wrote:

> Hi Everyone,
>
> I hope someone can guide me.
>
> here is my issue
>
> I have a central database that is running in my back office and each of my
> front end machines have a database that is used just for the front end.
>
> most of the data that is stored in the back office is not needed on the
> front end but the product table is.
>
> the product table hold more information then what is needed on the front
> end (the front end system does not need to know who the supplier is of the
> item for example)
>
> so I have the following feilds on the front end database (called
> posdatabase)
> barcodenumber
> posdeacription
> pricetype
> salelocation
> Systemprice.
>
> both database are using the same field names
>
> is it possible to use replication on select Feilds on the master to the
> slave? I have seen on YouTube lots of videos showing replication of every
> table and field in a database but I don't need that)
>
> what I want to do is that possible?
>
> Thank you for your time with this matter
>
> Andrew Gable
> Owner / Software developer
> algPoS,Northampton http://www.algpos.co.uk
> 01604 289431
>
> Send from iPhone Email.
>
>
> This e-mail is private, confidential and is for the intended recipient only. 
> If misdirected, please notify the sender by telephone and confirm, by return 
> e-mail, that it has been deleted from your system and any copies destroyed. 
> If you are not the intended recipient you are strictly prohibited from using, 
> printing, copying, distributing or disseminating this e-mail or any 
> information contained in or attached to it.
>
> ___
> discuss mailing list -- discuss@lists.mariadb.org
> To unsubscribe send an email to discuss-le...@lists.mariadb.org
>
___
discuss mailing list -- discuss@lists.mariadb.org
To unsubscribe send an email to discuss-le...@lists.mariadb.org

[Maria-discuss] Creating a trigger on information_schema.processlist

[Gordan Bobic]

A long standing annoyance has been that stored procedures are opaque
when it comes to profiling them with more granularity than just the
entire procedure.

SHOW FULL PROCESSLIST shows the individual queries while they are
running, but polling SHOW FULL PROCESSLIST or SELECT * FROM
information_schema.PROCESSLIST is not a practical solution because you
can only poll it so many times per second without crippling the
server.

So I had the idea to hook triggers to information_schema.PROCESSLIST
to capture the changes to the INFO column and shunt them into
mysql.slow_log.

But MariaDB informs them that root isn't privileged enough to crate a
trigger in information_schema.

Is there another way to augment slow logging granularity of queries
that execute as part of the stored procedure?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MySQLTuner-perl 2.0.10 with MariaDB 10.3.35 + Plesk

[Gordan Bobic]

On Wed, Jan 18, 2023 at 4:30 PM Marko Mäkelä  wrote:

> It should be worth noting that starting with MariaDB 10.5, there will
> no longer be writes of garbage pages that were freed from the
> underlying file, for example, as part of DROP INDEX, TRUNCATE TABLE,
> or a massive DELETE.
>
> In https://jira.mariadb.org/browse/MDEV-19895 I would welcome
> constructive feedback on how to set sane default values of some
> parameters, based on a small number of parameters, say, the available
> size of memory, CPU cores, storage, and the size of the database.

May or may not be directly related to this, but with 10.5+ I see the
checkpoint age exhibiting a sawtooth pattern - nothing gets flushed
until it gets near the log capacity, then there is a huge burst of
writes and it crashes down to a sensible level.
Prior to 10.5, the checkpoint age remains low unless the workload is
overruning innodb_io_capacity.

I get the motivation to reduce flushing, but if the checkpoint age is
already close to the high water mark and a sustained high volume of
transactions hits, then the server has nowhere to go because it has
already used up all of it's scope for absorbing the shock into the
redo log and everything grinds to a halt.
Is there a way to restore the old behaviour? The new behaviour seems
to be in all 10.5+ versions I looked at.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MySQLTuner-perl 2.0.10 with MariaDB 10.3.35 + Plesk

[Gordan Bobic]

On Wed, Jan 18, 2023 at 3:28 PM Dragos Pacher  wrote:
>
> Hello Gordan,
>
> Thank you for your advice. Your answer surprised me from 2 perspectives:
>
> 1. The following is coming from Plesk people in the default production 
> install(!), I just changed it a bit on tuners advice.
> By default the setting is present in my.cnf:
>
> join_buffer_size= 3M

Plesk people aren't database specialists and I wouldn't expect web
control panel developers to have a particularly extensive
understanding of database internals.
If whoever wrote MySQL Tuner doesn't have an adequate understanding of
such, what chance do developers who don't claim to be database
specialists have?

> 2. I see mysqltuner gaining more adoption - did not knew its that bad..

It really isn't (thankfully). The only setting you should be changing
away from defaults out of the box is innodb_buffer_pool_size.
You shouldn't be touching anything else unless you know exactly why
you are changing it.

> I will take your advice and leave the options as defaults - in the way Plesk 
> meant it with the only change being to delete the join_buffer_size.
>
> What do you think about point nr 1?

Skipping name resolution?
That is fine if you don't define any of your users as logging in with
a hostname match.

Things like this will be fine if you skip the name resolve:
user@'1.2.3.4'
user@'1.2.3.%'

Things like this will NOT work:
user@'%.domain.com'

If all of your users are restricted by IP (or IP range), then you can
safely disable reverse DNS lookup and save yourself a millisecond or
two on every tcp database connection.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MySQLTuner-perl 2.0.10 with MariaDB 10.3.35 + Plesk

[Gordan Bobic]

A lot of MySQL Tuner's advice is based on extensive misconceptions and
questionable understanding of MySQL internals. Realistically - the
best option is to just pretend it doesn't exist.
But since you asked:

join_buffer_size (> 5.0M, or always use indexes with JOINs)

It is spectacularly bad advice to suggest off hand that increasing the
join buffer size is a good idea.
If you genuinely do find that you need to increase it, you almost
certainly have bigger problems that would be better addressed by
improving your queries and indexes.

innodb_log_file_size should be (=16M) if possible, so InnoDB total
log files size equals 25% of buffer pool size.

I have no idea where this notion that innodb_log_file_size is in any
way related to the buffer pool size, but it is completely
non-sensical. The two are not in any way related, and each needs to be
configured independently in a way that is appropriate to the server's
workload.

Bottom line: uninstall MySQL Tuner and forget it ever existed. At best
it will mislead you. At worst it will completely cripple the server.


On Wed, Jan 18, 2023 at 2:27 PM Dragos Pacher  wrote:
>
> Hello,
>
> I am looking for an expert opinion on MySQLTuner-perl recommendations and 
> usefulness in my specific case of MariaDB 10.3.35
> with Plesk Web Host Edition 18.0.49 Update #2 - with a somehow default 
> installation: one demo site added + some popular
> components in default state idling - on Rocky Linux 8.7 kernel 
> 4.18.0-425.10.1.el8_7.x86_64.
>
> What are your opinions on the recommendations below? My machine is a 
> VirtualBox 7.0.4 one(on Windows 11 22H2 build 22621.1105)
> with slower Seagate Barracuda PRO disks (non RAID), 8 GB RAM, processor Ryzen 
> 7 3700x(all cores assigned). This was meant
> just for testing but I am trying to extrapolate some of the results here for 
> a production environment with generous
> hardware(over 48 GB RAM and newer generation CPUs, NVME/RAIDs). The only way 
> (as of now) for Plesk to work is by coexisting on the
> same machine with the database server - please take this into consideration.
>
> Please see the results below after ~15-24h of Plesk idling on the machine. 
> Also please let me know if you need more data
> from my side or if I should enable something and rerun a specific test, I can 
> provide detailed statistics if needed. I am
> really looking forward on how to make this database a bit faster if 
> possible(without enabling really dangerous options).
> Thank you.
>
> [root@pensive-aryabhata ~]# cat /etc/my.cnf
> #
> # This group is read both both by the client and the server
> # use it for options that affect everything
> #
> [client-server]
>
> #
> # include all files from the config directory
> #
> !includedir /etc/my.cnf.d
>
> [mysqld]
> sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
> bind-address = :::127.0.0.1
> local-infile=0
>
> join_buffer_size= 5M # I changed this from 3 to 5 24h before 
> running the MySQLTuner
>
> [root@pensive-aryabhata ~]# sysctl -a | grep swapp
> vm.force_cgroup_v2_swappiness = 0
> vm.swappiness = 5
>
> [root@pensive-aryabhata ~]# perl mysqltuner.pl --host 127.0.0.1
>  >>  MySQLTuner 2.0.10
>  * Jean-Marie Renouard 
>  * Major Hayden 
>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>  >>  Run with '--help' for additional options and output filtering
>
> [--] Skipped version check for MySQLTuner script
> [--] Performing tests on 127.0.0.1:3306
> [OK] Currently running supported MySQL version 10.3.35-MariaDB
> [OK] Operating on 64-bit architecture
>
>  Log file Recommendations 
> --
> [OK] Log file /var/log/mariadb/mariadb.log exists
> [--] Log file: /var/log/mariadb/mariadb.log (13K)
> [OK] Log file /var/log/mariadb/mariadb.log is not empty
> [OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
> [OK] Log file /var/log/mariadb/mariadb.log is readable.
> [!!] /var/log/mariadb/mariadb.log contains 15 warning(s).
> [!!] /var/log/mariadb/mariadb.log contains 2 error(s).
> [--] 5 start(s) detected in /var/log/mariadb/mariadb.log
> [--] 1) 2023-01-17 23:36:33 0 [Note] /usr/libexec/mysqld: ready for 
> connections.
> [--] 2) 2023-01-17 23:36:20 0 [Note] /usr/libexec/mysqld: ready for 
> connections.
> [--] 3) 2023-01-17 23:23:33 0 [Note] /usr/libexec/mysqld: ready for 
> connections.
> [--] 4) 2023-01-17 18:18:58 0 [Note] /usr/libexec/mysqld: ready for 
> connections.
> [--] 5) 2023-01-17 18:18:24 0 [Note] /usr/libexec/mysqld: ready for 
> connections.
> [--] 4 shutdown(s) detected in /var/log/mariadb/mariadb.log
> [--] 1) 2023-01-17 23:36:29 0 [Note] /usr/libexec/mysqld: Shutdown complete
> [--] 2) 2023-01-17 23:36:16 0 [Note] /usr/libexec/mysqld: Shutdown complete
> [--] 3) 2023-01-17 23:20:59 0 [Note] /usr/libexec/mysqld: Shutdown complete
> [--] 4) 2023-01-17 18:18:50 0 [Note] /usr/libexec/mysqld: Shutdown 

Re: [Maria-discuss] Code of Conduct

[Gordan Bobic]

I do. And it is not relevant to MariaDB and databases so this didn't
seem like an appropriate forum.

On Fri, Dec 23, 2022 at 2:01 PM Ruben Safir  wrote:
>
> One more thing,  Try to actually have a conversaiton here by addressing
> what was previously written.
>
> I will repeat it here.
>
>  Do you know who John Tennant was?  Remember Aaron Swartz?  Did you
>  witness the fiasco at the FSF?
>

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Code of Conduct

[Gordan Bobic]

On Fri, Dec 23, 2022 at 1:31 PM Ruben Safir  wrote:
>
> On Thu, Dec 22, 2022 at 10:32:36PM +0200, Gordan Bobic wrote:
> > Put it this way - would you expect to get away with abusive behaviour at
> > work without getting booted out? Or in a restaurant? Or at a conference?
>
> This is not work, a restaurant or a conference and the Code of
> Conducts doesn't define abuse 
>
> So your wrong on all those points.

It is roughly equivalent to the ones I mentioned. If you cannot see
that, I can only assume you are being willfully ignorant and
facetious.

> Codes of Conducts are facist tools to harrass anyone who doesn't
> agree with you... period.and enforcing the moral standards of a few
> on the many and to levy false chages on people and push them out of
> organizations.
>
> That is there purpose.
>
> Do you know who John Tennant was?  Remember Aaron Swartz?  Did you
> witness the fiasco at the FSF?
>
> I will not comply with a code of conduction.  And I will not have the
> utter stupidy of Corperate HR departments dictate my private
> affiliations or determin my ethical standards.
>
> All codes of conducts are evil.
>
> BTW - maybe you should reconsider using gmail for your email account.
> Grow up and get a real mail server, one that doesn't spy on you.

I use gmail specifically because I grew up. I was running my own mail
servers for a decade. These days I have far better things to do with
my time than waste it on solving problems that have been extensively
solved.

Gordan

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Fri, Dec 9, 2022 at 2:43 AM Reindl Harald  wrote:

> > If you're concerned about database corruption then you need to start off
> > by having multiple copies of the data available in an online state if
> > you want to recover from corrupt data without doing a backup.
>
> completly different topic
>
> > This is
> > what ZFS does with RAID - detects which block is corrupt and then uses
> > other data blocks, including parity, to rebuild the corrupt block.
>
> completly different layers
>
> > MariaDB isn't designed for that. I'm not even sure if there is any
> > database that's designed for that, including SQL Server (see below for
> > more.)
>
> the topic was "Some of us run MariaDB on file systems that do their own
> block checksumming, and thus run innodb_checksum_algorithm=none" where
> you mix two completly independent layers and now we have two morons

Virtually all data corruption that occurs happens in the disk layer,
not the database internals layer. This is the reality you seem to be
struggling to grasp.
If address the problem of corruption in the disk layer, the usefulness
of additionally checking in a place that is only useful for internals
debugging is vanishingly narrow.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Fri, Dec 9, 2022 at 2:40 AM Reindl Harald  wrote:

> >>> 2.2) Database doesn't crash because the damage merely corrupts a
> >>> single value but the record structure remains sound.
> >>>
> >>> So it is that 2.2) point where the InnoDB checksum gives you anything
> >>
> >> moron it don't matter if you find it useful - the whole point was that
> >> you pretended the filesystem can do the same with it's checksums which
> >> is nonsense
> >
> > You are conveniently ignoring the fact that in the vast majority of
> > cases what InnoDB checksums will catch is silent disk corruption
> > rather than database internals corruption.
>
> i ignore nothing but filesystem corruption is still a different topic
>
> > So the one narrow edge case you are clinging to as the full
> > justification of your abusive behaviour and delusions of grandeur are
> > a tiny fraction of a percent of the errors that will cause InnoDB
> > checksums to fail - and outside that narrow edge case all of the rest
> > of them will be caught and handled better at layers below the database
> > itself.
>
> the topic was and still is "Some of us run MariaDB on file systems that
> do their own block checksumming, and thus run
> innodb_checksum_algorithm=none" where you mix two completly independent
> layers
>
> > So either you are arguing in bad faith, or you really are extensively
> > ignorant of typical failure patterns.
>
> the topic was and still is "Some of us run MariaDB on file systems that
> do their own block checksumming, and thus run
> innodb_checksum_algorithm=none" where you mix two completly independent
> layers

They are different layers, but 99.9%+ of corruption that InnoDB
checksums ever detects occur in the storage layer, not in the database
internals layer.
So in terms of the overall picture of the corruption cause probability
landscape which you seem to be struggling to see, you are about 0.1%
correct. I'll grant you that.

I'll go as far as hazarding a guess that InnoDB checkums were
originally added with the main motivation of detecting disk corruption
rather than internals debugging. Unfortunately, the code tree back in
3.23.24 (as far as I can tell the release where InnoDB was merged)
doesn't seem to contain any annotations on the subject that might shed
light on the original motivation.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Fri, Dec 9, 2022 at 12:31 AM Reindl Harald  wrote:

> > 2.2) Database doesn't crash because the damage merely corrupts a
> > single value but the record structure remains sound.
> >
> > So it is that 2.2) point where the InnoDB checksum gives you anything
>
> moron it don't matter if you find it useful - the whole point was that
> you pretended the filesystem can do the same with it's checksums which
> is nonsense

You are conveniently ignoring the fact that in the vast majority of
cases what InnoDB checksums will catch is silent disk corruption
rather than database internals corruption.
So the one narrow edge case you are clinging to as the full
justification of your abusive behaviour and delusions of grandeur are
a tiny fraction of a percent of the errors that will cause InnoDB
checksums to fail - and outside that narrow edge case all of the rest
of them will be caught and handled better at layers below the database
itself.
So either you are arguing in bad faith, or you really are extensively
ignorant of typical failure patterns.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 10:59 PM Reindl Harald  wrote:
>
> the next reply-all clown boy i am subsribed so you can just respond
> to the list

You sure whine a lot about inconsequential things.

> Am 08.12.22 um 21:13 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 9:42 PM Reindl Harald  wrote:
> >>
> >> Am 08.12.22 um 18:59 schrieb Gordan Bobic:
> >>> On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  
> >>> wrote:
> >>>> MariaDB does the same as the filesystem
> >>>> InnoDB in fact is more ore less a FS on top of a FS
> >>>
> >>> So why do it at both levels?
> >>
> >> because the FS layer can't detect MariaDB errors?
> >
> > What is the net benefit of detecting said error? The way I see it, the
> > options are:
> > 1) MariaDB detects and error, crashes out
> > 2) MariaDB doesn't detect an error, ingests garbage, crashes out
>
> silent data corruption

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.

> > The only way an error will creep in without the error checking FS
> > spotting it is:
> > 1) manually corrupting the block by writing garbage over it
> > 2) MariaDB generated a duff block and wrote it out
> > 3) Some other hardware failure corrupted the block in MariaDB memory,
> > just before writing it to the file system
> >
> > If any of the latter set happens, the data is toast anyway.
> > If the former set happens, the data is toast anyway.
>
> silent data corruption

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.

> > Sure it's nice to get an error that confirms that your data is
> > corrupted, but that won't bring it back
> silent data corruption
>
> > Shift it down to a layer where at least a subset of the problemspace
> > can be fixed and we have a net gain in at least some cases.
>
> different world

A far better world that you seem have a hard time accepting exists.

> >>> And what makes doing it at MariaDB level
> >>> in any way better than doing it somewhere else?
> >>
> >> which magic should do it somewehre else?
> >
> > If a file system is in control of data mirroring and checksumming
> > every 16KB block, then if the data is corrupted on disk, file system
> > will detect it on a read and fetch a good copy from an uncorrupted
> > mirror.
>
> moron the files are fine from the viewpoint of the filessystem

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.
I am talking about total cumulative data integrity up to and including
what MariaDB reads from disks.
What layer in the stack, exactly, are you trying to fight "silent data
corruption" you mentioned above?

> > Application never knows something went wrong, file system replaces the
> > corrupted block on the other disk and everything carries on
> > uninterrupted.
>
> moron the files are fine from the viewpoint of the filessystem
>
> >> AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED
> >
> > So your argument is that the page checksum is there to tell you that
> > your data is corrupted because of either:
> > 1) data was corrupted by the database itself, or
> > 2) a superuser overwriting the block on the disk
>
> and it's impressive how long it takes until you mororn understand such
> basics

And in that case what exactly will the ckecksum do for you?
What do you gain from it? The error message that the block is
corrupted just before the database crashes?

> > In either case, you are not getting the data back either way the the
> > database will stop working.
>
> yes, but it's a difference if you have silent data corruption detectet
> months later when clean backups are gone because retention times
>
> > So what is the point? I'd rather have the error fixable
>
> moron your filesystem can't fix that type of error
>
> that's why "Some of us run MariaDB on file systems that do their own
> block checksumming, and thus run innodb_checksum_algorithm=none" was an
> idiots response
>
> a different response would have been "i don't think that i need innodb
> checksums and want to disable it" but you moron pretended your
> filesystem does the same

Let's look at the scenarios here, a

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 9:42 PM Reindl Harald  wrote:
>
>
>
> Am 08.12.22 um 18:59 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:
> >> MariaDB does the same as the filesystem
> >> InnoDB in fact is more ore less a FS on top of a FS
> >
> > So why do it at both levels?
>
> because the FS layer can't detect MariaDB errors?

What is the net benefit of detecting said error? The way I see it, the
options are:
1) MariaDB detects and error, crashes out
2) MariaDB doesn't detect an error, ingests garbage, crashes out

The only way an error will creep in without the error checking FS
spotting it is:
1) manually corrupting the block by writing garbage over it
2) MariaDB generated a duff block and wrote it out
3) Some other hardware failure corrupted the block in MariaDB memory,
just before writing it to the file system

If any of the latter set happens, the data is toast anyway.
If the former set happens, the data is toast anyway.

Sure it's nice to get an error that confirms that your data is
corrupted, but that won't bring it back.
Shift it down to a layer where at least a subset of the problemspace
can be fixed and we have a net gain in at least some cases.

> > And what makes doing it at MariaDB level
> > in any way better than doing it somewhere else?
>
> which magic should do it somewehre else?

If a file system is in control of data mirroring and checksumming
every 16KB block, then if the data is corrupted on disk, file system
will detect it on a read and fetch a good copy from an uncorrupted
mirror.
Application never knows something went wrong, file system replaces the
corrupted block on the other disk and everything carries on
uninterrupted.

> >> "Some of us run MariaDB on file systems that do their own block
> >> checksumming, and thus run innodb_checksum_algorithm=none" makes you
> >> looking like a fool - period
> >>
> >> are you dumb or why don't you understand that the filesystem is a
> >> completly different layer and has no clue about the data itself?
> >
> > Are you too dumb to understand that if a block is corrupted at InnoDB
> > level MariaDB can't do anyting to fix it, but if a block is corrupted
> > at lower level, ZFS can fix it from redundantly stored data and
> > MariaDB never gets to ingest a corrupted block in the first place?
>
> it can at least fail early instead work with corrupted data
>
> > If you disagree, please describe a scenario in which an InnoDB page
> > checksum does anything useful if the file system it is on has built in
> > block checksumming and data redundancy.
> INNDOB CHECKSUMS DETECT DATA CORRUPTION WITHIN MARIADB NOT CAUSED BY ANY
> FILESYSTEM ISSUE AT ALL
>
> the filesystem can't do that with it's block checksumming and data
> redundancy because there is *nothing wrong* for the view of the FS layer
>
> one is for consistency of the database
> one is for consistency of the underlying filesystem
>
> two worlds and i simply don't get why people not understanding such
> basics  work in the IT but to top that talking nonsense on mailing-lists
>
> AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED

So your argument is that the page checksum is there to tell you that
your data is corrupted because of either:
1) data was corrupted by the database itself, or
2) a superuser overwriting the block on the disk

In either case, you are not getting the data back either way the the
database will stop working.
So what is the point? I'd rather have the error fixable than have the
knowledge that I have an error I can do nothing about.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:

>  MariaDB Server 10.4 introduced a new file format
>  innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
>  the default. Any files that were created when that setting is active
>  are guaranteed to write any unused bytes as zeroes. It also fixes a
>  peculiar design decision that some bytes of the page are not covered
>  by any checksum, and that a page is considered valid if any of the
>  non-full_crc32 checksums happen to produce a match. This includes the
>  magic 0xdeadbeef for innodb_checksum_algorithm=none.
> 
>  Maybe we should consider eventually deprecating write support for the
>  non-full_crc32 format, to force a fresh start.
> >>>
> >>> Please don't. Some of us run MariaDB on file systems that do their own
> >>> block checksumming, and thus run innodb_checksum_algorithm=none
> >>
> >> that's nonsense - when mariadb writes wrong data in it's files no
> >> filesystem can magically fix that
> >
> > MariaDB can't fix it either. And if that is what happened, there is no
> > benefit to duplicating the effort.
>
> MariaDB does the same as the filesystem
> InnoDB in fact is more ore less a FS on top of a FS

So why do it at both levels? And what makes doing it at MariaDB level
in any way better than doing it somewhere else?

> >> you need to understand what innodb checksums are and then it's logical
> >> that the file-system layer is a completly different world
> >>
> >> https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum
> >
> > You need to understand that properly thought out and sensibly written
> > file systems (which is, granted, pretty rare, I know of a total of 1)
> > implicitly prevent torn pages from being possible.
> > So the checksum and the doublewrite are completely redundant in such
> > cases, and can be safely disabled.
>
> "Some of us run MariaDB on file systems that do their own block
> checksumming, and thus run innodb_checksum_algorithm=none" makes you
> looking like a fool - period
>
> are you dumb or why don't you understand that the filesystem is a
> completly different layer and has no clue about the data itself?

Are you too dumb to understand that if a block is corrupted at InnoDB
level MariaDB can't do anyting to fix it, but if a block is corrupted
at lower level, ZFS can fix it from redundantly stored data and
MariaDB never gets to ingest a corrupted block in the first place?
If you disagree, please describe a scenario in which an InnoDB page
checksum does anything useful if the file system it is on has built in
block checksumming and data redundancy.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 7:02 PM Reindl Harald  wrote:

> >> MariaDB Server 10.4 introduced a new file format
> >> innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
> >> the default. Any files that were created when that setting is active
> >> are guaranteed to write any unused bytes as zeroes. It also fixes a
> >> peculiar design decision that some bytes of the page are not covered
> >> by any checksum, and that a page is considered valid if any of the
> >> non-full_crc32 checksums happen to produce a match. This includes the
> >> magic 0xdeadbeef for innodb_checksum_algorithm=none.
> >>
> >> Maybe we should consider eventually deprecating write support for the
> >> non-full_crc32 format, to force a fresh start.
> >
> > Please don't. Some of us run MariaDB on file systems that do their own
> > block checksumming, and thus run innodb_checksum_algorithm=none
>
> that's nonsense - when mariadb writes wrong data in it's files no
> filesystem can magically fix that

MariaDB can't fix it either. And if that is what happened, there is no
benefit to duplicating the effort.

> you need to understand what innodb checksums are and then it's logical
> that the file-system layer is a completly different world
>
> https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum

You need to understand that properly thought out and sensibly written
file systems (which is, granted, pretty rare, I know of a total of 1)
implicitly prevent torn pages from being possible.
So the checksum and the doublewrite are completely redundant in such
cases, and can be safely disabled.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 6:59 PM Reindl Harald  wrote:
>
>
>
> Am 08.12.22 um 17:51 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 6:43 PM Reindl Harald  wrote:
> >
> >> and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which
> >> hold data even in file-per-table mode you can't cleanup from crap caused
> >> by a crash 13 years ago
> >
> > How can that happen when every once in a while upgrades require a full
> > clean shutdown with innodb_fast_shutdown=0 so that the ib_logfile*
> > have to be rebuild due to an on-disk format change?
>
> in my whole life from MySQL 5.0 (the frist time i built mysql with
> innodb support at all) to MariaDB 10.3 i didn't do anything then
> upgrade, restart service and be done

I don't have an explanation for that, short of pure luck, in the fact
that the log was naturally empty because everything had been flushed
to disk at the point you upgraded.
You should play the lottery more often if your luck is that good. Mine
certainly isn't.

> > More to the point, if that is a concern, why can you not shut down
> > cleanly with innodb_fast_shutdown=0, rm the files, and let MariaDB
> > re-create them completely clean and empty?
>
> don't ask me why that shit can't be removed automatically over 13 years
> - the reference to './dbmail/#sql2-704-271.ibd' lives somewhere in the
> "global tablespace" which shouldn't exist at all

I have seen phantom InnoDB tables like that before, but never ones
that coiuldn't be cleared with a simple "drop table".
Does it show up in innodb_sys_tables or innodb_sys_tablespaces?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 6:43 PM Reindl Harald  wrote:

> and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which
> hold data even in file-per-table mode you can't cleanup from crap caused
> by a crash 13 years ago

How can that happen when every once in a while upgrades require a full
clean shutdown with innodb_fast_shutdown=0 so that the ib_logfile*
have to be rebuild due to an on-disk format change?
More to the point, if that is a concern, why can you not shut down
cleanly with innodb_fast_shutdown=0, rm the files, and let MariaDB
re-create them completely clean and empty?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

[Gordan Bobic]

On Thu, Dec 8, 2022 at 6:25 PM Marko Mäkelä  wrote:

> MariaDB Server 10.4 introduced a new file format
> innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
> the default. Any files that were created when that setting is active
> are guaranteed to write any unused bytes as zeroes. It also fixes a
> peculiar design decision that some bytes of the page are not covered
> by any checksum, and that a page is considered valid if any of the
> non-full_crc32 checksums happen to produce a match. This includes the
> magic 0xdeadbeef for innodb_checksum_algorithm=none.
>
> Maybe we should consider eventually deprecating write support for the
> non-full_crc32 format, to force a fresh start.

Please don't. Some of us run MariaDB on file systems that do their own
block checksumming, and thus run innodb_checksum_algorithm=none,
and/or have databases many terabytes in size. With terabyte size
databases, doing a mysqldump+restore is realistically _never_ going to
happen.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Libguestfs] [zfs-discuss] ZFS-on-NBD

[Gordan Bobic]

Isn't ZFS getting native object storage capability soon? It was showcased
at the last OpenZFS summit.


On Mon, 12 Sep 2022, 23:53 Nikolaus Rath,  wrote:

> Hi all,
>
> In case people have been wondering about the background of the various
> questions that I asked on these lists in the last few months:
>
> I've been experimenting with running ZFS-on-NBD as a cloud backup solution
> (and potential alternative to S3QL, which I am using for this purpose at
> the moment).
>
> In case someone is interested, here is the (rather long) write-up:
> https://www.rath.org/s3ql-vs-zfs-on-nbd.html
>
> Best,
> -Nikolaus
>
> --
> GPG Fingerprint: ED31 791B 2C5C 1613 AF38 8B8A D113 FCAC 3C4E 599F
>
>  »Time flies like an arrow, fruit flies like a Banana.«
>
>
> *zfsonlinux * / Discuss / see
> discussions  +
> participants 
> + delivery options
> 
> Permalink
> 
>
___
Libguestfs mailing list
Libguestfs@redhat.com
https://listman.redhat.com/mailman/listinfo/libguestfs

Re: [Maria-discuss] "Rewind" type of mechanisms?

[Gordan Bobic]

On Fri, Aug 19, 2022 at 3:05 PM Sam R.  wrote:
>
> Thank you again!
>
> Sam wrote:
>
> Q: > > resync automatically --> Does it remove a possible (obsolete) rogue 
> row?
>
> Gordan Bobic wrote:
>
> A: >  In asynchronous replication, yes it does.
>
> Only to double check the same. To make sure we are discussing the same 
> context.
>
> So, we are talking in the following context: in the "rewind" type of 
> situation? (Rogue row.)
> Rogue row is on old Master. Old Master has been switched to Standby mode. --> 
> Rogue row gets rewinded automatically?
>
> In other words,
> With rogue row and rewind, I mean following situation:
>
> Master -- Slave
>
> 1. Row is inserted to Master:
> Master (new row) -- Slave
>
> 2. The row does not end up on Slave DB node. E.g. because of a sudden 
> failover.
>
> 3. So:
> Master (has rogue row)  --  Slave (rogue row does not end up on the Slave 
> node)
>
> 4. The sudden failover happens in the middle.
>
> ==>
> 5. The previous Master becomes New Slave:
>
> New Slave (old Master, with the rogue row)  -- New Master (old Slave)
>
> 6. Now: "New Slave" has the rogue row. Such rogue row would need to be 
> rewinded.
>
> So, only to double check (repeating the same question).
> Q: MariaDB is able to rewind such automatically?
>
> Is my understanding correct?
>
> Async replication would be in use, with MaxScale in use.

When the old master comes back up, the new master will get the missing
transaction provided it doesn't violate any constraints.
If you want to avoid that race condition, then you will have to use
Galera, because in Galera the slave cannot be behind.
There is no built in rewind option to roll back a committed
transaction that I am aware of - though it probably could be
implemented by reverse playback of row based binlog.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] "Rewind" type of mechanisms?

[Gordan Bobic]

On Fri, Aug 19, 2022 at 12:37 PM Sam R.  wrote:
>
> Thank you!
>
> > "You can automatically resync either with gtid based async replication"
>
> Few questions:
> resync automatically --> Does it remove a possible (obsolete) rogue row?

In asynchronous replication, yes it does.
In Galera, it doesn't because the node doesn't come online until it is
fully in sync, and replication in Galera is synchronous.

> How long task is such resyncing operation? --> Does it "recreate" the whole 
> DB node?

Provided the replication logs on the other node haven't expired, it
will be an incremental update.
In async replication, that will be done by applying missing
transactions from the donor node.
In Galera it will be done by doing an incremental state transfer,
logically they amount to the same thing.

If the replication logs or the galera cache have expired while the
node was down, Galera will do a full state transfer (all data from
scratch) automatically.
With async replication in that case you will have to reseed the node
yourself using mariabackup or one of several other approaches, but
this is reasonably well automatable.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] "Rewind" type of mechanisms?

[Gordan Bobic]

You can automatically resync either with gtid based async replication
or with Galera without full reseeding.

On Fri, Aug 19, 2022 at 11:57 AM Sam R.  wrote:
>
> Hi!
>
> I would like to know about following topic regarding MariaDB.
> We would like to run DB cluster with HA (high availability). -- E.g. with 
> MaxScale.
>
> My Q:
> Does MariaDB support such mechanism which would be similar to pg_rewind? 
> Reference: PostgreSQL supports such a rewind operation.
> Or, are there plans to support such regarding MariaDB?
> ( If such would be needed / useful. )
>
> Rewind: "A typical scenario is to bring an old primary server back online 
> after failover as a standby that follows the new primary."
>
> Target: robustness/stability of a DB cluster. Avoiding needs to manually fix 
> DB cluster.
>
> Best Regards
> Sam
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Some questions about mariadb-dump and mariabackup

[Gordan Bobic]

On Tue, Aug 2, 2022 at 2:47 PM Cédric Counotte
 wrote:
>
> I will partially respond to your questions:
>
> -skip-lock-tables, avoids locking the table when dumping them.
> This means if you are on production servers, the dump won't 
> freeze/slow your activity, however the dump may be incomplete or not 
> reflecting an atomic state.
>
> -single-transaction
> This as far as I know is only the way the INSERT commands will be 
> written to the dump. With multiple inserts or a single one.

Not quite. --skip-lock-tables dumps the data without locking the
tables. You are telling MariaDB that you don't care if the tables are
mutually consistent during the dump.
The --single-transaction option means that it will grab a brief
metadata lock and start a transaction, and then dump everything from
within that transaction, thus ensuring that the dump is coherent and
internally consistent, while still avoiding long table locking.

> As for mariabackup, I don't think the prepare command needs access to the 
> actual source folder, as everything has been copied. The prepare seems to 
> replace the last bin log, but you might want to seek for someone else 
> confirmation.
>
> FWIW, I stopped using mariabackup for backup (using it to start replication 
> only) because restoring a single DB (we have 300+) or a single table seems 
> highly cumbersome because you'll have to restore the entire backup to a 
> working MariaDB server, and then dump/import the tables/DB you need onto your 
> production server. Had to do it once, never again. So now I'm using plain 
> mysqldump for which I easily control the output and how I'll be able to 
> retrieve a DB or single table!

Unfortunately, mysqldump/mariadbdump are completely useless for big
databases. Anything more than about 50GB is just too painfully slow.
There are usually better ways to achieve this with well thought out
infrastructures unless your database sizes are very small.

> ** mariabackup
>
> We have a setup that stores the binary logs under /var/log/mysql (set up with 
> the log_bin configuration option) and the databases themselves under 
> /var/lib/mysql
>
> Suppose I call mariabackup to make a backup of the database as follows (some 
> options obmitted):
>
>   mariabackup --backup --target-dir=my_backup_dir
>
> Could you tell me if calling
>
>   mariabackup --prepare  --target-dir=my_backup_dir
>
> needs access to the binary logs stored under /var/log/mysql or if mariabackup 
> made a copy of all that it needs in my_backup_dir?

No, it only needs access to the what is in the backup directory. What
--prepare does is applies the redo log changes it buffered up during
the backup to make the backup set fully consistent.
There are no circumstances under which mariabackup will need access to
binary replication logs.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

He was far too low on table_open_cache which was in turn bumping into the
LimitNOFILE limit.

On Thu, 28 Jul 2022, 22:57 ,  wrote:

> I tried to follow but missed the solution.
> What OS limit are you talking about?
>
> Thanks,
> Eliezer
>
> 
> Eliezer Croitoru
> NgTech, Tech Support
> Mobile: +972-5-28704261
> Email: ngtech1...@gmail.com
> Web: https://ngtech.co.il/
> My-Tube: https://tube.ngtech.co.il/
>
> -Original Message-
> From: Maria-discuss  gmail@lists.launchpad.net> On Behalf Of Cédric Counotte
> Sent: Thursday, 28 July 2022 21:07
> To: Gordan Bobic 
> Cc: Mailing-List mariadb ; Pierre
> LAFON 
> Subject: Re: [Maria-discuss] MariaDB server horribly slow on start
>
> I had to restart a server earlier and with this new setting, everything
> was smooth after the SST ! for the second time in a row.
>
> I just started another server, and it did its SST and worked just fine
> immediately! Wow! I never imagined such an OS limit would be imposed and
> cause so much trouble! It ought to be documented if not already (then I
> missed it).
>
> Thanks a ton for your help! That's a big problem solved!
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

I suggest you get PMM installed. With it this troubleshooting would have
taken someone familiar with it 1% of the time we all spent typing emails on
this thread.


On Thu, 28 Jul 2022, 21:07 Cédric Counotte, 
wrote:

> I had to restart a server earlier and with this new setting, everything
> was smooth after the SST ! for the second time in a row.
>
> I just started another server, and it did its SST and worked just fine
> immediately! Wow! I never imagined such an OS limit would be imposed and
> cause so much trouble! It ought to be documented if not already (then I
> missed it).
>
> Thanks a ton for your help! That's a big problem solved!
>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

On Thu, Jul 28, 2022 at 12:07 PM Cédric Counotte
 wrote:
>
> Well, one server crashed twice a few days ago and I've asked my service 
> provided (OVH) to look into it, but they asked me to test the hardware 
> myself, found a NVMe disk with 17000+ errors, still waiting for their 
> feedback on this.

It sounds like you need:
1) ZFS
2) Better monitoring

> Only our 2 oldest servers are experiencing crashes (6 months old only!), and 
> it turns out the RAID NVMe have very different written data, one disk has 
> 58TB (not a replacement) while the other is at 400+TB within the same RAID ! 
> All other servers have identical written data size on both disks of their 
> RAID, so it seems we got used disks and that those are having issues.

Welcome to the cloud. But this is not a bad thing, it's better than
having multiple disks in the same array fail at the same time.
ZFS would help you by catching those errors before the database
ingests them. In normal non-ZFS RAID, it is plausible and even quite
probable that the corrupted data will be loaded from disk and
propagate to other nodes, either via a state transfer or via corrupted
binlogs.
ZFS prevents that by making sure every block's checksum is compared at
read time and any errors that show up get recovered from other
redundant disks.

Under the current circumstances, I wouldn't trust your data integrity
until you run a full extended table check on all tables on all nodes.
And probably pt-table-checksum on all the tables between the nodes to make sure.

> Still didn't have time to produce a crash dump and post an issue with those 
> (to confirm the cause) as I kept having to deal with server restarts trying 
> to reduce the slow issue for 30 minutes to one hour.

you need to be careful with that - state transfer from a node with
failing disks can actually result in the corrupted data propagating to
the node being bootstrapped.

> There was issues with slave thread crashing which I posted an issue and got 
> to update MariaDB to resolve, still there are issues with slave threads 
> stopping without reason so I have written a script to restart it and posted 
> an issue with that.

I don't think you can meaningfully debug anything until you have
verified that your hardware is reliable.
Do your OVH servers have ECC memory?

> The original objective was to have 2 usable cluster in different sites, 
> synched with each other using replication, however all those issues have not 
> allowed us to move forward with this.

With 4 nodes across 2 DCs, you are going to lose writability if you
lose a DC even if it is the secondary DC.
Your writes are also going to be very slow because with 4 nodes, all
writes have to be acknowledged by 3 nodes - and the 3rd node is always
going to be slow because it is connected over a WAN.
I would seriously question whether Galera is the correct solution for you.
And that's on top of writing to multiple nodes which will make things
far worse on top.

> Not to mention the fact that we are now using OVH load balancer and that 
> piece of hardware is sometimes thinking all our servers are down and starts 
> showing error 503 to our customers while our servers are just running fine 
> (no restart, no issue, nothing). So one more issue to deal with, for which 
> we'll get a dedicated server and configure our own load balancer we can have 
> control on.

I think you need to take a long hard look at what you are trying to
achieve and re-assess:
1) Whether it is actually achievable sensibly within the constraints you imposed
2) What the best workable compromise is between what you want and what
you can reasonably have

Right now, I don't think you have a solution that is likely to be workable.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

Servers shouldn't be crashing. If they are crashing you need to
establish why and deal with it.
Uptimes of years with MariaDB are not uncommon. Or at least months
even among the security conscious who patch with every release cycle.

On Thu, Jul 28, 2022 at 11:48 AM Cédric Counotte
 wrote:
>
> Well, turns out the last attached server crashed and did an IST, the setting 
> described below was already applied, and the issue didn't show up!?
>
>
>
> I'll try again later today to confirm, but it looks very positive 
>
>
>
> [ It might be a false positive as I've seen 2 restarts goes smooth in the 
> past while there are just too many restarts having gone haywire. ]
>
>
>
>
>
>
>
> -Message d'origine-
> De : Cédric Counotte
> Envoyé : jeudi 28 juillet 2022 10:24
> À : Gordan Bobic 
> Cc : jocelyn fournier ; Marko Mäkelä 
> ; Mailing-List mariadb 
> ; Pierre LAFON 
> Objet : RE: [Maria-discuss] MariaDB server horribly slow on start
>
>
>
> I've prepared all servers with that new setting, and this (is it ok or should 
> I set it to 1048576 as well?):
>
> table_open_cache = 65536
>
>
>
> I'll do the server restart this evening to avoid creating problems during the 
> day.
>
>
>
> I did try to restart the backup cluster (2 nodes, one slave of the main 
> cluster) and it didn't seem to slowdown the slave as it used to, so that 
> might be the solution (or part of)!
>
>
>
> Thanks a lot for your time, will keep you posted later today when I restart a 
> node of the main cluster.
>
>
>
>
>
> -Message d'origine-
>
> De : Gordan Bobic  Envoyé : jeudi 28 juillet 2022 
> 10:06 À : Cédric Counotte  Cc : jocelyn fournier 
> ; Marko Mäkelä ; 
> Mailing-List mariadb ; Pierre LAFON 
>  Objet : Re: [Maria-discuss] MariaDB server horribly 
> slow on start
>
>
>
> On Thu, Jul 28, 2022 at 10:56 AM Cédric Counotte  
> wrote:
>
> >
>
> > Concerning table_open_cache, it’s currently set to 13869 (however in config 
> > it’s set to 16384), global status shows this on new node, 9 hours after 
> > start:
>
>
>
> That means you are out of file handles at systemd level.
>
> systemctl edit mariadb
>
> and add this to the override file:
>
> [Service]
>
> LimitNOFILE=1048576
>
>
>
> systemctl daemon reload
>
> systemctl restart mariadb
>
>
>
> Yes, this will probably trigger the problem you are having, but with some 
> luck it may make it better in the future.
>
> Do that on all nodes.
>
>
>
> > +---+-+
>
> >
>
> > | Table_open_cache_active_instances | 1   |
>
> >
>
> > | Table_open_cache_hits | 2136757 |
>
> >
>
> > | Table_open_cache_misses   | 185097  |
>
> >
>
> > | Table_open_cache_overflows| 146153  |
>
> >
>
> > +---+-+
>
> >
>
> > +---++
>
> >
>
> > | Opened_tables | 159629 |
>
> >
>
> > +---++
>
> >
>
> >
>
> >
>
> > I’ve updated table_open_cache to 65536 on 2 servers and the cache_overflows 
> > stops increasing.
>
> >
>
> >
>
> >
>
> > In /usr/lib/systemd/system/mariadb.service, I see those:
>
> >
>
> > LimitNOFILE=32768
>
>
>
> That is way too low. This needs to be big enough to cover the sum total of:
>
> max_connections
>
> table_open_cache x2 (because innodb_open_files is separate)
>
>
>
> There is generally no harm in bumping LimitNOFILE much higher on modern 
> kernels.
>
>
>
> > So I don’t understand why MariaDB decided to reduce the configured value? 
> > Not sure if changing the config will have any effect on the live value 
> > either? I’ll try to set both to 65536 this evening and see if it helps.
>
>
>
> Because it tries to make sure that your total of the above mentioned settings 
> fits in the number of file handles it has available to it.
>
>
>
> > Is it safe to increase both limits? Maybe to the value I use during 
> > mariabackup, which is 919200?
>
>
>
> Yes, see above. But it requires a daemon-reload and a restart of the service 
> to take effect.
>
>
>
> > All active nodes are used for writing, the HTTP load is spread evenly on 
> > all nodes. The ratio is 1.2% writes at 7780 read/sec with 125 write/sec on 
> > peak. Both read/writes are spread upon all active nodes using a load 
> > balancer using round-robin at the moment.
&g

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

On Thu, Jul 28, 2022 at 11:24 AM Cédric Counotte
 wrote:
>
> I've prepared all servers with that new setting, and this (is it ok or should 
> I set it to 1048576 as well?):
> table_open_cache = 65536

No, remember that LimitNOFILE has to fit at least the sum of all of
the settings I mentioned, and still leave plenty of headroom.
If you had PMM up and running you could see at a glance whether your
table_open_cache is big enough.

> I'll do the server restart this evening to avoid creating problems during the 
> day.
>
> I did try to restart the backup cluster (2 nodes, one slave of the main 
> cluster) and it didn't seem to slowdown the slave as it used to, so that 
> might be the solution (or part of)!
>
> Thanks a lot for your time, will keep you posted later today when I restart a 
> node of the main cluster.

IMO you are probably experiencing bigger problems from writing to
multiple nodes at the same time.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

On Thu, Jul 28, 2022 at 10:56 AM Cédric Counotte
 wrote:
>
> Concerning table_open_cache, it’s currently set to 13869 (however in config 
> it’s set to 16384), global status shows this on new node, 9 hours after start:

That means you are out of file handles at systemd level.
systemctl edit mariadb
and add this to the override file:
[Service]
LimitNOFILE=1048576

systemctl daemon reload
systemctl restart mariadb

Yes, this will probably trigger the problem you are having, but with
some luck it may make it better in the future.
Do that on all nodes.

> +---+-+
>
> | Table_open_cache_active_instances | 1   |
>
> | Table_open_cache_hits | 2136757 |
>
> | Table_open_cache_misses   | 185097  |
>
> | Table_open_cache_overflows| 146153  |
>
> +---+-+
>
> +---++
>
> | Opened_tables | 159629 |
>
> +---++
>
>
>
> I’ve updated table_open_cache to 65536 on 2 servers and the cache_overflows 
> stops increasing.
>
>
>
> In /usr/lib/systemd/system/mariadb.service, I see those:
>
> LimitNOFILE=32768

That is way too low. This needs to be big enough to cover the sum total of:
max_connections
table_open_cache x2 (because innodb_open_files is separate)

There is generally no harm in bumping LimitNOFILE much higher on modern kernels.

> So I don’t understand why MariaDB decided to reduce the configured value? Not 
> sure if changing the config will have any effect on the live value either? 
> I’ll try to set both to 65536 this evening and see if it helps.

Because it tries to make sure that your total of the above mentioned
settings fits in the number of file handles it has available to it.

> Is it safe to increase both limits? Maybe to the value I use during 
> mariabackup, which is 919200?

Yes, see above. But it requires a daemon-reload and a restart of the
service to take effect.

> All active nodes are used for writing, the HTTP load is spread evenly on all 
> nodes. The ratio is 1.2% writes at 7780 read/sec with 125 write/sec on peak. 
> Both read/writes are spread upon all active nodes using a load balancer using 
> round-robin at the moment.

That is likely a part of your problem.
You should never ever use more than one Galera node for writing at a time.
Performance will be WORSE than performance of a single node, and you
will get deadlocks all over the place.
You can use any of them for reading, but you should never use more
than one at a time for writing.
It is a little concerning that you managed to get as far as putting
Galera into production for months without full awareness of this.

> During yesterday’s test the existing 2 nodes where active at first. Seeing 
> the queries starting to be stuck I decided to activate the new node to spread 
> the load in hope for some improvements, however it just made things even 
> worse, so I deactivated it again.

The more writable nodes you have the worse the performance will get.
There should only ever be one writable node at a time.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

You should hardly ever need to adjust table_open_cache_instances away from
defaults, but it sounds like you may need into bump your table_open_cache
by a 2-4x.

Are you using more than one node for writing? And does the new node get
used for anything immediately after joining?

On Thu, 28 Jul 2022, 01:20 Cédric Counotte, 
wrote:

> I just tested using this setting, but it made no difference:
> innodb_max_dirty_pages_pct_lwm=0.001
>
> Tested with 2 existing nodes running and sync'ed, and one new node being
> attached.
>
> As soon as SST sync ended, the first 2 nodes started piling queries,
> mostly stuck in commit.
> If the new node was used, all queries got stuck in opening tables.
>
> Please see attached screenshots showing part of the queues, which quickly
> reach 100+.
>
> It took about 20 minutes to recover from that situation without using the
> new node at all! It took another 20 minutes for the new node to become
> usable (eg not too slow).
>
>
> Restarted server, it did an IST, exact same result, just worse, pending
> queries above 200 this time!
> How is it possible for queries to be stuck for several minutes (10!!!)
> while in nominal state they take less than 0.2 seconds !?
>
>
> Saw this in logs just after the IST, however table_open_cache_instances =
> 16 is set in config file and live value is indeed 1!?
>
> 2022-07-27 23:55:09 11 [Warning] Detected table cache mutex contention at
> instance 1: 30% waits. Additional table cache instance cannot be activated:
> consider raising table_open_cache_instances. Number of active instances: 1.
>
>
> FWIW we have 350+ DB each with 100 tables, and doing a mariabackup
> requires ulimit -n 919200, servers are processing about 3.000
> queries/seconds during those tests, while it peaks at 10.000 during the
> day. A single server is capable of handling the peak load (verified earlier
> this morning) !
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

Replaying a gigabyte of a redo log takes seconds on hardware from this century.
When it comes to redo log sizing, there are two bounds:

Upper bound: big enough to soak your peak hour of writes.

Lower bound: big enough that your checkpoint age never hits the
effective redo log size. To provide a margin for error, I normally try
to make it never exceed 80% of redo log size.

There is such a thing as overtuning. If you are seeing crashes
regularly and have no redundant infrastructure for failover, tuning
redo log size to reduce crash recovery is wasting time fixing the
wrong problem.




On Wed, Jul 27, 2022 at 4:26 PM jocelyn fournier
 wrote:
>
> It’s not that tiny, this article is a bit old, but still valid:
>
> https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
>
> If you don’t need a big redo log, reduce its side to avoid slow crash recovery
>
> > Le 27 juil. 2022 à 15:23, Gordan Bobic  a écrit :
> >
> > 10.5+ only uses a single log file, so that is 1x1GB.
> > And 1GB is tiny, IMO it should be a default these days.
> > I would only even consider something smaller if I was running on an
> > older Raspberry Pi or something similarly constrained.
> >
> >
> > On Wed, Jul 27, 2022 at 4:11 PM jocelyn fournier
> >  wrote:
> >>
> >> Hi Cédric!
> >>
> >> Just to be sure, do you really need the 2x 1G log_file_size ?
> >>
> >> BR,
> >>  Jocelyn Fournier
> >>
> >>> Le 27 juil. 2022 à 14:36, Cédric Counotte  a 
> >>> écrit :
> >>>
> >>> Reading this: https://jira.mariadb.org/browse/MDEV-27295
> >>>
> >>> It's quite unclear when it is fixed or reverted.
> >>>
> >>> That said I read that the following setting might fix it:
> >>>  SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
> >>>
> >>> Is that correct and should I try that and see if that helps?
> >>>
> >>>
> >>> -Message d'origine-
> >>> De : Gordan Bobic 
> >>> Envoyé : mercredi 27 juillet 2022 14:29
> >>> À : Marko Mäkelä 
> >>> Cc : Cédric Counotte ; Mailing-List mariadb 
> >>> 
> >>> Objet : Re: [Maria-discuss] MariaDB server horribly slow on start
> >>>
> >>> On Wed, Jul 27, 2022 at 3:08 PM Marko Mäkelä  
> >>> wrote:
> >>>>
> >>>> On Wed, Jul 27, 2022 at 2:48 PM Gordan Bobic  
> >>>> wrote:
> >>>>>
> >>>>> There is no supported downgrade path other than logical dump+restore.
> >>>>> There are also no packages built for distros where the major version is 
> >>>>> older than what ships with the distro.
> >>>>>
> >>>>> Since your queries seem to end up stuck in commit stage, it could be 
> >>>>> related to redo log flushing, which behaves very erratically on 10.5+. 
> >>>>> If it leaves the log to fill up to 90% and the state transfer hits, it 
> >>>>> could be that with the checkpoint age already high, there just isn't 
> >>>>> enough headroom to avoid a massive stall. Purely guessing here without 
> >>>>> any telemetry.
> >>>>
> >>>> I think that you may refer to InnoDB page flushing. There was some
> >>>> misunderstanding around that, and indeed some partly unintended or
> >>>> uninformed changes in behaviour (in 10.5.7 and 10.5.8) that were
> >>>> reverted later. It could be useful to read
> >>>> https://jira.mariadb.org/browse/MDEV-27295.
> >>>
> >>> What version was it reverted in?
> >>> I am still seeing the errant redo log flushing behaviour in 10.5.15.
> >>> It looks like no flushing happens until the hwm is reached at about 85% 
> >>> full. It then tries to commit everything down to the lwm. And inbetween 
> >>> it doesn't do anything, even while everything is idle and it should be 
> >>> running down the
> >>> ___
> >>> Mailing list: https://launchpad.net/~maria-discuss
> >>> Post to : maria-discuss@lists.launchpad.net
> >>> Unsubscribe : https://launchpad.net/~maria-discuss
> >>> More help   : https://help.launchpad.net/ListHelp
> >>
>

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

10.5+ only uses a single log file, so that is 1x1GB.
And 1GB is tiny, IMO it should be a default these days.
I would only even consider something smaller if I was running on an
older Raspberry Pi or something similarly constrained.


On Wed, Jul 27, 2022 at 4:11 PM jocelyn fournier
 wrote:
>
> Hi Cédric!
>
> Just to be sure, do you really need the 2x 1G log_file_size ?
>
> BR,
>   Jocelyn Fournier
>
> > Le 27 juil. 2022 à 14:36, Cédric Counotte  a 
> > écrit :
> >
> > Reading this: https://jira.mariadb.org/browse/MDEV-27295
> >
> > It's quite unclear when it is fixed or reverted.
> >
> > That said I read that the following setting might fix it:
> >   SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
> >
> > Is that correct and should I try that and see if that helps?
> >
> >
> > -Message d'origine-
> > De : Gordan Bobic 
> > Envoyé : mercredi 27 juillet 2022 14:29
> > À : Marko Mäkelä 
> > Cc : Cédric Counotte ; Mailing-List mariadb 
> > 
> > Objet : Re: [Maria-discuss] MariaDB server horribly slow on start
> >
> > On Wed, Jul 27, 2022 at 3:08 PM Marko Mäkelä  
> > wrote:
> >>
> >> On Wed, Jul 27, 2022 at 2:48 PM Gordan Bobic  
> >> wrote:
> >>>
> >>> There is no supported downgrade path other than logical dump+restore.
> >>> There are also no packages built for distros where the major version is 
> >>> older than what ships with the distro.
> >>>
> >>> Since your queries seem to end up stuck in commit stage, it could be 
> >>> related to redo log flushing, which behaves very erratically on 10.5+. If 
> >>> it leaves the log to fill up to 90% and the state transfer hits, it could 
> >>> be that with the checkpoint age already high, there just isn't enough 
> >>> headroom to avoid a massive stall. Purely guessing here without any 
> >>> telemetry.
> >>
> >> I think that you may refer to InnoDB page flushing. There was some
> >> misunderstanding around that, and indeed some partly unintended or
> >> uninformed changes in behaviour (in 10.5.7 and 10.5.8) that were
> >> reverted later. It could be useful to read
> >> https://jira.mariadb.org/browse/MDEV-27295.
> >
> > What version was it reverted in?
> > I am still seeing the errant redo log flushing behaviour in 10.5.15.
> > It looks like no flushing happens until the hwm is reached at about 85% 
> > full. It then tries to commit everything down to the lwm. And inbetween it 
> > doesn't do anything, even while everything is idle and it should be running 
> > down the
> > ___
> > Mailing list: https://launchpad.net/~maria-discuss
> > Post to : maria-discuss@lists.launchpad.net
> > Unsubscribe : https://launchpad.net/~maria-discuss
> > More help   : https://help.launchpad.net/ListHelp
>

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

You can try it, but that isn't a fix, that is a way to make the
flushing run all the time at full rate.
If I remember correctly, old behaviour was that the flushing would
happen at innodb_io_capacity rate, and above the hwm, it would kick
into innodb_io_capacity_max rate.
Or something along those lines.
On 10.5+ you get only two speeds, 0 and whatever your disks can handle
(which can also starve other I/O)
Whatever the "improvement" intended was, the outcome is a substantial downgrade.

On Wed, Jul 27, 2022 at 3:36 PM Cédric Counotte
 wrote:
>
> Reading this: https://jira.mariadb.org/browse/MDEV-27295
>
> It's quite unclear when it is fixed or reverted.
>
> That said I read that the following setting might fix it:
> SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
>
> Is that correct and should I try that and see if that helps?
>
>
> -Message d'origine-
> De : Gordan Bobic 
> Envoyé : mercredi 27 juillet 2022 14:29
> À : Marko Mäkelä 
> Cc : Cédric Counotte ; Mailing-List mariadb 
> 
> Objet : Re: [Maria-discuss] MariaDB server horribly slow on start
>
> On Wed, Jul 27, 2022 at 3:08 PM Marko Mäkelä  wrote:
> >
> > On Wed, Jul 27, 2022 at 2:48 PM Gordan Bobic  wrote:
> > >
> > > There is no supported downgrade path other than logical dump+restore.
> > > There are also no packages built for distros where the major version is 
> > > older than what ships with the distro.
> > >
> > > Since your queries seem to end up stuck in commit stage, it could be 
> > > related to redo log flushing, which behaves very erratically on 10.5+. If 
> > > it leaves the log to fill up to 90% and the state transfer hits, it could 
> > > be that with the checkpoint age already high, there just isn't enough 
> > > headroom to avoid a massive stall. Purely guessing here without any 
> > > telemetry.
> >
> > I think that you may refer to InnoDB page flushing. There was some
> > misunderstanding around that, and indeed some partly unintended or
> > uninformed changes in behaviour (in 10.5.7 and 10.5.8) that were
> > reverted later. It could be useful to read
> > https://jira.mariadb.org/browse/MDEV-27295.
>
> What version was it reverted in?
> I am still seeing the errant redo log flushing behaviour in 10.5.15.
> It looks like no flushing happens until the hwm is reached at about 85% full. 
> It then tries to commit everything down to the lwm. And inbetween it doesn't 
> do anything, even while everything is idle and it should be running down the

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

On Wed, Jul 27, 2022 at 3:08 PM Marko Mäkelä  wrote:
>
> On Wed, Jul 27, 2022 at 2:48 PM Gordan Bobic  wrote:
> >
> > There is no supported downgrade path other than logical dump+restore.
> > There are also no packages built for distros where the major version is 
> > older than what ships with the distro.
> >
> > Since your queries seem to end up stuck in commit stage, it could be 
> > related to redo log flushing, which behaves very erratically on 10.5+. If 
> > it leaves the log to fill up to 90% and the state transfer hits, it could 
> > be that with the checkpoint age already high, there just isn't enough 
> > headroom to avoid a massive stall. Purely guessing here without any 
> > telemetry.
>
> I think that you may refer to InnoDB page flushing. There was some
> misunderstanding around that, and indeed some partly unintended or
> uninformed changes in behaviour (in 10.5.7 and 10.5.8) that were
> reverted later. It could be useful to read
> https://jira.mariadb.org/browse/MDEV-27295.

What version was it reverted in?
I am still seeing the errant redo log flushing behaviour in 10.5.15.
It looks like no flushing happens until the hwm is reached at about
85% full. It then tries to commit everything down to the lwm. And
inbetween it doesn't do anything, even while everything is idle and it
should be running down the

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

On Wed, Jul 27, 2022 at 2:38 PM Cédric Counotte
 wrote:
>
> No idea what is PMM ?

https://www.percona.com/software/database-tools/percona-monitoring-and-management

I prefer v1, but in Percona's build of PMM v1 there are several bugs /
missing features that are going to cause you problems with newer
kernels and MariaDB 10.5+.
I have patches to fix it, but my build of it is not yet publicly
available. But v2 is still far better than no PMM.

> Do you have a bug tracking this behavior, so that maybe I can follow it or 
> apply work-around ?

I cannot say I have looked. I'm generally happy to leave testing to
others until I have an overwhelming reason to upgrade to a version
with a lot of changes. And 10.5 was a rather big feature release.

> I’m not quite keen on downgrading (which I’ll do using the spare servers I 
> have and a little replication in between), but if it turns out to provide a 
> more reliable alternative I’m eager to do it then!

Replication from newer version to older version is also not officially
supported. It usually works, though.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

There is no supported downgrade path other than logical dump+restore.
There are also no packages built for distros where the major version is
older than what ships with the distro.

Since your queries seem to end up stuck in commit stage, it could be
related to redo log flushing, which behaves very erratically on 10.5+. If
it leaves the log to fill up to 90% and the state transfer hits, it could
be that with the checkpoint age already high, there just isn't enough
headroom to avoid a massive stall. Purely guessing here without any
telemetry.

So it seems you are, most unfortunately, in for an awful lot of pain.

You seem to have multiple instances of some settings in your config, that
is never a good sign.

Do you have PMM set up on this cluster and fully instrumented?


On Wed, 27 Jul 2022, 14:04 Cédric Counotte, 
wrote:

> I started with 10.4.x back in January, exact same issue.
>
>
>
> Now on Ubuntu 22.04 it comes with MariaDB 10.6 and I was forced to upgrade!
>
>
>
> I’ll test downgrading somehow, but on mariadb downloads I can’t find 22.04
> version?!
>
> Download MariaDB Server - MariaDB.org
> <https://mariadb.org/download/?t=repo-config=21.10+%22impish%22=10.5_m=mva>
>
>
>
>
>
> *De :* Gordan Bobic 
> *Envoyé :* mercredi 27 juillet 2022 13:02
> *À :* Cédric Counotte 
> *Cc :* William Edwards ;
> maria-discuss@lists.launchpad.net
> *Objet :* Re: [Maria-discuss] MariaDB server horribly slow on start
>
>
>
> IMO your biggest cause of problems is chasing the bleeding edge.
>
> You are on 10.6.x. I generally don't even deploy 10.5.x in production yet
> except in very exceptional circumstances (simple async replication, no
> expectation of extreme performance).
>
> Migrate to 10.4.x and stick with it until it goes EOL. Then maybe think
> about upgrading to 10.5.x in the 6 months following 10.4.x EOL.
>
>
>
> On Wed, Jul 27, 2022 at 1:50 PM Cédric Counotte <
> cedric.couno...@1check.com> wrote:
>
> I must add that the slowness occurs when the SST or IST are completed, and
> the server becomes available. Donor is also entirely blocked (it seems)
> when the queue is being processed.
>
>
>
> While the SST is performed, there is no issue.
>
>
>
> *De :* William Edwards 
> *Envoyé :* mercredi 27 juillet 2022 11:58
> *À :* Cédric Counotte 
> *Cc :* maria-discuss@lists.launchpad.net
> *Objet :* Re: [Maria-discuss] MariaDB server horribly slow on start
>
>
>
>
>
> Op 27 jul. 2022 om 11:46 heeft Cédric Counotte 
> het volgende geschreven:
>
> 
>
> Hello all. I hope I’m at the right place to ask this question.
>
> I opened a bug here: https://jira.mariadb.org/browse/MDEV-28969, however
> I was told to use this mailing list.
>
>
>
> We have 4 MariaDB servers in a Galera Cluster and it happens that a server
> has to be restarted (be it for a crash which I have to open a bug for) or
> maintenance.
>
>
>
> When that happens, the restarted server is causing huge slow down on the
> whole cluster, and it lasts for 10 to 30 minutes at the very least!
>
>
>
> And by huge, I mean huge, we end up with 500 to 800 pending queries on all
> servers as you can see on attached screenshots
>
> I’ve attached the configuration of any server for reference in case this
> is the source of the issue.
>
>
>
> Any way to solve this would be greatly appreciated.
>
>
>
> You seem to be focusing on effect. What is the cause? SST?
>
>
>
>
> Regards,
>
> 3C.
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB server horribly slow on start

[Gordan Bobic]

IMO your biggest cause of problems is chasing the bleeding edge.
You are on 10.6.x. I generally don't even deploy 10.5.x in production yet
except in very exceptional circumstances (simple async replication, no
expectation of extreme performance).
Migrate to 10.4.x and stick with it until it goes EOL. Then maybe think
about upgrading to 10.5.x in the 6 months following 10.4.x EOL.

On Wed, Jul 27, 2022 at 1:50 PM Cédric Counotte 
wrote:

> I must add that the slowness occurs when the SST or IST are completed, and
> the server becomes available. Donor is also entirely blocked (it seems)
> when the queue is being processed.
>
>
>
> While the SST is performed, there is no issue.
>
>
>
> *De :* William Edwards 
> *Envoyé :* mercredi 27 juillet 2022 11:58
> *À :* Cédric Counotte 
> *Cc :* maria-discuss@lists.launchpad.net
> *Objet :* Re: [Maria-discuss] MariaDB server horribly slow on start
>
>
>
>
>
> Op 27 jul. 2022 om 11:46 heeft Cédric Counotte 
> het volgende geschreven:
>
> 
>
> Hello all. I hope I’m at the right place to ask this question.
>
> I opened a bug here: https://jira.mariadb.org/browse/MDEV-28969, however
> I was told to use this mailing list.
>
>
>
> We have 4 MariaDB servers in a Galera Cluster and it happens that a server
> has to be restarted (be it for a crash which I have to open a bug for) or
> maintenance.
>
>
>
> When that happens, the restarted server is causing huge slow down on the
> whole cluster, and it lasts for 10 to 30 minutes at the very least!
>
>
>
> And by huge, I mean huge, we end up with 500 to 800 pending queries on all
> servers as you can see on attached screenshots
>
> I’ve attached the configuration of any server for reference in case this
> is the source of the issue.
>
>
>
> Any way to solve this would be greatly appreciated.
>
>
>
> You seem to be focusing on effect. What is the cause? SST?
>
>
>
>
> Regards,
>
> 3C.
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Looking for advice where to start looking at a slow query case

[Gordan Bobic]

On Wed, 27 Jul 2022, 00:11 ,  wrote:

> Hey Gordan,
>
> Got you..
>
> I actually have a script that can translate the relevant tables for the
> benchmark test In mind but have yet to sit on it.
> I really do not have any intention of replacing the DB but merely try to
> understand if this similar issue in the query optimizer
> present in most of the popular DB systems.
> I do assume that Oracle DB do not have an issue since I have seen it does
> a much more complex queries on a very large data sets
> pretty easily, however there is a difference between what I believe to
> what I know when I am trying to state that  there is a bug in
> any DB what so ever and whatever the depth of this bug.
>


On an extensive enough test - you will find optimizer anomalies like this
in all RDBMS-es. They won't be in the same place (same combination of
queries and data), but there is a reason why most of them have index
hinting features (and in those that don't, it is a common feature request).
No optimizer is ever perfect, it's all based in heuristics to make educated
guesses.


>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Looking for advice where to start looking at a slow query case

[Gordan Bobic]

On Tue, Jul 26, 2022 at 11:04 PM  wrote:
>
> Hey Gordon,
>
> Before I am filing a jira bug report I would like to verify how the same 
> queries are running on other DB ie:
> * MSSQL
> * PostgreSQL
> * Oracle DB

You are probably going to have to ask that question on forums where
the primary interest are those respective databases.

> Are there any recommended tools to migrate the data from MariaDB to 
> PostgreSQL or MSSQL?

Migrating to completely different databases is not something you can
do without a non-trivial amount of effort, unless your application and
requirements are pretty trivial.
You are going to have to at the very least make changes to your
application to address various incompatibilities in the SQL dialects
and features.

> Specifically MSSQL has a migration tool but I have yet to use it so I am 
> looking for whatever recommendations.
> Any MariaDB to PostgreSQL transferring tool known to anyone?

In theory, mysqldump has an option for formatting the output so that
it is more easy to ingest into other databases.
In reality, unless your data and structures are very trivial, you will
still have to thump the output to make the ingestion process work.

> I can try to re-create the scenario just with the relevant tables and it 
> would be pretty simple to do eventually
> but time consuming if there is any automated tools for that.
>
> Any Recommendation is more then welcome!

The recommendation I can give you is that if you are looking at
migrating to a completely different database just to avoid using an
index hint, you probably haven't thought through the complexities of
migrating between databases.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Looking for advice where to start looking at a slow query case

[Gordan Bobic]

MariaDB and MySQL share the same roots, so it is unsurprising their
optimizers are similar.
IMO MySQL optimizer started to get worse since 5.7, and MariaDB's since 10.5.
All databases get it wrong some time, I've yet to work on a database
system that didn't at some time or another come up with a bad
execution plan for a query.

On Wed, Jul 13, 2022 at 12:38 PM  wrote:
>
> Thanks!!
>
> Just to point out that this exact same issue exist on MySQL.
> I haven't tried yet with MSSQL and Oracle DB yet but from my experience MSSQL 
> will probably
> figure it our right enough.
>
> 
> Eliezer Croitoru
> NgTech, Tech Support
> Mobile: +972-5-28704261
> Email: ngtech1...@gmail.com
> Web: https://ngtech.co.il/
> My-Tube: https://tube.ngtech.co.il/
>
> -Original Message-
> From: Gordan Bobic 
> Sent: Wednesday, 13 July 2022 10:42
> To: NgTech LTD 
> Cc: Mailing-List mariadb 
> Subject: Re: [Maria-discuss] Looking for advice where to start looking at a 
> slow query case
>
> Just had a look with the data set you provided, this looks like a
> classical case of query optimizer getting it wrong.
>
> In your first query, do this modification:
> s/SELECT/SELECT STRAIGHT_JOIN/
> and the execution time goes from seconds to milliseconds.
> The optimizer wasn't smart enough to figure out that the optimal plan
> is to start with the videos table and take advantage of early stopping
> with limit.
>
> It seems reasonably reproducible so you might want to file an
> optimizer bug on MariaDB jira.
>
> On Wed, Jul 13, 2022 at 9:59 AM NgTech LTD  wrote:
> >
> > Sorry, I didn't got why what works faster is faster.
> > Also when I am removing the Order by from the original query it runs faster.
> > There is index to the relevant fields.
> > The big table is the videos so I assumed that the db engine would be smart 
> > enough to first run the search in an optimized fashion and then compare to 
> > the other tables by the id.
> >
> > also why the difference between the same exact query when the u.* is used 
> > it's slow but when I am using the AS to define each and every field 
> > renaming it would run faster.
> > It doesn't make any sense to me as a programmer.
> > If I would have written the code to do this query it would be in such a way 
> > that it will run in couple ms...
> >
> > I have started re-learning my DBA materials with hope to JOIN my sysadmin, 
> > programming and dba skills to make sense in the future what doesn't make 
> > sense now.
> >
> > Thanks!
> >
> > בתאריך יום ד׳, 13 ביולי 2022, 9:45, מאת Gordan Bobic 
> > ‏:
> >>
> >> It isn't the same query.
> >> Sub-selects tend to execute by fully constructing the data set,
> >> putting it into a temporary table, and then selecting from that for
> >> the wrapping query. That means you get no benefit from early stopping,
> >> and often the optimizer doesn't figure out the appropriate indexing so
> >> your temporary table ends up with a full table scan.
> >> If you care about performance, avoid sub-selects by re-writing the
> >> query without it. If sub-selects are unavoidable, use an appropriately
> >> indexed materialized view (not a regular view, a regular view is just
> >> a sub-select in disguise in the vast majority of cases) that you can
> >> either refresh periodically via a scheduled event, or if necessary in
> >> real-time using triggers on the underlying tables.
> >>
> >>
> >> On Wed, Jul 13, 2022 at 9:25 AM  wrote:
> >> >
> >> > The full DB can be downloaded from:
> >> > https://www.ngtech.co.il/static/AVideo.sql.gz
> >> >
> >> > Thanks for any advice.
> >> > Eliezer
> >> >
> >> > 
> >> > Eliezer Croitoru
> >> > NgTech, Tech Support
> >> > Mobile: +972-5-28704261
> >> > Email: ngtech1...@gmail.com
> >> > Web: https://ngtech.co.il/
> >> > My-Tube: https://tube.ngtech.co.il/
> >> >
> >> > -Original Message-
> >> > From: ngtech1...@gmail.com 
> >> > Sent: Wednesday, 13 July 2022 8:46
> >> > To: 'maria-discuss@lists.launchpad.net' 
> >> > 
> >> > Subject: Looking for advice where to start looking at a slow query case
> >> >
> >> > Hey,
> >> >
> >> > I have tried to run a local service which claims to be a YouTube alike 
> >> > which is named AVideo(used to be PHPTube).
> >> > I loaded the service with 220k+ videos from YouTube and hav

Re: [Maria-discuss] Looking for advice where to start looking at a slow query case

[Gordan Bobic]

Just had a look with the data set you provided, this looks like a
classical case of query optimizer getting it wrong.

In your first query, do this modification:
s/SELECT/SELECT STRAIGHT_JOIN/
and the execution time goes from seconds to milliseconds.
The optimizer wasn't smart enough to figure out that the optimal plan
is to start with the videos table and take advantage of early stopping
with limit.

It seems reasonably reproducible so you might want to file an
optimizer bug on MariaDB jira.

On Wed, Jul 13, 2022 at 9:59 AM NgTech LTD  wrote:
>
> Sorry, I didn't got why what works faster is faster.
> Also when I am removing the Order by from the original query it runs faster.
> There is index to the relevant fields.
> The big table is the videos so I assumed that the db engine would be smart 
> enough to first run the search in an optimized fashion and then compare to 
> the other tables by the id.
>
> also why the difference between the same exact query when the u.* is used 
> it's slow but when I am using the AS to define each and every field renaming 
> it would run faster.
> It doesn't make any sense to me as a programmer.
> If I would have written the code to do this query it would be in such a way 
> that it will run in couple ms...
>
> I have started re-learning my DBA materials with hope to JOIN my sysadmin, 
> programming and dba skills to make sense in the future what doesn't make 
> sense now.
>
> Thanks!
>
> בתאריך יום ד׳, 13 ביולי 2022, 9:45, מאת Gordan Bobic 
> ‏:
>>
>> It isn't the same query.
>> Sub-selects tend to execute by fully constructing the data set,
>> putting it into a temporary table, and then selecting from that for
>> the wrapping query. That means you get no benefit from early stopping,
>> and often the optimizer doesn't figure out the appropriate indexing so
>> your temporary table ends up with a full table scan.
>> If you care about performance, avoid sub-selects by re-writing the
>> query without it. If sub-selects are unavoidable, use an appropriately
>> indexed materialized view (not a regular view, a regular view is just
>> a sub-select in disguise in the vast majority of cases) that you can
>> either refresh periodically via a scheduled event, or if necessary in
>> real-time using triggers on the underlying tables.
>>
>>
>> On Wed, Jul 13, 2022 at 9:25 AM  wrote:
>> >
>> > The full DB can be downloaded from:
>> > https://www.ngtech.co.il/static/AVideo.sql.gz
>> >
>> > Thanks for any advice.
>> > Eliezer
>> >
>> > 
>> > Eliezer Croitoru
>> > NgTech, Tech Support
>> > Mobile: +972-5-28704261
>> > Email: ngtech1...@gmail.com
>> > Web: https://ngtech.co.il/
>> > My-Tube: https://tube.ngtech.co.il/
>> >
>> > -Original Message-
>> > From: ngtech1...@gmail.com 
>> > Sent: Wednesday, 13 July 2022 8:46
>> > To: 'maria-discuss@lists.launchpad.net' 
>> > Subject: Looking for advice where to start looking at a slow query case
>> >
>> > Hey,
>> >
>> > I have tried to run a local service which claims to be a YouTube alike 
>> > which is named AVideo(used to be PHPTube).
>> > I loaded the service with 220k+ videos from YouTube and have used MariaDB 
>> > as the DB backend.
>> > The service moved slowly as the size of the videos DB growed from 100k to 
>> > 150k.
>> > I got a recommendation from the developer to use MySQL which didn’t made 
>> > any sense to me.
>> > I tracked the issue to the SQL queries that the service runs.
>> > Currently I am upgrading my local servers so the DB cannot be downloaded 
>> > for testing however I have seen a very interesting thing.
>> > I will first put the query here:
>> > ```sql
>> > SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as 
>> > clean_category,c.description as category_description,
>> > v.created as videoCreation, v.modified as videoModified
>> > FROM videos as v
>> > LEFT JOIN categories c ON categories_id = c.id
>> > LEFT JOIN users u ON v.users_id = u.id
>> > WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f')
>> > ORDER BY likes DESC  LIMIT 36, 12;
>> > ```
>> >
>> > The ANALYZE FORMAT=JSON for that query is:
>> > ```json
>> > {
>> >   "query_block": {
>> > "select_id": 1,
>> > "r_loops": 1,
>> > "r_total_time_ms": 9643.807074,
>> > "filesort": {
>> >   &qu

Re: [Maria-discuss] Looking for advice where to start looking at a slow query case

[Gordan Bobic]

It isn't the same query.
Sub-selects tend to execute by fully constructing the data set,
putting it into a temporary table, and then selecting from that for
the wrapping query. That means you get no benefit from early stopping,
and often the optimizer doesn't figure out the appropriate indexing so
your temporary table ends up with a full table scan.
If you care about performance, avoid sub-selects by re-writing the
query without it. If sub-selects are unavoidable, use an appropriately
indexed materialized view (not a regular view, a regular view is just
a sub-select in disguise in the vast majority of cases) that you can
either refresh periodically via a scheduled event, or if necessary in
real-time using triggers on the underlying tables.


On Wed, Jul 13, 2022 at 9:25 AM  wrote:
>
> The full DB can be downloaded from:
> https://www.ngtech.co.il/static/AVideo.sql.gz
>
> Thanks for any advice.
> Eliezer
>
> 
> Eliezer Croitoru
> NgTech, Tech Support
> Mobile: +972-5-28704261
> Email: ngtech1...@gmail.com
> Web: https://ngtech.co.il/
> My-Tube: https://tube.ngtech.co.il/
>
> -Original Message-
> From: ngtech1...@gmail.com 
> Sent: Wednesday, 13 July 2022 8:46
> To: 'maria-discuss@lists.launchpad.net' 
> Subject: Looking for advice where to start looking at a slow query case
>
> Hey,
>
> I have tried to run a local service which claims to be a YouTube alike which 
> is named AVideo(used to be PHPTube).
> I loaded the service with 220k+ videos from YouTube and have used MariaDB as 
> the DB backend.
> The service moved slowly as the size of the videos DB growed from 100k to 
> 150k.
> I got a recommendation from the developer to use MySQL which didn’t made any 
> sense to me.
> I tracked the issue to the SQL queries that the service runs.
> Currently I am upgrading my local servers so the DB cannot be downloaded for 
> testing however I have seen a very interesting thing.
> I will first put the query here:
> ```sql
> SELECT u.*, v.*, c.iconClass, c.name as category, c.clean_name as 
> clean_category,c.description as category_description,
> v.created as videoCreation, v.modified as videoModified
> FROM videos as v
> LEFT JOIN categories c ON categories_id = c.id
> LEFT JOIN users u ON v.users_id = u.id
> WHERE 2=2 AND u.status = 'a' AND v.status IN ('a','k','f')
> ORDER BY likes DESC  LIMIT 36, 12;
> ```
>
> The ANALYZE FORMAT=JSON for that query is:
> ```json
> {
>   "query_block": {
> "select_id": 1,
> "r_loops": 1,
> "r_total_time_ms": 9643.807074,
> "filesort": {
>   "sort_key": "v.likes desc",
>   "r_loops": 1,
>   "r_total_time_ms": 756.223544,
>   "r_limit": 48,
>   "r_used_priority_queue": true,
>   "r_output_rows": 49,
>   "r_sort_mode": "sort_key,rowid",
>   "temporary_table": {
> "table": {
>   "table_name": "u",
>   "access_type": "ref",
>   "possible_keys": ["PRIMARY", "users_status_IDX"],
>   "key": "users_status_IDX",
>   "key_length": "1",
>   "used_key_parts": ["status"],
>   "ref": ["const"],
>   "r_loops": 1,
>   "rows": 1,
>   "r_rows": 1,
>   "r_table_time_ms": 0.034852389,
>   "r_other_time_ms": 0.020715696,
>   "filtered": 100,
>   "r_filtered": 100,
>   "index_condition": "u.`status` = 'a'"
> },
> "table": {
>   "table_name": "v",
>   "access_type": "ref",
>   "possible_keys": [
> "fk_videos_users_idx",
> "video_status_idx",
> "videos_status_index"
>   ],
>   "key": "fk_videos_users_idx",
>   "key_length": "4",
>   "used_key_parts": ["users_id"],
>   "ref": ["AVideo.u.id"],
>   "r_loops": 1,
>   "rows": 391299,
>   "r_rows": 990067,
>   "r_table_time_ms": 2691.791742,
>   "r_other_time_ms": 6129.887127,
>   "filtered": 50.00025558,
>   "r_filtered": 100,
>   "attached_condition": "v.`status` in ('a','k','f')"
> },
> "table": {
>   "table_name": "c",
>   "access_type": "eq_ref",
>   "possible_keys": ["PRIMARY"],
>   "key": "PRIMARY",
>   "key_length": "4",
>   "used_key_parts": ["id"],
>   "ref": ["AVideo.v.categories_id"],
>   "r_loops": 990067,
>   "rows": 1,
>   "r_rows": 1,
>   "r_table_time_ms": 0.00546745,
>   "r_other_time_ms": 0.569425897,
>   "filtered": 100,
>   "r_filtered": 100
> }
>   }
> }
>   }
> }
> ```
> While for the next:
> ```sql
> SELECT * FROM (SELECT  u.id as uid , u.user as uuser , u.name as uname , 
> u.email as uemail ,
> u.password as upassword , u.created as ucreated , u.modified as umodified , 
> u.isAdmin as uisAdmin , u.status as ustatus ,
> u.photoURL as uphotoURL , u.lastLogin as ulastLogin , u.recoverPass as 
> urecoverPass , u.backgroundURL as 

Re: [Maria-discuss] Incorrect Documentation: Replicating JSON from MySQL to MariaDB

[Gordan Bobic]

"Starting from MariaDB 10.5.7 ..."

Unfortunately, 10.5+ is not an option at the moment for the
environment where I need this to work.

On Sun, May 29, 2022 at 1:13 PM Sergei Golubchik  wrote:
>
> Hi, Gordan,
>
> Would it work if you install "mysql_json" plugin in the MariaDB and then
> replicate MySQL->MariaDB?
>
> I didn't try myself, but I've checked the sources and it appears that it
> was supposed to work.
>
> If it won't - please, report it as a bug.
>
> On May 29, Gordan Bobic wrote:
> > On this page:
> > https://mariadb.com/kb/en/json-data-type/
> >
> > it states:
> > If you must use row-based replication and cannot change the MySQL
> > master from JSON to TEXT, you can try to introduce an intermediate
> > MySQL slave and change the column type from JSON to TEXT on it. Then
> > you replicate from this intermediate slave to MariaDB.
> >
> > I can confirm that this does not work with MySQL 5.7. If you have a
> > 5.7 master and you replicate to 5.7 slave, and you convert the slave's
> > json columns to longtext, replication fails with this error:
> >
> > "Column X of table 'dbname.tablename' cannot be converted from type
> > 'json' to type 'longtext'"
> >
> > Using longblob doesn't work either.
> >
> > So it seems there is currently no viable solution for replicating
> > anything that involves JSON columns from MySQL to MariaDB.
> >
> Regards,
> Sergei
> VP of MariaDB Server Engineering
> and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

[Maria-discuss] Incorrect Documentation: Replicating JSON from MySQL to MariaDB

[Gordan Bobic]

On this page:
https://mariadb.com/kb/en/json-data-type/

it states:
If you must use row-based replication and cannot change the MySQL
master from JSON to TEXT, you can try to introduce an intermediate
MySQL slave and change the column type from JSON to TEXT on it. Then
you replicate from this intermediate slave to MariaDB.

I can confirm that this does not work with MySQL 5.7. If you have a
5.7 master and you replicate to 5.7 slave, and you convert the slave's
json columns to longtext, replication fails with this error:

"Column X of table 'dbname.tablename' cannot be converted from type
'json' to type 'longtext'"

Using longblob doesn't work either.

So it seems there is currently no viable solution for replicating
anything that involves JSON columns from MySQL to MariaDB.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Custom Galera SST Method Documentation

[Gordan Bobic]

Ah, that would be where I was going wrong - I was looking for it in
MariaDB documentation rather than upstream Galera documentation.
Thanks.

On Fri, May 27, 2022 at 11:09 AM Daniel Black  wrote:
>
> Sure,
>
> There's https://galeracluster.com/library/documentation/scriptable-sst.html
>
> And the configuration is derived from wsrep_sst_method
> (https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-sst-method)
>
>
> On Fri, May 27, 2022 at 5:41 PM Gordan Bobic  wrote:
> >
> > Hi,
> >
> > I vaguely remember reading something years ago about implementing a
> > custom SST method, but I cannot seem to find any documentation for it
> > now.
> >
> > Is there any documentation on the interfaces that scripts need to
> > implement and how to hook up said scripts into the config files?
> >
> > ___
> > Mailing list: https://launchpad.net/~maria-discuss
> > Post to : maria-discuss@lists.launchpad.net
> > Unsubscribe : https://launchpad.net/~maria-discuss
> > More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

[Maria-discuss] Custom Galera SST Method Documentation

[Gordan Bobic]

Hi,

I vaguely remember reading something years ago about implementing a
custom SST method, but I cannot seem to find any documentation for it
now.

Is there any documentation on the interfaces that scripts need to
implement and how to hook up said scripts into the config files?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] In memory DB, how to in MariaDB?

[Gordan Bobic]

It's called "marketing". They will pick a specific case and then
pretend that somehow it can solve generalised O(n) problems in
O(log(n)) time.

On Sun, May 22, 2022 at 3:14 PM NgTech LTD  wrote:
>
> Thanks!
> I assumed so and was wondering how exactly Oracle claimed that the in-memory 
> DB makes the speed fast compared between in memory to on disk and without 
> indexes.
> What the demo shows is that a DB in memory can be fast and so fast that you 
> wouldn't need indexes if and only if I understood right.
>
> Can you abd others please try to verify my assumption and clear out my doubts?
>
> Thanks,
> Eliezer
>
> בתאריך יום א׳, 22 במאי 2022, 15:07, מאת Gordan Bobic 
> ‏:
>>
>> If your buffer pool is big enough to store the entire data set, then
>> there is no need to use any kind of in-memory database engine, the
>> buffer pool will end up caching everything (once it is warmed up).
>> For faster writes if you don't particularly care about ACID
>> compliance, you can disable InnoDB transaction log flushing by setting
>> innodb_flush_log_at_trx_commit=0. You will lose transaction if there
>> is a dirty shutdown, but it will largely bypass any disk write I/O
>> bottleneck.
>>
>> Over and above that, the usual performance tuning applies - make sure
>> your queries are well written and well indexed. In most cases that
>> makes far more difference than keeping all of your data in memory.
>>
>> On Sun, May 22, 2022 at 3:01 PM Eliezer Croitoru  
>> wrote:
>> >
>> > I have seen the next blog post which had a video at 2014:
>> >
>> > https://blogs.oracle.com/pcoe/post/the-future-of-the-database-begins
>> >
>> >
>> >
>> > The demo video was removed from YouTube for some reason but I had a copy 
>> > of it at:
>> >
>> > https://cloud1.ngtech.co.il/static/oracle/Oracle%20Database%20In-Memory%20Powering%20the%20Real-Time%20Enterprise%20.mp4
>> >
>> >
>> >
>> > I am not a MariaDB or MySQL or DB in general expert and was wondering if 
>> > there is something these days in MariaDB that can be used
>> > in comparison to what Oracle DB demo shows?
>> >
>> >
>> >
>> > I have used LevelDB as a backend in a customized DB and it worked very 
>> > very fast and it is great.
>> > With batches it works even faster but in general it works faster then 
>> > standard MariaDB setups I have implemented.
>> >
>> > So what are the options?
>> >
>> > I have millions of values and I would like to be able to insert and select 
>> > as fast as possible on a single DB Server.
>> >
>> > Currently I am using a Caching tier with a specific TTL to lower the 
>> > stress off the DB, but, is I possible to make the DB
>> > Faster like maybe with some writeback (leaving aside fault tolerance for 
>> > speed).
>> >
>> >
>> >
>> > Thanks,
>> >
>> > Eliezer
>> >
>> >
>> >
>> > 
>> >
>> > Eliezer Croitoru
>> >
>> > NgTech, Tech Support
>> >
>> > Mobile: +972-5-28704261
>> >
>> > Email: ngtech1...@gmail.com
>> >
>> >
>> >
>> > ___
>> > Mailing list: https://launchpad.net/~maria-discuss
>> > Post to : maria-discuss@lists.launchpad.net
>> > Unsubscribe : https://launchpad.net/~maria-discuss
>> > More help   : https://help.launchpad.net/ListHelp
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] In memory DB, how to in MariaDB?

[Gordan Bobic]

If your buffer pool is big enough to store the entire data set, then
there is no need to use any kind of in-memory database engine, the
buffer pool will end up caching everything (once it is warmed up).
For faster writes if you don't particularly care about ACID
compliance, you can disable InnoDB transaction log flushing by setting
innodb_flush_log_at_trx_commit=0. You will lose transaction if there
is a dirty shutdown, but it will largely bypass any disk write I/O
bottleneck.

Over and above that, the usual performance tuning applies - make sure
your queries are well written and well indexed. In most cases that
makes far more difference than keeping all of your data in memory.

On Sun, May 22, 2022 at 3:01 PM Eliezer Croitoru  wrote:
>
> I have seen the next blog post which had a video at 2014:
>
> https://blogs.oracle.com/pcoe/post/the-future-of-the-database-begins
>
>
>
> The demo video was removed from YouTube for some reason but I had a copy of 
> it at:
>
> https://cloud1.ngtech.co.il/static/oracle/Oracle%20Database%20In-Memory%20Powering%20the%20Real-Time%20Enterprise%20.mp4
>
>
>
> I am not a MariaDB or MySQL or DB in general expert and was wondering if 
> there is something these days in MariaDB that can be used
> in comparison to what Oracle DB demo shows?
>
>
>
> I have used LevelDB as a backend in a customized DB and it worked very very 
> fast and it is great.
> With batches it works even faster but in general it works faster then 
> standard MariaDB setups I have implemented.
>
> So what are the options?
>
> I have millions of values and I would like to be able to insert and select as 
> fast as possible on a single DB Server.
>
> Currently I am using a Caching tier with a specific TTL to lower the stress 
> off the DB, but, is I possible to make the DB
> Faster like maybe with some writeback (leaving aside fault tolerance for 
> speed).
>
>
>
> Thanks,
>
> Eliezer
>
>
>
> 
>
> Eliezer Croitoru
>
> NgTech, Tech Support
>
> Mobile: +972-5-28704261
>
> Email: ngtech1...@gmail.com
>
>
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] mariadb 10.4.13-lp152.1.1 to 10.5.12-3.9.1 upgrade failure SOLVED!

[Gordan Bobic]

Failure of in-place upgrade between 10.5.11 and 10.5.12 sounds like a
_major_ bug.


On Thu, 25 Nov 2021, 21:59 Marc Chamberlin,  wrote:

> Hi and thanks Karl and everyone who tried to help me, and sorry for the
> long delay in responding. Trying to use the packages from the mariadb.org
> repo did not work any better than using the repos from OpenSuSE's
> repositories. The automatic upgrade process did not work when trying to
> upgrade from any version past 10.5.11-3.3.1-x86-64.
>
> However, I have managed to find a solution by  using another approach. I
> reinstalled version 10.5.11-3.3.1-x86-64 and used mysqldump to dump all the
> databases to an sql file. Then I set the database storage directory aside
> and created a new directory to hold the database files. I then installed
> the latest versions of mariadb server, client, tools etc from the OpenSuSE
> repository (Ver 10.5.12-3.9.1-x86_64), started it with no databases, and
> imported all my databases from the aforementioned sql file.  Seems to have
> worked!
>
> Thanks again, just reporting how I managed to workaround this issue.
> Marc
> On 11/8/21 4:27 AM, Karl Levik wrote:
>
> Hiya Marc,
>
> That sounds like progress.
>
> I wonder whether the upgrade would work with MariaDB Server packages from
> the mariadb.org repo instead of packages from OpenSuSE?
>
> If so, then that would indicate the problem might be related to the
> packaging done by OpenSuSE.
>
> Cheers,
> Karl
>
> On Mon, 8 Nov 2021 at 06:08, Marc Chamberlin 
> wrote:
>
>> Hi Karl, and thanks for your thoughts, and I think you got me a bit
>> further but in the end still no joy getting the upgrade to the latest
>> version of Mariadb, that is supported in OpenSuSE 15.3, (10.5.12-3.9.1), to
>> work. I took your idea of doing incremental upgrades to see how far I could
>> get and this is what I found using various versions found in the OpenSuSE
>> 15.3 repositories -
>>
>> Version 10.4.13-lp152.1.1-x86_64 (supplied as part of OpenSuSE 15.2)
>> upgraded to version 10.5.8-1.5-x86-64, works!
>> Version 10.5.8-1.5-x86-64 (supplied as part of OpenSuSE 15.3) upgraded to
>> version 10.5.11-3.3.1-x86-64, works!
>> Version 10.5.11-3.3.1-x86-64 (supplied as part of OpenSuSE 15.3) upgraded
>> to version 10.5.12-3.6.1-x86-64, worked first time I tried, but all
>> subsequent attempts to upgrade to this version (from version
>> 10.5.8-1.5-x86-64) have failed.
>> Version 10.5.12-3.6.1-x86-64 (supplied as part of OpenSuSE 15.3) upgraded
>> to version 10.5.12-3.9.1-x86-64, always fails.
>>
>> So at the moment I can use version 10.5.11-3.3.1-x86-64.  By failed I
>> mean I cannot get that version of the MySQL/MariaDB server to start.
>> Instead I get the error about corrupt tables that I reported earlier. It
>> appears that something broke in version 10.5.12-3.6.1-x86-64 even though I
>> did get it to work one time.
>>
>> Your other suggestion, to "Retry with innodb_force_recovery=5" hasn't
>> seemed to make a difference in my assorted attempts to upgrade to the
>> latest versions that OpenSuSE 15.3 has in it's repositories.
>>
>>HTHs,  Marc...
>>
>>
>> On 11/5/21 2:30 AM, Karl Levik wrote:
>>
>> (Re-sent because I forgot to include the list, sorry.)
>>
>> Hi Marc,
>>
>> That sounds frustrating. 10.4.13 is not the most recent in the 10.4
>> series, so perhaps it would help to upgrade to 10.4.21 first, and then
>> upgrade from there to 10.5.12?
>>
>> Your error log also suggests a solution: "Retry with
>> innodb_force_recovery=5" - it might be worth trying that as well.
>>
>> Cheers,
>> Karl
>>
>> On Thu, 4 Nov 2021 at 16:45, Marc Chamberlin 
>> wrote:
>>
>>>
>>> Hello - I have ran into a problem that maybe someone else has hit and
>>> knows a solution for it.  I recently upgraded my OpenSuSE 15.2 x64 version
>>> to OpenSuSE 15.3 x64 and installed all additional updates and patches that
>>> were suggested for 15.3.  Most of the upgrade succeeded, all except
>>> Mariadb. Google has not been helpful either. My understanding of Mariadb is
>>> that after a new install of a new version it is suppose to automagically
>>> update and fix the databases from the older version, when the server is
>>> restarted, but I think this is failing. I found the  upgrade log file for
>>> Mariadb and it appears to be reporting some kind of corruption has
>>> occurred. This is preventing the mariadb (mysql) server from even starting,
>>> so it is not possible to use queries to find and repair the database. -
>>>
>>> bigbang:/var/tmp/mysql-protected.lBP2YQ # more log_upgrade_run.err
>>> 2021-10-31 12:11:36 0 [Note] InnoDB: !!! innodb_force_recovery is set to
>>> 1 !!!
>>> 2021-10-31 12:11:36 0 [Note] InnoDB: Uses event mutexes
>>> 2021-10-31 12:11:36 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
>>> 2021-10-31 12:11:36 0 [Note] InnoDB: Number of pools: 1
>>> 2021-10-31 12:11:36 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
>>> 2021-10-31 12:11:36 0 [Note] mysqld: O_TMPFILE is not supported on /tmp
>>> (disabling 

Re: [Maria-discuss] mariadb 10.4.13-lp152.1.1 to 10.5.12-3.9.1 upgrade failure

[Gordan Bobic]

Before you do the upgrade, can you try shutting it down after setting
innodb_fast_shutdown=0?
That will purge the redo logs completely before shutting down. Then
try the new version and see if it starts.
innodb_force_recovery is a bit of a last-resort option to try before
you have to resort to more advanced methods of data recovery.

On Fri, Nov 5, 2021 at 11:30 AM Karl Levik  wrote:
>
> (Re-sent because I forgot to include the list, sorry.)
>
> Hi Marc,
>
> That sounds frustrating. 10.4.13 is not the most recent in the 10.4 series, 
> so perhaps it would help to upgrade to 10.4.21 first, and then upgrade from 
> there to 10.5.12?
>
> Your error log also suggests a solution: "Retry with innodb_force_recovery=5" 
> - it might be worth trying that as well.
>
> Cheers,
> Karl
>
> On Thu, 4 Nov 2021 at 16:45, Marc Chamberlin  wrote:
>>
>>
>> Hello - I have ran into a problem that maybe someone else has hit and knows 
>> a solution for it.  I recently upgraded my OpenSuSE 15.2 x64 version to 
>> OpenSuSE 15.3 x64 and installed all additional updates and patches that were 
>> suggested for 15.3.  Most of the upgrade succeeded, all except Mariadb. 
>> Google has not been helpful either. My understanding of Mariadb is that 
>> after a new install of a new version it is suppose to automagically update 
>> and fix the databases from the older version, when the server is restarted, 
>> but I think this is failing. I found the  upgrade log file for Mariadb and 
>> it appears to be reporting some kind of corruption has occurred. This is 
>> preventing the mariadb (mysql) server from even starting, so it is not 
>> possible to use queries to find and repair the database. -
>>
>> bigbang:/var/tmp/mysql-protected.lBP2YQ # more log_upgrade_run.err
>> 2021-10-31 12:11:36 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 
>> !!!
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Uses event mutexes
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Number of pools: 1
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
>> 2021-10-31 12:11:36 0 [Note] mysqld: O_TMPFILE is not supported on /tmp 
>> (disabling future attempts)
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Using Linux native AIO
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Initializing buffer pool, total size = 
>> 134217728, chunk size = 134217728
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Completed initialization of buffer pool
>> 2021-10-31 12:11:36 0 [ERROR] InnoDB: corrupted TRX_NO 5f737461748dea16
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Retry with innodb_force_recovery=5
>> 2021-10-31 12:11:36 0 [ERROR] InnoDB: Plugin initialization aborted with 
>> error Data structure corruption
>> 2021-10-31 12:11:36 0 [Note] InnoDB: Starting shutdown...
>> 2021-10-31 12:11:36 0 [ERROR] Plugin 'InnoDB' init function returned error.
>> 2021-10-31 12:11:36 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE 
>> ENGINE failed.
>> 2021-10-31 12:11:36 0 [Note] Plugin 'FEEDBACK' is disabled.
>> 2021-10-31 12:11:36 0 [ERROR] Unknown/unsupported storage engine: InnoDB
>> 2021-10-31 12:11:36 0 [ERROR] Aborting
>>
>> On a hunch, I decided to install the OpenSuSE OSS repository for 15.2 and 
>> this allowed me to drop back and install version 10.4.13-lp152.1.1 of the 
>> mariadb server, client, errormessages, and tools packages. (OpenSuSE 15.3 
>> wants to install version 10.5.12-3.9.1 of these mariadb packages.) And lo 
>> and behold the previous 10.4.13-lp152.1.1 package works fine!
>>
>> So what gives? Any idea why the upgraded version of mariadb packages are 
>> failing to upgrade and support my databases? As I said I have tried Googling 
>> for an answer but no joy finding one. A lot of the proposed solutions, 
>> Google found, assume the mariadb server is up and running (hump!) so the 
>> example solutions given, use mysql queries and commands to fix corrupt 
>> databases and tables. I should also point out that AFAIK most, if not all of 
>> my database structures are using InnoDB and not the older MYISAM structures 
>> (although the mysql database itself appears to have both). I did make a 
>> mariadb backup of all the databases but without the ability to start the 
>> mariadb server I don't know how to make use of this backup file to 
>> reconstruct all the databases and their tables etc.
>>
>> Thanks in advance for any and all advice on how to upgrade my mariadb 
>> server.Marc Chamberlin
>>
>> --
>> "The Truth is out there" - Spooky
>>
>> --
>> _   _   .   .   .   .   .   .   _   _   .   _   _   _   _   .   
>> .   .   .   _   .   .   .   .   _   _   .   _   
>> _   _   .   .   .   .   _   _   .   _   .   .   _   .   _   _
>>_   _   .   _   .   _   .   _   .   _   .
>>
>> Computers: the final frontier.
>> These are the voyages of the user Marc.
>> His mission: to explore strange new hardware.
>> To seek out new software 

Re: [Maria-discuss] Troubleshooting Statement That Crashes MariaDB (mysqld got signal 11)

[Gordan Bobic]

What hardware are you on, and does it have ECC memory?

On Thu, Nov 4, 2021 at 7:48 PM Michael Caplan  wrote:
>
> Hello,
>
> I have a nothing fancy SQL statement that crashes MariaDB 10.3.31.  The
> error log reports "mysqld got signal 11" with the below included mini
> core dump.
>
> The statement caused no issues with my prior install of 10.2.xx. With
> 10.3, the statement runs the majority of the time.  The vast majority of
> the time.
>
> I have never dealt with such an issue with MariaDB, and am unsure where
> to start with troubleshooting this.  Any tips?
>
> FYI, there is a corresponding bug recorded here:
> https://jira.mariadb.org/browse/MDEV-26835
>
> Thanks,
>
> Mike
>
>
>
>
> 211014 16:13:53 [ERROR] mysqld got signal 11 ;
> This could be because you hit a bug. It is also possible that this binary
> or one of the libraries it was linked against is corrupt, improperly built,
> or misconfigured. This error can also be caused by malfunctioning hardware.
>
> To report this bug, see https://mariadb.com/kb/en/reporting-bugs
>
> We will try our best to scrape up some info that will hopefully help
> diagnose the problem, but since we have already crashed,
> something is definitely wrong and this may fail.
>
> Server version: 10.3.31-MariaDB-1:10.3.31+maria~focal-log
> key_buffer_size=134217728
> read_buffer_size=131072
> max_used_connections=587
> max_threads=752
> thread_count=81
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
> 1784362 K  bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> Thread pointer: 0x7f13e83b3768
> Attempting backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong...
> stack_bottom = 0x7f1644e96dd8 thread_stack 0x49000
> /usr/sbin/mysqld(my_print_stacktrace+0x32)[0x55d5a2f6f3a2]
> /usr/sbin/mysqld(handle_fatal_signal+0x55d)[0x55d5a2a46e8d]
> /lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0)[0x7f27569fc3c0]
> /usr/sbin/mysqld(+0x978f4f)[0x55d5a2bdbf4f]
> /usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x50)[0x55d5a2a4c6d0]
> /usr/sbin/mysqld(_Z16rr_from_pointersP11READ_RECORD+0x3e)[0x55d5a2b7cb2e]
> /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x18e)[0x55d5a28869be]
> /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xbee)[0x55d5a28b0dce]
> /usr/sbin/mysqld(_ZN4JOIN4execEv+0x37)[0x55d5a28b1137]
> /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xf2)[0x55d5a28b1282]
> /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14b)[0x55d5a28b1bcb]
> /usr/sbin/mysqld(+0x5de9f1)[0x55d5a28419f1]
> /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5414)[0x55d5a284f6e4]
> /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x1f3)[0x55d5a2852253]
> /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x105d)[0x55d5a285462d]
> /usr/sbin/mysqld(_Z10do_commandP3THD+0x12d)[0x55d5a28565dd]
> /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x246)[0x55d5a293f186]
> /usr/sbin/mysqld(handle_one_connection+0x3f)[0x55d5a293f35f]
> /lib/x86_64-linux-gnu/libpthread.so.0(+0x9609)[0x7f27569f0609]
> /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f2756915293]
>
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort.
> Query (0x7f13e84a8b70): SELECT * FROM ((
>  #
>  # get postings from last statement and postings not on
> statements
>  #
>  SELECT a.*, COALESCE(ci.id, i.id) as invoice_id
>  FROM ddx_lab_801317.postings a
>  LEFT JOIN ddx_lab_801317.cases_invoices ci
>  ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
>  LEFT JOIN ddx_lab_801317.invoices i
>  ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
>  WHERE (
>  a.statement_date IS NULL
>  OR a.statement_date = '2021-09-27'
>  )
>   AND a.practice_id='19686'
>  ORDER BY post_date
>  ) UNION (
>  #
>  # get postings that have remaining balances
>  #
>  SELECT DISTINCT a.*, COALESCE(ci.id, i.id) as invoice_id
>  FROM ddx_lab_801317.postings a
>  LEFT JOIN ddx_lab_801317.cases_invoices ci
>  ON a.ref_type = 'CASE_INVOICE' AND a.ref_id = ci.id
>  LEFT JOIN ddx_lab_801317.invoices i
>  ON a.ref_type = 'INVOICE' AND a.ref_id = i.id
>  LEFT JOIN ddx_lab_801317.postings b
>  ON a.ref_type = b.ref_type
>  AND a.ref_id = b.ref_id
>  AND a.id != b.id
>
>  WHERE NOT (a.type = 'CREDIT' OR a.type = 'PAYMENT')
>  AND ((SELECT SUM(b.amount)) IS NULL OR (SELECT
> SUM(a.amount)) + (SELECT SUM(b.amount)) > 0)
>   AND 

Re: [Maria-discuss] Issues with Upgrading from MariaDB 10.2.22 to 10.5.12

[Gordan Bobic]

I don't think skipping releases in an upgrade is supported. So you need to
upgrade
10.2 to 10.3 to 10.4 to 10.5, with any additional caveats for specific
version upgrades (e.g. InnoDB log format change during 10.2).

On Wed, 25 Aug 2021, 15:00 Michael Caplan,  wrote:

> Hi there.
>
> I'm going through an upgrade process I have done before with earlier
> versions of mariaDB and Mysql, but running into an issue. My goal is to
> create a new slave including upgrade process from MariaDB 10.2.22
> (serverOld) to 10.5.12 on a new server (serverNew)
>
> (as inspired by the brighter mind at
>
> https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replication-with-rsync/
> )
>
>   1. rsync /var/lib/mysql from serverOld to serverNew
>   2. On serverOld:  FLUSH TABLES WITH READ LOCK;  SHOW MASTER STATUS;
>   3. rerun rsync from serverOld to serverNew
>   4. Release read lock from serverOld
>   5. Start mariadb on serverNew
>   6. run mysql_upgrade on serverNew
>   7. Celebrate and have a nap
>
>
> In actual practice step #5 failed numerous times before what I think is
> now an okay serverNew
>
>
> # Innodb settings between serverOld and serverNew
>
> #serverOld
> innodb-flush-method= O_DIRECT
> innodb-log-files-in-group  = 2
> innodb-log-file-size   = 48M
> innodb-flush-log-at-trx-commit = 2
> innodb-file-per-table  = 1
> innodb-buffer-pool-size= 128M
>
> #serverNew
> innodb-flush-method= O_DIRECT
> innodb-log-file-size   = 48M
> innodb-flush-log-at-trx-commit = 2
> innodb-file-per-table  = 1
> innodb-buffer-pool-size= 128M
>
>
> The only difference is innodb-log-files-in-group setting is removed in
> serverNew, as it has been depricated and removed in 10.5
>
>
>
> # Upgrade after crash?
>
> When attempting to start serverNew, it would fail with the following error:
>
> [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
> created with MariaDB 10.2.22.
> [ERROR] InnoDB: Plugin initialization aborted with error Generic error
>
> the log file on serverOld showed no evidence of crashing.  None the
> less, I restarted serverOld, and reran the process above. Still the same
> issue step #5.
>
>
> # Remove ib_logfile* files
>
> I then tried forcing the recreation of the redo logs by removing them
> and then starting up serverNew.  This resulted in the following scary
> errors:
>
> [ERROR] InnoDB: Page [page id: space=0, page number=1984] log sequence
> number 285722576186 is in the future! Current system log sequence number
> 259573448177.
> [ERROR] InnoDB: Your database may be corrupt or you may have copied the
> InnoDB tablespace but not the InnoDB log files. Please refer to
> https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information
> about forcing recovery.
>
>
> # innodb_fast_shutdown = 0
>
> The next attempt started with serverOld being restarted and
> innodb_fast_shutdown = 0 being set beforehand.  I reran the above steps
> and still got stuck on step #5 with the following error:
>
> [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
> created with MariaDB 10.2.22.
> [ERROR] InnoDB: Plugin initialization aborted with error Generic error
>
> This time, however, when I removed the ib_logfile* files, we seemed to
> have an okay startup:
>
> [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk
> size = 134217728
> [Note] InnoDB: Completed initialization of buffer pool
> [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
> [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
> [Note] InnoDB: New log file created, LSN=288830457728
> [Note] InnoDB: 128 rollback segments are active.
> [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
> [Note] InnoDB: Creating shared tablespace for temporary tables
> [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing
> the file full; Please wait ...
> [Note] InnoDB: File './ibtmp1' size is now 12 MB.
> [Note] InnoDB: 10.5.12 started; log sequence number 0; transaction id
> 3037161218
> [Note] Plugin 'FEEDBACK' is disabled.
> [Note] Recovering after a crash using /var/lib/mysql/mysql-bin
> [Note] Starting crash recovery...
> [Note] Crash recovery finished.
> [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
> [Note] Server socket created on IP: '::'.
> [ERROR] Incorrect definition of table mysql.event: expected column
> 'sql_mode' at position 14 to have type
> 

Re: [Maria-discuss] MariaDB master-slave chained replication and parallelism

[Gordan Bobic]

InnoDB is faster even on a read-only workload now and has been for over a
decade.
One of very, very few cases for use of MyISAM is if you use compressed
read-only MyISAM tables.

On Thu, Aug 5, 2021 at 11:39 AM Jan Křístek  wrote:

> It's used as an archive of events and data. The data are written just once
> and then read multiple times.
>
> I think that the response times are limited by the disk speed and that
> indexing for lookups works in a similar way on both MyISAM and InnoDB. We
> were considering upgrading it to Aria storage engine, as it offers better
> consistency (after a crash - I have read that somewhere), but it's not much
> time to do it now.
>
> Could be the upgrade might be easier when we will have a DB-proxy in place.
>
> On Thu, 5 Aug 2021 at 10:14, Gordan Bobic  wrote:
>
>> I think you are going to have to bite the bullet on conversion to InnoDB
>> sooner rather than later. The performance improvement you are going to see
>> will likely be orders of magnitude. The longer you leave it, the more
>> painful it is going to become.
>> You could do it on a slave and then promote it, or you could do it with
>> pt-online-schema-change.
>> While MyISAM still has some very narrow niche uses, it really is way past
>> time to retire it in any regular use.
>>
>>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] MariaDB master-slave chained replication and parallelism

[Gordan Bobic]

I think you are going to have to bite the bullet on conversion to InnoDB
sooner rather than later. The performance improvement you are going to see
will likely be orders of magnitude. The longer you leave it, the more
painful it is going to become.
You could do it on a slave and then promote it, or you could do it with
pt-online-schema-change.
While MyISAM still has some very narrow niche uses, it really is way past
time to retire it in any regular use.

On Thu, Aug 5, 2021 at 11:04 AM Jan Křístek  wrote:

>
> Hi Kristian,
>
> Thank you for your answer.
>
> On Thu, 5 Aug 2021 at 09:44, Kristian Nielsen 
> wrote:
>
>>
>> Interesting. Where do you see these counts? My guess is that these are
>> counting the "transactional" status flag on each GTID event in the binlog.
>>
>
> I see these counts almost at the end of 'mysql -e "show slave status\G"'
>
> You can see these yourself in a mysqlbinlog output from a binlog on the
>> master respectively the slaves.
>>
>
> I will check that when I get the setup running again. Thank you for the
> tip.
>
> If these show non-transactional on the master but transactional on the
>> first
>> slave, it sounds like you are replicating from MyISAM tables on the master
>> to InnoDB tables on the slave. Try SHOW CREATE TABLE t on a relevant table
>> on the master and the slave and see which storage engine they are using.
>>
>
> I am sure that at least the big tables use MyISAM on master and all the
> slaves. Could be that one or two small tables use InnoDB. But when the
> replication is running, just one counter is always increasing, the other is
> staying zero (or in case of switching the master while being run) staying
> fixed.
>
> Thus, the first slave sees MyISAM changes, and does not do parallel
>> operation, but writes InnoDB transactions. These InnoDB transactions are
>> then seen by following slaves which enables the parallel replication
>> algorithms.
>>
>
> This could be the reason, even though I am sure that all slaves use MyISAM
> for almost all tables. I suppose that (could be) the default engine might
> be switched to InnoDB.
>
>
>> The obvious answer is to change the tables to be InnoDB on the master.
>> Which
>> may or may not be possible in your setup.
>>
>
> Oh, this is not so easy. The size of a few MyISAM tables is reaching
> almost TBs.
>
> And thank you for the idea with the BlackHole engine :)
>
> Jan
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Problem with mariabackup

[Gordan Bobic]

That's recent enough to have the fix in it. In which case, I'm fresh out of
ideas.

On Tue, Jun 15, 2021 at 6:40 PM Riccardo Brunetti 
wrote:

> Hello.
>
> Server version: 10.3.28-MariaDB-log MariaDB Server
>
> Cheers
> Riccardo
>
>
> 15/06/2021, 17:38 Gordan Bobic ha scritto:
>
> What version of MariaDB are you using? There was a release about 6 months
> ago that caused spurious phantom locks intermittently under load. I
> observed it on both 10.3 and 10.4. It was fixed about 2 releases ago, I
> think it was a regression in a release at the beginning of the year.
> Update to the latest point release on the branch you are using and you
> might find the problem disappears.
>
> On Tue, Jun 15, 2021 at 6:32 PM Riccardo Brunetti <
> riccardo.brune...@host.it> wrote:
>
>> Hello.
>> Thank you very much for your answer.
>> What it is not clear to me is that, if I look at one of the tables that
>> appears to be locked:
>>
>>  | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query |
>> 17 | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET
>> reason = CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
>>
>> it's not a MyISAM table, but it's InnoDB..
>>
>> | wp_litespeed_crawler | CREATE TABLE `wp_litespeed_crawler` (
>>   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>>   `url` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
>>   `res` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
>> COMMENT '-=not crawl, H=hit, M=miss, B=blacklist',
>>   `reason` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'response
>> code, comma separated',
>>   `mtime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE
>> current_timestamp(),
>>   PRIMARY KEY (`id`),
>>   KEY `url` (`url`(191)),
>>   KEY `res` (`res`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=15414 DEFAULT CHARSET=utf8mb4
>> COLLATE=utf8mb4_unicode_ci |
>>
>> Cheers
>> Riccardo
>>
>>
>> 15/06/2021, 17:02 Gordan Bobic ha scritto:
>>
>> Purely guessing here, but it sounds like you have MyISAM tables. Those
>> have to be locked for the duration of the transfer to ensure data
>> consistency.
>> Convert everything except the system databases to InnoDB and there is a
>> good chance the problem will disappear.
>>
>> On Tue, Jun 15, 2021 at 4:00 PM Riccardo Brunetti <
>> riccardo.brune...@host.it> wrote:
>>
>>> Hello.
>>> We are using the mariabackup tool to stream to a remote server the
>>> backup of our databases.
>>> We are having some issue because in the middle of the operation the db
>>> server stops responding for many minutes until we ultimately kill the
>>> mariabackup process.
>>> During the lock, we can see the following:
>>>
>>> MariaDB [(none)]> show processlist;
>>>
>>> +++-+--+-+--+--+--+--+
>>> | Id | User | Host | db | Command | Time | State | Info | Progress |
>>>
>>> +++-+--+-+--+--+--+--+
>>> | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL
>>> | 0.000 |
>>> | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL
>>> | 0.000 |
>>> | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator |
>>> NULL | 0.000 |
>>> | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL
>>> | 0.000 |
>>> | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler |
>>> NULL | 0.000 |
>>> | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000
>>> |
>>> | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query |
>>> 17 | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET
>>> reason = CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
>>> | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47
>>> | Waiting for global read lock | DELETE FROM
>>> `ps4d_marketplace_product_action`
>>> WHERE `id_product` = 7356 AND `id_lan | 0.000 |
>>> | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Q

Re: [Maria-discuss] Problem with mariabackup

[Gordan Bobic]

What version of MariaDB are you using? There was a release about 6 months
ago that caused spurious phantom locks intermittently under load. I
observed it on both 10.3 and 10.4. It was fixed about 2 releases ago, I
think it was a regression in a release at the beginning of the year.
Update to the latest point release on the branch you are using and you
might find the problem disappears.

On Tue, Jun 15, 2021 at 6:32 PM Riccardo Brunetti 
wrote:

> Hello.
> Thank you very much for your answer.
> What it is not clear to me is that, if I look at one of the tables that
> appears to be locked:
>
>  | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query | 17
> | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET reason =
> CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
>
> it's not a MyISAM table, but it's InnoDB..
>
> | wp_litespeed_crawler | CREATE TABLE `wp_litespeed_crawler` (
>   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>   `url` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
>   `res` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
> COMMENT '-=not crawl, H=hit, M=miss, B=blacklist',
>   `reason` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'response
> code, comma separated',
>   `mtime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE
> current_timestamp(),
>   PRIMARY KEY (`id`),
>   KEY `url` (`url`(191)),
>   KEY `res` (`res`)
> ) ENGINE=InnoDB AUTO_INCREMENT=15414 DEFAULT CHARSET=utf8mb4
> COLLATE=utf8mb4_unicode_ci |
>
> Cheers
> Riccardo
>
>
> 15/06/2021, 17:02 Gordan Bobic ha scritto:
>
> Purely guessing here, but it sounds like you have MyISAM tables. Those
> have to be locked for the duration of the transfer to ensure data
> consistency.
> Convert everything except the system databases to InnoDB and there is a
> good chance the problem will disappear.
>
> On Tue, Jun 15, 2021 at 4:00 PM Riccardo Brunetti <
> riccardo.brune...@host.it> wrote:
>
>> Hello.
>> We are using the mariabackup tool to stream to a remote server the backup
>> of our databases.
>> We are having some issue because in the middle of the operation the db
>> server stops responding for many minutes until we ultimately kill the
>> mariabackup process.
>> During the lock, we can see the following:
>>
>> MariaDB [(none)]> show processlist;
>>
>> +++-+--+-+--+--+--+--+
>> | Id | User | Host | db | Command | Time | State | Info | Progress |
>>
>> +++-+--+-+--+--+--+--+
>> | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
>> 0.000 |
>> | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
>> 0.000 |
>> | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator |
>> NULL | 0.000 |
>> | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
>> 0.000 |
>> | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler |
>> NULL | 0.000 |
>> | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000 |
>> | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query |
>> 17 | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET
>> reason = CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
>> | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47
>> | Waiting for global read lock | DELETE FROM
>> `ps4d_marketplace_product_action`
>> WHERE `id_product` = 7356 AND `id_lan | 0.000 |
>> | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Query |
>> 46 | Waiting for global read lock | INSERT INTO wp_post_views (id, type,
>> period, count)
>> VALUES (62075, 3, '2021', 1)
>> ON DUPLIC | 0.000 |
>> | 302395 | uqlugtoj_enedina.it | localhost | uqlugtoj_enedina | Query |
>> 44 | Waiting for global read lock | UPDATE `wp_options` SET `option_value`
>> = '1623829414' WHERE `option_name` = '_transient_timeout_wc_r | 0.000 |
>> | 302404 | udlah5r1_remigio | localhost:50782 | udlah5r1_vtiger71 |
>> Query | 44 | Waiting for global read lock | insert into
>> vtiger_pbxmanager(pbxmanagerid,direction,callstatus,starttime,endtime,totalduration,bill
>> | 0.000 |
>

Re: [Maria-discuss] Problem with mariabackup

[Gordan Bobic]

Purely guessing here, but it sounds like you have MyISAM tables. Those have
to be locked for the duration of the transfer to ensure data consistency.
Convert everything except the system databases to InnoDB and there is a
good chance the problem will disappear.

On Tue, Jun 15, 2021 at 4:00 PM Riccardo Brunetti 
wrote:

> Hello.
> We are using the mariabackup tool to stream to a remote server the backup
> of our databases.
> We are having some issue because in the middle of the operation the db
> server stops responding for many minutes until we ultimately kill the
> mariabackup process.
> During the lock, we can see the following:
>
> MariaDB [(none)]> show processlist;
>
> +++-+--+-+--+--+--+--+
> | Id | User | Host | db | Command | Time | State | Info | Progress |
>
> +++-+--+-+--+--+--+--+
> | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator |
> NULL | 0.000 |
> | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL |
> 0.000 |
> | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler |
> NULL | 0.000 |
> | 274355 | root | localhost:42512 | NULL | Sleep | 407 | | NULL | 0.000 |
> | 297631 | udaww4uw_gsut | localhost:39784 | udaww4uw_gsprod | Query | 17
> | Waiting for global read lock | UPDATE `wp_litespeed_crawler` SET reason =
> CONCAT( SUBSTRING_INDEX( reason, ',', 0 ), '200', SUBSTRI | 0.000 |
> | 302083 | ud4k9xsm_pres633 | localhost | ud4k9xsm_pres633 | Query | 47 |
> Waiting for global read lock | DELETE FROM `ps4d_marketplace_product_action`
> WHERE `id_product` = 7356 AND `id_lan | 0.000 |
> | 302391 | udaww4uw_gsut | localhost:50748 | udaww4uw_gsprod | Query | 46
> | Waiting for global read lock | INSERT INTO wp_post_views (id, type,
> period, count)
> VALUES (62075, 3, '2021', 1)
> ON DUPLIC | 0.000 |
> | 302395 | uqlugtoj_enedina.it | localhost | uqlugtoj_enedina | Query |
> 44 | Waiting for global read lock | UPDATE `wp_options` SET `option_value`
> = '1623829414' WHERE `option_name` = '_transient_timeout_wc_r | 0.000 |
> | 302404 | udlah5r1_remigio | localhost:50782 | udlah5r1_vtiger71 | Query
> | 44 | Waiting for global read lock | insert into
> vtiger_pbxmanager(pbxmanagerid,direction,callstatus,starttime,endtime,totalduration,bill
> | 0.000 |
> | 302431 | utlmb6ko_6M0oD0 | localhost | utlmb6ko_6M0oD0 | Query | 38 |
> Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> '1623743080' WHERE `option_name` = 'action_scheduler_lock_a | 0.000 |
> | 302432 | uml1gkxr_58IJ3 | localhost:50836 | uml1gkxr_prod | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 |
> | 302433 | uml1gkxr_58IJ3 | localhost:50838 | uml1gkxr_prod | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> 'a:13835:{i:1226;s:32:\"ab7050de2954be1e369e358a15a17c48\"; | 0.000 |
> | 302440 | uqtx0c94_giorgio | localhost:50848 | uqtx0c94_due | Query | 38
> | Waiting for global read lock | UPDATE `wp_options` SET `option_value` =
> '[]' WHERE `option_name` = '_transient_adsforwp_transient_a | 0.000 |
> | 302473 | uw6vnwff_helpdesk | localhost:50932 | uw6vnwff_helpassist |
> Query | 30 | Waiting for global read lock | INSERT INTO ost_syslog SET
> created=NOW(), updated=NOW() ,title='DB Error #1205',log_type='Error',log |
> 0.000 |
>
> At the same time, we see messages like these in the system log:
>
> Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
> Executing FLUSH TABLES WITH READ LOCK...
>
> This is the command we launch to backup data:
>
> mariabackup --backup --stream=xbstream --user=root
> --extra-lsndir=${BCKDBDIR} -u${MYSQLUSER} -p${MYSQLPW} -H ${MYSQLHOST}"
>
> Is it somehow an expected behavior (some of the db are quite heavily used)?
> How can we avoid those locks?
>
> Thanks
> Riccardo
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Problem with InnoDB: Semaphore wait after upgrading to 10.3.27

[Gordan Bobic]

I'd look at 10.3.28, there seem to be a few locking related fixes in it.

On Tue, Mar 16, 2021 at 2:10 PM Conor Murphy 
wrote:

> Hi,
>
> I had seen some discussions about this and tried setting
>
> innodb_adaptive_hash_index = OFF
>
> But it didn't resolve the problem.
>
> /Conor
>
> ------
> *From:* Gordan Bobic 
> *Sent:* Tuesday 16 March 2021 14:06
> *To:* Conor Murphy 
> *Cc:* maria-discuss@lists.launchpad.net  >
> *Subject:* Re: [Maria-discuss] Problem with InnoDB: Semaphore wait after
> upgrading to 10.3.27
>
> Just a hunch, but have you tried disabling AHI?
> innodb_adaptive_hash_index = 0
>
> I vaguely remember having a similar problem a while back and it was AHI
> related.
>
> I hit a number of spurious locking issues in 10.4.17 that seem to have
> been resolved in 10.4.18. It is plausible that some of them also affected
> the 10.3 branch and were similarly resolved recently. Some of them
> manifested similarly until I mitigated them by setting the
> lock_wait_timeout to 30 on the basis that it was better for a query to give
> up and be retried than to be blocking things for longer. I haven't seen it
> come up since the 10.4.18 upgrade. 10.4.18 change log has quite a few fixes
> for various locking issues.
>
>
> On Tue, Mar 16, 2021 at 1:58 PM Conor Murphy <
> conor_mark_mur...@hotmail.com> wrote:
>
> Hi,
>
> We have a handful of servers running MariaDB 10.3. We noticed issues on
> two of them where we were getting issues like "InnoDB: A long semaphore
> wait:--Thread 140602802394880 has waited at dict0stats.cc line 1969 for
> 241.00 seconds the semaphore:" and crashes like "ERROR] [FATAL] InnoDB:
> Semaphore wait has lasted > 600 seconds. We intentionally crash the server
> because it appears to be hung.210215  7:14:24 [ERROR] mysqld got signal 6 ;"
>
> Turned out that these two servers were running 10.3.27 whereas the others
> were running 10.3.17. After downgrading the two problem servers to 10.3.17,
> all the issues with InnoDB semaphores stopped.
>
> Any ideas on how to track down what change between 10.3.17 to 10.3.27
> might be causing this problem?
>
> Thanks,
> Conor
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Problem with InnoDB: Semaphore wait after upgrading to 10.3.27

[Gordan Bobic]

Just a hunch, but have you tried disabling AHI?
innodb_adaptive_hash_index = 0

I vaguely remember having a similar problem a while back and it was AHI
related.

I hit a number of spurious locking issues in 10.4.17 that seem to have been
resolved in 10.4.18. It is plausible that some of them also affected the
10.3 branch and were similarly resolved recently. Some of them manifested
similarly until I mitigated them by setting the lock_wait_timeout to 30 on
the basis that it was better for a query to give up and be retried than to
be blocking things for longer. I haven't seen it come up since the 10.4.18
upgrade. 10.4.18 change log has quite a few fixes for various locking
issues.


On Tue, Mar 16, 2021 at 1:58 PM Conor Murphy 
wrote:

> Hi,
>
> We have a handful of servers running MariaDB 10.3. We noticed issues on
> two of them where we were getting issues like "InnoDB: A long semaphore
> wait:--Thread 140602802394880 has waited at dict0stats.cc line 1969 for
> 241.00 seconds the semaphore:" and crashes like "ERROR] [FATAL] InnoDB:
> Semaphore wait has lasted > 600 seconds. We intentionally crash the server
> because it appears to be hung.210215  7:14:24 [ERROR] mysqld got signal 6 ;"
>
> Turned out that these two servers were running 10.3.27 whereas the others
> were running 10.3.17. After downgrading the two problem servers to 10.3.17,
> all the issues with InnoDB semaphores stopped.
>
> Any ideas on how to track down what change between 10.3.17 to 10.3.27
> might be causing this problem?
>
> Thanks,
> Conor
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maximum possible memory usage: 123.3G

[Gordan Bobic]

You are _vastly_ over-tuning, IMO.
Do you actually have empirical evidence that each of the settings you are
changing is actually yielding a net overall improvement?

Are you actually using MyISAM? Are you _sure_ that enlarging
join_buffer_size is helping in your case? Query cache is almost always
harmful if you have done your real optimisation appropriately.

Is that mysqltuner output you pasted? It is a downright dangerous tool that
far too often gives cripplingly terrible advice.


On Fri, 19 Feb 2021, 08:08 Reindl Harald,  wrote:

> h - i thought i had the per-thread buffers down to a value which
> would be reasonable in case the max number of connections is reached
>
> i guess that two are guilty but with low "myisam_sort_buffer_size"
> optimize large tables fail from my expierience and a small
> "join_buffer_size" don't work well too
>
> are these *really* allocated unconditional per-threaf or only in case
> it's needed?
>
> myisam_sort_buffer_size = 15M
> join_buffer_size = 1M
>
> ---
>
> [--] Up for: 19h 10m 52s (174K q [2.520 qps], 688 conn, TX: 65M, RX: 11M)
> [--] Reads / Writes: 69% / 31%
> [--] Binary logging is enabled (GTID MODE: ON)
> [--] Physical Memory : 31.2G
> [--] Max MySQL memory: 123.3G
> [--] Other process memory: 0B
> [--] Total buffers: 450.0M global + 251.7M per thread (500 max threads)
> [--] P_S Max memory usage: 0B
> [--] Galera GCache Max memory usage: 0B
> [OK] Maximum reached memory usage: 4.9G (15.57% of installed RAM)
> [!!] Maximum possible memory usage: 123.3G (394.87% of installed RAM)
> [!!] Overall possible memory usage with other process exceeded memory
>
> ---
>
> max_allowed_packet  = 250M
> max_tmp_tables  = 100
> max_connect_errors  = 500
> max_delayed_threads = 30
>
> max_connections = 500
> back_log= 1000
>
> query_cache_limit   = 512K
> query_cache_min_res_unit= 1024
> query_cache_size= 32M
> query_cache_type= 1
>
> table_cache = 5000
> table_definition_cache  = 512
> thread_cache_size   = 100
> tmp_table_size  = 256M
> max_heap_table_size = 256M
>
> key_buffer_size = 32M
> sort_buffer_size= 128K
> myisam_sort_buffer_size = 15M
> join_buffer_size= 1M
> preload_buffer_size = 128K
> read_buffer_size= 128K
> read_rnd_buffer_size= 128K
>
> innodb_buffer_pool_size = 96M
> innodb-defragment   = 1
> innodb_buffer_pool_dump_at_shutdown = 0
> innodb_buffer_pool_instances= 1
> innodb_checksum_algorithm   = NONE
> innodb_doublewrite  = 1
> innodb_file_per_table   = 1
> innodb_flush_log_at_trx_commit  = 2
> innodb_flush_method = O_DIRECT
> innodb_io_capacity  = 1000
> innodb_lock_wait_timeout= 50
> innodb_log_buffer_size  = 2M
> innodb_log_file_size= 32M
> innodb_max_dirty_pages_pct  = 60
> innodb_max_purge_lag= 20
> innodb_open_files   = 300
> innodb_page_cleaners= 1
> innodb_purge_threads= 1
> innodb_read_io_threads  = 4
> innodb_stats_on_metadata= 0
> innodb_strict_mode  = 1
> innodb_table_locks  = 0
> innodb_thread_concurrency   = 0
> innodb_thread_sleep_delay   = 10
> innodb_write_io_threads = 4
> transaction-isolation   = READ-COMMITTED
>
> server-id   = 1
> expire_logs_days= 21
> max_binlog_size = 256M
> binlog-format   = ROW
> binlog_stmt_cache_size  = 256K
> binlog_cache_size   = 256K
> sync_binlog = 30
> log_bin_compress= 1
> binlog-ignore-db= autotest
> replicate-ignore-db = autotest
>
> low-priority-updates
> skip-symbolic-links
> skip-name-resolve
> safe-user-create
>
> [mariadb]
> aria_pagecache_buffer_size  = 32m
> aria_sort_buffer_size   = 32m
> aria_page_checksum  = 0
> key_cache_segments  = 8
> thread_handling = pool-of-threads
> thread_pool_idle_timeout= 900
>
> ---
>
> MariaDB 

Re: [Maria-discuss] Galera replication - optimistic locking problems

[Gordan Bobic]

I don't have the answer to your exact question off the top of my head, but
deadlocks like this are not specific to Galera - they can happen in
standalone node, even if the window of opportunity for deadlocks is much
greater with Galera.

The point I'm getting to is that if the application can't handle deadlocks,
it is broken and arguably unfit for purpose - because deadlocks in a
relational database that serves concurrent connections cannot be guaranteed
to never occur.

On Tue, 15 Dec 2020, 21:53 Antony Stone, <
antony.st...@mariadb.open.source.it> wrote:

> Hi.
>
> I hope this is a reasonable place to ask about Galera replication problems
> -
> if not, please point me elsewhere and I'll happily ask others :)
>
> I have four Debian 10 "Buster" servers installed with MariaDB 10.1.47 and
> Galera 25.3.19-2.
>
> I have other servers which write to these machines (there's no single
> "master", since I don't want any particular client to depend on any single
> master being available), and also read from them (in case it matters,
> those
> machines are running Asterisk, and writing Call Event Logs and Call Detail
> Records into MariaDB via ODBC).
>
> My problem is the optimistic locking used by Galera when replicating
> between
> nodes.  It results in Asterisk log file entries such as:
>
> WARNING[20089]: res_odbc.c:460 in ast_odbc_print_errors: SQL Execute
> returned
> an error: 40001: [ma-3.0.6][10.1.47-MariaDB-0+deb9u1]Deadlock found when
> trying to get lock; try restarting transaction
>
> Now, that's all very well for an application which can respond to such a
> warning (personally I'd call it an error, when a write did not take place,
> but
> that's a question for Asterisk, not MariaDB) and perform the retry, but
> Asterisk does not do this.
>
> Therefore I'd like to find some way to:
>
> 1. tell Galera to use pessimistic locking for replication if possible (I
> can
> accept the performance penalty)
>
> 2. tell MariaDB to automatically retry the write when the error occurs
> (although I can't think of any way that could be done, since I can't
> create a
> transaction of any sort - the operation is entirely determined for me by
> Asterisk)
>
> 3. find a High-Availability (which basically means no single point of
> failure)
> front-end for the whole cluster of 4 MariaDB servers so that the problem
> does
> not occur.
>
>
> Questions:
>
> 1. How do other people deal with this problem?
>
> 2. Are any of my potential solutions above actually feasible?  (If so,
> how?)
>
> 3. Does anyone have any alternative ideas about how to connect an
> application
> which doesn't understand retrying database writes (Asterisk) with a
> database
> which doesn't guarantee to write the data you give it (MariaDB + Galera)?
>
>
> Thanks,
>
>
> Antony.
>
> --
> Schrödinger's rule of data integrity: the condition of any backup is
> unknown
> until a restore is attempted.
>
>Please reply to the
> list;
>  please *don't* CC
> me.
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Out of sort memory, consider increasing server sort buffer size - after upgrade to 10.5.5

[Gordan Bobic]

10.5.x still seem to be problematic. Is reverting an option for you?

https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-in-mariadb-10-5-5/



On Wed, 26 Aug 2020, 01:38 Jeff Dyke,  wrote:

> Thought it was worth adding, I only added the relevant details, i hope, as
> i've been dealing with scaling MariaDB and MySQL, with innodb, for a long
> time in both high data throughput and millions of sessions a day, so i've
> taken the normal steps to mitigate/investigate this on the server, btw is a
> EC2 C5XLarge, with NVMe SSDs.  I'm not calling myself an expert, just
> looking for the non low hanging fruit.
>
> On Tue, Aug 25, 2020 at 7:24 PM Jeff Dyke  wrote:
>
>> Unfortunately, I can't easily give you a repro case, or show you the
>> queries.  I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did
>> this on Friday night.  We're not a high volume site (by design) so nothing
>> would really pop up over the weekend.  We use google data studio to present
>> reports of aggregated data after the daily events have run.  Any given
>> report page will generate from 5-25 connections from datastudio, lots of
>> joins, group bys etc.All of the daily events and reporting and ad-hoc
>> querying by our data analyst was working fine on 10.4, but on 10.5.5,
>> started generating "Out of sort memory, consider increasing server sort
>> buffer size" errors.
>>
>> That's the background, there were no query changes, no significant
>> increase in data.  I changed sort_buffer_size and made a couple other
>> optimizations that fit our environment and table types. And i'll have more
>> information in the next few days.
>>
>> All that, leads me to a simple question, that I have not been able to
>> find an answer to or missed something in the changelogs that would indicate
>> this behaviour would have changed.  Has anyone seen anything similar.  Or
>> does this ring a bell to a change I need to tune other than increasing
>> sort_buffer_size.  The only thing that changed was an upgrade.
>>
>> Thanks for reading and giving it a thought.
>>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

[Bug 1888303] Re: Intermittent buggines with user mode emulation of x86-64 on aarch64

[Gordan Bobic]

As another interesting data point - with dynamically linked qemu-x86_64,
when it doesn't work, the process is consuming about 140% of CPU. On a
successful run, the process is consuming about 30% of CPU.

-- 
You received this bug notification because you are a member of qemu-
devel-ml, which is subscribed to QEMU.
https://bugs.launchpad.net/bugs/1888303

Title:
  Intermittent buggines with user mode emulation of x86-64 on aarch64

Status in QEMU:
  New

Bug description:
  QEMU Version: 5.0.0
  ./configure --target-list=x86_64-linux-user --enable-user --prefix=/opt/qemu 
--static

  Testing using node_exporter from pmm-client-1.17.4-1.el8.x86_64.rpm

  aarch64 system is running CentOS 8 with a mainline 5.4.52 kernel built
  for 4KB memory pages.

  On aarch64 machine, invoke:

  ./qemu-x86_64-static /usr/local/percona/pmm-
  client/node_exporter.x86_64 -web.listen-address=192.168.0.10:42000
  -web.auth-file=/usr/local/percona/pmm-client/pmm.yml -web.ssl-key-
  file=/usr/local/percona/pmm-client/server.key -web.ssl-cert-
  file=/usr/local/percona/pmm-client/server.crt
  
-collectors.enabled=diskstats,filefd,filesystem,loadavg,meminfo,netdev,netstat,stat,time,uname,vmstat,meminfo_numa,textfile

  Most of the time it will outright segfault within a few seconds,
  seemingly when the prometheus server polls for data.

  But, about once every 10 times, it will not sefault and will continue
  working just fine forever.

  The dynamically linked version of qemu (built without --static) always
  works without segfaulting, but it just doesn't work, the prometheus
  server gets no data from it. Again, once in a while it will work, but
  even when it doesn't work it won't segfault.

  This vaguely feels like a memory alignment issue somewhere, but my
  debug-fu is not quite strong enough to attack the problem.

To manage notifications about this bug go to:
https://bugs.launchpad.net/qemu/+bug/1888303/+subscriptions

[Bug 1888303] [NEW] Intermittent buggines with user mode emulation of x86-64 on aarch64

[Gordan Bobic]

Public bug reported:

QEMU Version: 5.0.0
./configure --target-list=x86_64-linux-user --enable-user --prefix=/opt/qemu 
--static

Testing using node_exporter from pmm-client-1.17.4-1.el8.x86_64.rpm

aarch64 system is running CentOS 8 with a mainline 5.4.52 kernel built
for 4KB memory pages.

On aarch64 machine, invoke:

./qemu-x86_64-static /usr/local/percona/pmm-client/node_exporter.x86_64
-web.listen-address=192.168.0.10:42000 -web.auth-file=/usr/local/percona
/pmm-client/pmm.yml -web.ssl-key-file=/usr/local/percona/pmm-
client/server.key -web.ssl-cert-file=/usr/local/percona/pmm-
client/server.crt
-collectors.enabled=diskstats,filefd,filesystem,loadavg,meminfo,netdev,netstat,stat,time,uname,vmstat,meminfo_numa,textfile

Most of the time it will outright segfault within a few seconds,
seemingly when the prometheus server polls for data.

But, about once every 10 times, it will not sefault and will continue
working just fine forever.

The dynamically linked version of qemu (built without --static) always
works without segfaulting, but it just doesn't work, the prometheus
server gets no data from it. Again, once in a while it will work, but
even when it doesn't work it won't segfault.

This vaguely feels like a memory alignment issue somewhere, but my
debug-fu is not quite strong enough to attack the problem.

** Affects: qemu
 Importance: Undecided
 Status: New

-- 
You received this bug notification because you are a member of qemu-
devel-ml, which is subscribed to QEMU.
https://bugs.launchpad.net/bugs/1888303

Title:
  Intermittent buggines with user mode emulation of x86-64 on aarch64

Status in QEMU:
  New

Bug description:
  QEMU Version: 5.0.0
  ./configure --target-list=x86_64-linux-user --enable-user --prefix=/opt/qemu 
--static

  Testing using node_exporter from pmm-client-1.17.4-1.el8.x86_64.rpm

  aarch64 system is running CentOS 8 with a mainline 5.4.52 kernel built
  for 4KB memory pages.

  On aarch64 machine, invoke:

  ./qemu-x86_64-static /usr/local/percona/pmm-
  client/node_exporter.x86_64 -web.listen-address=192.168.0.10:42000
  -web.auth-file=/usr/local/percona/pmm-client/pmm.yml -web.ssl-key-
  file=/usr/local/percona/pmm-client/server.key -web.ssl-cert-
  file=/usr/local/percona/pmm-client/server.crt
  
-collectors.enabled=diskstats,filefd,filesystem,loadavg,meminfo,netdev,netstat,stat,time,uname,vmstat,meminfo_numa,textfile

  Most of the time it will outright segfault within a few seconds,
  seemingly when the prometheus server polls for data.

  But, about once every 10 times, it will not sefault and will continue
  working just fine forever.

  The dynamically linked version of qemu (built without --static) always
  works without segfaulting, but it just doesn't work, the prometheus
  server gets no data from it. Again, once in a while it will work, but
  even when it doesn't work it won't segfault.

  This vaguely feels like a memory alignment issue somewhere, but my
  debug-fu is not quite strong enough to attack the problem.

To manage notifications about this bug go to:
https://bugs.launchpad.net/qemu/+bug/1888303/+subscriptions

perf: util/syscalltbl.c:43:38: error: ‘SYSCALLTBL_ARM64_MAX_ID’ undeclared here (not in a function)

[Gordan Bobic]

I hit this FTBFS earlier today while trying to build the LT 4.19.132
kernel from source:

https://lore.kernel.org/patchwork/patch/960281/

The "quick hack" patch mentioned at the bottom of the thread gets it
to compile. Is this the correct solution, or is there a better fix? As
it stands, tools/perf doesn't seem to compile on 4.19.132.

Re: [Maria-discuss] [EXTERNAL] Re: Poor performance compared to MySQL

[Gordan Bobic]

Can you post EXPLAIN from both? Is it exactly the same?

On Tue, 2 Jun 2020, 09:21 Ling, Andy,  wrote:

> MariaDB is still significantly slower.
>
>
>
>
>
> *From:* Roberto Spadim [mailto:robe...@spadim.com.br]
> *Sent:* Mon 01 June 2020 18:33
> *To:* Ling, Andy 
> *Cc:* Mailing-List mariadb 
> *Subject:* [EXTERNAL] Re: [Maria-discuss] Poor performance compared to
> MySQL
>
>
>
> *External Message:Use caution before opening links or attachments*
>
> what happen when comparing myisam-myisam?
>
>
>
> Em seg., 1 de jun. de 2020 às 12:51, Ling, Andy 
> escreveu:
>
> I am looking at switching from MySQL to MariaDB and have been comparing
> the performance of the two.
>
> I am using the same databases on the same Windows machine and running
> queries using MySQL and MariaDB
>
> and I am finding that MariaDB is 6 times slower. A query that takes 5
> seconds on MySQL is taking 28 seconds on MariaDB.
>
>
>
> I am hoping I have some configuration wrong, so I looking for some help to
> work out what needs changing.
>
>
>
> One of the problem queries is a join between two tables. Analyzing the
> query gives..
>
>
>
> ANALYZE FORMAT=JSON SELECT r.rushid FROM rushes r LEFT JOIN browse b ON
> r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL
> 1 DAY;
>
>
> +
>
>
> ---+
>
> | ANALYZE
>
>
> |
>
>
> +
>
>
> ---+
>
> | {
>
>   "query_block": {
>
> "select_id": 1,
>
> "r_loops": 1,
>
> "r_total_time_ms": 107974,
>
> "table": {
>
>   "table_name": "r",
>
>   "access_type": "ALL",
>
>   "r_loops": 1,
>
>   "rows": 784286,
>
>   "r_rows": 784286,
>
>   "r_total_time_ms": 245.5,
>
>   "filtered": 100,
>
>   "r_filtered": 100,
>
>   "attached_condition": "r.updated < '2020-05-31 16:35:59'"
>
> },
>
> "table": {
>
>   "table_name": "b",
>
>   "access_type": "ref",
>
>   "possible_keys": ["rushid_start", "rushid"],
>
>   "key": "rushid",
>
>   "key_length": "96",
>
>   "used_key_parts": ["rushID"],
>
>   "ref": ["quentin_v3afp.r.rushID"],
>
>   "r_loops": 784286,
>
>   "rows": 10,
>
>   "r_rows": 1,
>
>   "r_total_time_ms": 106252,
>
>   "filtered": 100,
>
>   "r_filtered": 100,
>
>   "attached_condition": "trigcond(b.rushID is null)",
>
>   "using_index": true,
>
>   "not_exists": true
>
> }
>
>   }
>
> } |
>
>
> +
>
>
> ---+
>
> 1 row in set (1 min 48.244 sec)
>
>
>
> The table has been converted to the Aria engine from MyISAM. The my.ini
> file has had the following added/changed..
>
>
>
> #Not using MyISAM so save memory
>
> key_buffer_size=64k
>
>
>
> #Setting to improve Aria performance
>
> aria_pagecache_buffer_size=4007M
>
>
>
> tmp_table_size=35M
>
> max_heap_table_size=35M
>
>
>
>
>
>
>
> Thanks for any help.
>
>
>
> Andy Ling
>
>
>
>
> --
>
> DISCLAIMER:
> Privileged and/or Confidential information may be contained in this
> message. If you are not the addressee of this message, you may not copy,
> use or deliver this message to anyone. In such event, you should destroy
> the message and kindly notify the sender by reply e-mail. It is understood
> that opinions or conclusions that do not relate to the official business of
> the company are neither given nor endorsed by the company. Thank You.
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> 
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> 
> More help   : https://help.launchpad.net/ListHelp
> 
>
>
>
>
> --
>
> Roberto Spadim
> SPAEmpresarial - Software ERP/Scada
>
> Eng. Automação e Controle, Eng. Financeira
> 

Re: [Maria-discuss] Mariadb 10.3 crashing continuously on Centos 8

[Gordan Bobic]

There are several obvious issues in what you posted, to so with ulimit. You
need to look at your service scripts and make sure that they set suitable
limits for what you are configuring in my.cnf.
Are you using the CentOS 8 bundled MariaDB 10.3 or the packages from the
MariaDB repositories?

Your journalctl output seems incomplete. systemctl status output may also
be useful (make sure neither is truncated in width).

Comment out all of your tuning options and see if it starts then. Unless
you know _exactly_ what you are doing or had somebody who _really_ knows
what they are doing change any options other than innodb_buffer_pool_size,
you should probably leave them at defaults.


On Sun, May 17, 2020 at 3:00 PM Ralf Hartings  wrote:

> Hi
>
> My up-to-date Wordpress website running with mariadb 10.3 on CentOS8 (up
> to date) has been running for a long time without any issues.
> Suddenly, yesterday, my server became unreachable and did not respond to
> anything. Only power off/on helped to get it to respond again, but the
> website is down.
> I did not update the server in the last 2 weeks. No changes whatsoever.
>
> The logs show the mariadb server is restarting and crashing continuously.
> I switched off the mariadb.service to avoid any possible further issues,
> as I run a mailserver on the same machine (which runs just fine now).
>
> Please look at my post in the CentOS forum:
> https://forums.centos.org/viewtopic.php?f=54=74436=313469#p313469
> The people there are great experts in CentOS, but probably no experts in
> mariadb in the same way as you are, and I really would like to get my
> site up again ASAP.
>
> Thanks a lot for reading this post and helping me out on this!
>
> /Ralf
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] 回复: Some questions about the Aarch64 CI

[Gordan Bobic]

I thought EL7 is very much supported with aarch64. If that is the case
won't what you are suggesting effectively abandon EL7?


On Tue, Dec 10, 2019 at 9:25 PM Marko Mäkelä 
wrote:

> Hi Kevin,
>
> On Mon, Nov 25, 2019 at 8:23 AM Zheng Zhenyu 
> wrote:
> > Thanks alot for re quick response, the results looks cool. And Also, I
> noticed that the ARM job sometimes are slower than others in the
> fetch_tarball phase, this might due to that our machine is in China and the
> network connection is a little bit slow, I just got the info that our
> machine will be available in Singapore latter this month or ealier next
> month, maybe then we can then provide a machine with faster network which
> can speed up the jobs.
>
> Related to this, I wonder if it would be possible to install a newer
> operating system (or Docker image), such as CentOS 8 or Debian 10 or
> the most recent Fedora.
>
> What prompts me to ask is that I just noticed a compilation failure of
> MariaDB 10.2 that might be addressed by upgrading to a newer compiler:
>
> /buildbot/aarch64-centos-7/build/storage/innobase/row/row0log.cc: In
> function 'dberr_t
>
> _ZL17row_log_apply_opsPK5trx_tP12dict_index_tP15row_merge_dup_tP16ut_stage_alter_t.isra.94(const
> trx_t*, dict_index_t*, row_merge_dup_t*)':
> /buildbot/aarch64-centos-7/build/storage/innobase/row/row0log.cc:3734:1:
> error: could not split insn
>
> More information is available in
> https://buildbot.mariadb.org/#/builders/33/builds/260 (including a
> request to submit a compiler bug report).
>
> I remember seeing that kind of an error for some 64-bit atomic
> operation on a very old GCC targeting x86 (on CentOS 5 maybe?). While
> we have older compilers than GCC 4.8.5 for other instruction set
> architectures, I do not think that we run into internal compiler
> errors very often.
>
> On my AMD64 desktop, I am currently using GCC 9.2.1 and clang 9.0.0.
> As a developer, I prefer to have the most recent versions of tools
> whenever it is possible, for better diagnostics and possibly better
> optimizations.
>
> Best regards,
>
> Marko
> --
> Marko Mäkelä, Lead Developer InnoDB
> MariaDB Corporation
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Some questions about the Aarch64 CI

[Gordan Bobic]

On Fri, Nov 29, 2019 at 9:22 AM Marko Mäkelä 
wrote:

> On Thu, Nov 28, 2019 at 3:24 PM Gordan Bobic 
> wrote:
> > I would be very interested to see some tests data on unalighed access
> cost on various aarch64 chips. On various 32-bit ARM chips (including those
> >= ARMv6) the unaligned access performance hit was quite dramatic.
>
> I wonder if the unaligned access could ever end up costing more than
> the instruction decoding overhead for implementing multi-byte access
> via single-byte operations. (In the past, when unaligned access could
> have been supported by an interrupt to the operating system, like
> Digital UNIX on the Alpha, I could easily believe it. But, now we are
> talking about hardware-supported unaligned access.)
>

Last time I measured it, the difference was somewhere in the region of 20x
slower on ARMv5 (between auto-alignment fixup in the kernel enabled and
disabled for code that does unaligned access).
Obviously, the code that does unaligned access with the auto-fixup disabled
would just read/write garbage, with tragic consequences in some cases. One
of the reasons I stopped using ext4, for example, is because when I started
working on ARM32, I discovered that fsck.ext4 is guilty of loading fs
blocks into char[4096], and being char this array is byte aligned.
Unfortunately, it would then go on to cast this into a struct with a bigger
alignment requirement. The rest you can probably imagine.
Most developers are not even aware that this kind of a problem exists
because they only ever wrote code that runs on platforms that have
transparent alignment fixup like x86, so the worst case scenario is that it
runs slower rather than resulting in outright data corruption.

IIRC Intel compiler's 16-byte align option effectively makes every array
definition happen as if it were pragma aligned to 16 bytes explicitly, this
avoiding the problem. Of course, that doesn't help on any platform other
than x86, and there it's main purpose is for optimizing auto-vectorization
of loops that operate on such arrays.
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Some questions about the Aarch64 CI

[Gordan Bobic]

For what it's worth, unaligned access does come with a performance penalty,
typically somewhere in the 1-10% range on x86, depending on the generation
of chip used. It has been _mostly_ mitigated on recent x86 chips, and IIRC
Intel's C compiler does have an option to align all structs and arrays to a
16 byte boundary.
I would be very interested to see some tests data on unalighed access cost
on various aarch64 chips. On various 32-bit ARM chips (including those >=
ARMv6) the unaligned access performance hit was quite dramatic.

On Wed, Nov 27, 2019 at 11:36 AM Marko Mäkelä 
wrote:

> Hi Daniel,
>
> You seem to be right that the compilers are already mostly doing the
> right thing. Here is a notable exception where GCC lags behind clang
> (unnecessary use of stack):
> https://gcc.gnu.org/bugzilla/show_bug.cgi?id=89804
>
> I created another test program, checking how mach_read_from_4() gets
> compiled. It turns out that on Aarch64 and POWER, unaligned reads are
> being used by default:
> https://godbolt.org/z/ZcavM4
>
> For 32-bit ARM, -march=armv6 seems to enable unaligned reads. For
> RISC-V and WebAssembly, the code is rather ugly. :-)
>
> So, indeed, there does not appear to be much to micro-optimize here.
>
> Marko
> --
> Marko Mäkelä, Lead Developer InnoDB
> MariaDB Corporation
>
> ___
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04

[Gordan Bobic]

On Wed, 16 Oct 2019, 11:07 Reindl Harald,  wrote:

>
>
> Am 16.10.19 um 11:59 schrieb Gordan Bobic:
> > I have seen failures when upgrading from 10.0, with the latest 10.1 ->
> > 10.2 -> 10.3 -> 10.4 unless I issued a clean shutdown between the latest
> > 10.1 and 10.2 as recently as last week, with the latest RPMs for each
> > version.
> >
> > You are trying to make an argument analogous to "smoking can't be
> > harmful because I've been smoking for 50 years and I'm not dead yet".
> >
> > What is your sample size?
>
> * 10 years
> * 12 setups with innodb
> * MySQL 5.0 -> 5.1 -> 5.5
> * MariaDB 5.5 -> 10.0 -> 10.1 -> 10.2 -> 10.3
>
> Mo Sep 02 2019: 10.2.26 -> 10.3.17
>

I did 6 such upgrades for customers in the last month alone. Granted, I
only re-tested for this issue on one of those 6 because it was on a setup
easy to snapshot and rollback, I do such things most months (only when
upgrading from <= 10.1 to => 10.2, obviously) since I first got bitten by
it a few years ago. And I have yet to see it work without a clean shutdown.

So clearly there must be at least an element of luck / write load in play.
And I don't put much faith in luck.


> and frankly i expect whatever software to be able to read it's old data


Famous last words...
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp