7 ms·

MySQL now shows its thread names at OS level for better troubleshooting

131 points by tanelpoder 5y ago

user3939382 5y ago
The improvement I would like to help my MySQL troubleshooting is that bad queries give better and more specific error messages than “You have an error somewhere”. That’s not to downplay the complexity of providing that, I’m sure it’s technically difficult, but this is basically the worst debugging experience I’ve had of any language.
- ncake 5y ago
  This. "you've got a syntax error at around here: [the entire query]. go consult the manual or something idk"
  - willcipriano 5y ago
    I like Datagrip for writing SQL for this purpose.
    - lovingCranberry 5y ago
      I've used to write my queries in pgAdmin or VSCode. I downloaded DataGrip after reading your comment, and damn, the autocomplete is just so good. It even shows me the arguments of Postgresql extensions like TimescaleDB. This almost reads like an ad, but I just wanted to thank you for changing my life :P
  - gleenn 5y ago
    I feel like this is MySQL's model very well. A long time ago they traded some strictness for speed, because the average MySQL user was doing some basic CRUD apps and it never mattered. But adding useful checks and good messages everywhere requires a fair amount of engineer effort and probably costs more processing than whatever performance benchmark they are probably optimizing for.
    - hackernewds 5y ago
      It shouldn't be tough to tell you "a comma is missing in this line". currently you get another absurd not only vague, but incorrect error codes.
      - gleenn 5y ago
        You say that but having worked on a DSL for a while, you end up sprinkling this code all over the place and things which might be simple loops, or string.splits, etc become much more gnarly.
- jamesfinlayson 5y ago
  This took some getting used to after six years of working with Postgres. In my current set-up (inherited), the unit tests run using H2 while running code talks to MySQL, so I have two databases that give unhelpful generic error messages.
- winrid 5y ago
  For that, we can sell you something called Oracle!
  - muhehe 5y ago
    ...in which you get even worse messages, but for more money!
    - winrid 5y ago
      wow, is this true?
      - tanelpoder 5y ago
        In my experience (a 25+ year Oracle database geek), MySQL error messages confuse me more and Oracle's are better. But Oracle also has some error messages where you have to know how parsers (and semantic/typecheckers) work, in order to understand why the message was worded like that. But Oracle usually does point out the exact character/token in the SQL statement where the error originated from.
- linsomniac 5y ago
  Is this better in MariaDB? Over the last hour I have been writing some Python code talking to MariaDB, and the errors I got about syntax issues all seemed to point fairly closely to what the issue was. Mind you, these are fairy simple queries (perhaps more complex queries produce worse errors?) Honest question. Been migrating a 5 year old Percona MySQL galera cluster over to MariaDB galera. Galera multi-master clusters, for my use case, are a huge win. A month ago I had started down the path of replacing it with CockroachDB, but Cockroach only supports UTF-8 and my use case requires LATIN-1, sadly.
  - tpxl 5y ago
    It's no better in MariaDB.
  - kmos17 5y ago
    I wasn’t aware of Galera, that’s really interesting. how is the transition from percona to mariadb? any major pain points?
    - linsomniac 5y ago
      Percona -> MariaDB has been super smooth. With Percona over the years I've had a few updates that didn't apply and I had to go in and fix things like repo locations or keys as things evolved, but mostly it was just a workhorse. I guess one issue we had was it would never autostart on a reboot (despite "systemctl enable"), but I was never able to track it down in a reproducible way. With MariaDB I'm using the digitalocean repos and I guess we'll see how stable those are. Galera has been great, have used it for 5+ years and only a couple hiccups. This is a rather small database, but nearly mission critical. A couple times the cluster failed to start and I had to figure out the most recent node and manually bootstrap from it. I built some small shell tools to tell me if the cluster was happy, and bootstrap, because I had to do it so in frequently I could never remember the queries and files involved, and didn't want to have to read through my docs when the cluster was down. Our use case is a small e-mail relay, so the win with galera is each e-mail server also runs a database node, and since they are master/master we can take one node down at a time for maintenance without having to do any failovers or the like.
- dabber21 5y ago
  it helps when you surround each variable/name with backticks, then you get better error messages. But I agree MySQL should do that by default
LAC-Tech 5y ago
What's MySQL like as a database these days? I get the impression that it used to be a bit of a mess but it's improved a lot and has some advantages over Postgres. Did anyone choose it over pg, and if so, why?
- epberry 5y ago
  I think to think of it as an interface, https://dbdb.io/browse?compatible=mysql https://dbdb.io/browse?compatible=mysql These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.
- skanga 5y ago
  In terms of popularity, it sure looks like that ... https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend
- noaheverett 5y ago
  I haven’t used PG in production so I’m probably biased, naive or both, so I know this isn’t a direct response to your question, but… MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups. I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.
- jorams 5y ago
  It's mostly fine and certainly has some advantages, but it also continues to have weird behavior that can randomly creep up. My recent favorite is[1]: > MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. Because of course it's very reasonable to accept a foreign key specification but completely ignore it. [1]: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...
saagarjha 5y ago
Setting thread names is quite helpful for debugging. One of the projects I work on hoists Linux tasks into native threads, and we use the task comm and PID as the name, which is super helpful for debugging. That being said, each platform has their own weird quirks when it comes to names: Linux only accepts names up to 15 characters, for example. And macOS only lets you set the thread name from your own thread, so even though it uses the same pthread_setname_np call the parameters it takes are different :(
- tanelpoder 5y ago
  By the way, JVMs also set thread names for their own housekeeping threads (on Linux at least, not sure from which version) and allow applications to name them too. I wrote an example here: https://tanelpoder.com/posts/measuring-java-jvm-thread-level-activity-on-linux/ https://tanelpoder.com/posts/measuring-java-jvm-thread-level...
- teakettle42 5y ago
  “_np” stands for “non-posix” and is used for vendor-specific extensions; non-portability is expected.
whalesalad 5y ago
rearranging deck chairs on the titanic
- RandomBK 5y ago
  Hmm? MySQL is perhaps the most popular open source database when looking at # users served. Uber, Facebook, Twitter, all use it as their underlying data store. I'd say it's _far_ from "titanic", even if Postgres is quite popular these days.
- zmxz 5y ago
  How is this comment useful? It resembles what children comment about popular online games when they don't like them. If you don't use MySQL, it doesn't mean you're aware of who does, how they use it and what's the revenue produced. It'd be great if you could expand your post and add details, perhaps even some (anecdotal) evidence that sheds more light into your point of view.
freedomben 5y ago
Nice, this will be really helpful because it will end up in system monitoring logs/graphs. I've had to hunt down issues before and when you can see what it is that's eating a bunch of CPU it can be a very helpful hint. Thanks MySQL team!

131 points by tanelpoder 5y ago

The improvement I would like to help my MySQL troubleshooting is that bad queries give better and more specific error messages than “You have an error somewhere”. That’s not to downplay the complexity of providing that, I’m sure it’s technically difficult, but this is basically the worst debugging experience I’ve had of any language.

ncake 5y ago
This. "you've got a syntax error at around here: [the entire query]. go consult the manual or something idk"
- willcipriano 5y ago
  I like Datagrip for writing SQL for this purpose.
  - lovingCranberry 5y ago
    I've used to write my queries in pgAdmin or VSCode. I downloaded DataGrip after reading your comment, and damn, the autocomplete is just so good. It even shows me the arguments of Postgresql extensions like TimescaleDB. This almost reads like an ad, but I just wanted to thank you for changing my life :P
- gleenn 5y ago
  I feel like this is MySQL's model very well. A long time ago they traded some strictness for speed, because the average MySQL user was doing some basic CRUD apps and it never mattered. But adding useful checks and good messages everywhere requires a fair amount of engineer effort and probably costs more processing than whatever performance benchmark they are probably optimizing for.
  - hackernewds 5y ago
    It shouldn't be tough to tell you "a comma is missing in this line". currently you get another absurd not only vague, but incorrect error codes.
    - gleenn 5y ago
      You say that but having worked on a DSL for a while, you end up sprinkling this code all over the place and things which might be simple loops, or string.splits, etc become much more gnarly.
jamesfinlayson 5y ago
This took some getting used to after six years of working with Postgres. In my current set-up (inherited), the unit tests run using H2 while running code talks to MySQL, so I have two databases that give unhelpful generic error messages.
winrid 5y ago
For that, we can sell you something called Oracle!
- muhehe 5y ago
  ...in which you get even worse messages, but for more money!
  - winrid 5y ago
    wow, is this true?
    - tanelpoder 5y ago
      In my experience (a 25+ year Oracle database geek), MySQL error messages confuse me more and Oracle's are better. But Oracle also has some error messages where you have to know how parsers (and semantic/typecheckers) work, in order to understand why the message was worded like that. But Oracle usually does point out the exact character/token in the SQL statement where the error originated from.
linsomniac 5y ago
Is this better in MariaDB? Over the last hour I have been writing some Python code talking to MariaDB, and the errors I got about syntax issues all seemed to point fairly closely to what the issue was. Mind you, these are fairy simple queries (perhaps more complex queries produce worse errors?) Honest question. Been migrating a 5 year old Percona MySQL galera cluster over to MariaDB galera. Galera multi-master clusters, for my use case, are a huge win. A month ago I had started down the path of replacing it with CockroachDB, but Cockroach only supports UTF-8 and my use case requires LATIN-1, sadly.
- tpxl 5y ago
  It's no better in MariaDB.
- kmos17 5y ago
  I wasn’t aware of Galera, that’s really interesting. how is the transition from percona to mariadb? any major pain points?
  - linsomniac 5y ago
    Percona -> MariaDB has been super smooth. With Percona over the years I've had a few updates that didn't apply and I had to go in and fix things like repo locations or keys as things evolved, but mostly it was just a workhorse. I guess one issue we had was it would never autostart on a reboot (despite "systemctl enable"), but I was never able to track it down in a reproducible way. With MariaDB I'm using the digitalocean repos and I guess we'll see how stable those are. Galera has been great, have used it for 5+ years and only a couple hiccups. This is a rather small database, but nearly mission critical. A couple times the cluster failed to start and I had to figure out the most recent node and manually bootstrap from it. I built some small shell tools to tell me if the cluster was happy, and bootstrap, because I had to do it so in frequently I could never remember the queries and files involved, and didn't want to have to read through my docs when the cluster was down. Our use case is a small e-mail relay, so the win with galera is each e-mail server also runs a database node, and since they are master/master we can take one node down at a time for maintenance without having to do any failovers or the like.
dabber21 5y ago
it helps when you surround each variable/name with backticks, then you get better error messages. But I agree MySQL should do that by default

LAC-Tech 5y ago

What's MySQL like as a database these days? I get the impression that it used to be a bit of a mess but it's improved a lot and has some advantages over Postgres. Did anyone choose it over pg, and if so, why?

epberry 5y ago
I think to think of it as an interface, https://dbdb.io/browse?compatible=mysql https://dbdb.io/browse?compatible=mysql These days you have shiny companies like PlanetScale and SingleStore implementing the MySQL wire protocol.
skanga 5y ago
In terms of popularity, it sure looks like that ... https://db-engines.com/en/ranking_trend https://db-engines.com/en/ranking_trend
noaheverett 5y ago
I haven’t used PG in production so I’m probably biased, naive or both, so I know this isn’t a direct response to your question, but… MySQL has been great for every project I’ve used it in and haven’t had any issues. Largest app we used it in was Twitpic which handled high query traffic and along with the built-in replication, it was easy to use. We had replication lag on spinning disks (this was 2009) but switching to SSDs fixed it. Backups with xtrabackup from Percona is a key piece though for taking incremental or full DB backups. I think back-in-the-day, PG didn’t have replication built-in and that always kept me using MySQL, but I’ve honestly never understood the bad rap MySQL got in years previous as I’ve never run into any of the described issues and it has been rock solid for me in apps of all sizes.
jorams 5y ago
It's mostly fine and certainly has some advantages, but it also continues to have weird behavior that can randomly creep up. My recent favorite is[1]: > MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. Because of course it's very reasonable to accept a foreign key specification but completely ignore it. [1]: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-ke...

saagarjha 5y ago

Setting thread names is quite helpful for debugging. One of the projects I work on hoists Linux tasks into native threads, and we use the task comm and PID as the name, which is super helpful for debugging. That being said, each platform has their own weird quirks when it comes to names: Linux only accepts names up to 15 characters, for example. And macOS only lets you set the thread name from your own thread, so even though it uses the same pthread_setname_np call the parameters it takes are different :(

tanelpoder 5y ago
By the way, JVMs also set thread names for their own housekeeping threads (on Linux at least, not sure from which version) and allow applications to name them too. I wrote an example here: https://tanelpoder.com/posts/measuring-java-jvm-thread-level-activity-on-linux/ https://tanelpoder.com/posts/measuring-java-jvm-thread-level...
teakettle42 5y ago
“_np” stands for “non-posix” and is used for vendor-specific extensions; non-portability is expected.

whalesalad 5y ago

rearranging deck chairs on the titanic

RandomBK 5y ago
Hmm? MySQL is perhaps the most popular open source database when looking at # users served. Uber, Facebook, Twitter, all use it as their underlying data store. I'd say it's _far_ from "titanic", even if Postgres is quite popular these days.
zmxz 5y ago
How is this comment useful? It resembles what children comment about popular online games when they don't like them. If you don't use MySQL, it doesn't mean you're aware of who does, how they use it and what's the revenue produced. It'd be great if you could expand your post and add details, perhaps even some (anecdotal) evidence that sheds more light into your point of view.

freedomben 5y ago

Nice, this will be really helpful because it will end up in system monitoring logs/graphs. I've had to hunt down issues before and when you can see what it is that's eating a bunch of CPU it can be a very helpful hint. Thanks MySQL team!