Mike
Well-Known Member
Sheesh, I am black and blue and have had my backside handed to me by a MariaDB database. I am starting to think I need a DBA, but now that it is all over, I realize I did come out on top.
I think the site likely suffered about 10 minutes downtime, as a result. If that was the case, my apologies, but I really was in it, up to my eyebrows. And believe me, you cannot beat up on me any more than I have already beat up on myself.
I run a read-only script on all of my servers that keeps watch on the MySQL server. I run the script, every week or so, to get recommendations on how to better optimize the MySQL server. The script makes recommendations, which I evaluate and then decide which recommendations to apply to the MySQL server configuration file.
Once the changes are made to the configuration file, I have to re-start the MySQL server, for those changes to take effect. So the process requires three steps - get the recommendations, apply the changes to a file and then re-start the MySQL server. Easy-peasy, right? All of this is to try to optimize the MySQL server against this site in its current state.
I've been making tiny changes, over the last 5 or 6 months, trying to sneak up on more optimal settings and, until today, things have been going quite well. I found a couple of small suggestions, early this afternoon, so I thought I applied them to the configuration file. Only my fat fingers must have made a typo, because when I went to re-start the MySQL server, it rolled over on its back and died.
I checked the file, but did not see any obvious errors, so I attempted another re-start. Which was a waste of my time. Who was it, Einstein, who said doing the same thing over and over, expecting a different result is the definition of insanity?
I finally just moved the configuration file and moved in the original file, that I had backed up, prior to making any changes. I attempted a re-start and was golden. Of course, that was a blank config file, so I was back at square 1. And after searching the server high and low, I was unable to find the file that I had moved, so I could get my variable settings back.
I could recall most of the most recent edits to the config file, so I stuck them in, saved the file, attempted another restart and everything was good. But there were two variables I could not recall the most recent settings for, so I am digging around this stack of notes on my desks, looking for the numbers. Finally, I decided rather than trying to read every scrap of paper on my desk, I would just run the script and see what it recommended for those two variables.
I logged into the server and tried to run the script. But it was asking for an administrative log-in and password. Huh? That was the first time that had ever happened. No matter what I tried, it would not allow me to run the script against the database.
After about an hour of frustration, I contacted a tech at the datacenter, and asked his advice. He thought the syntax on my variable settings was incorrect. Where I would have a line that would read -
query_cache_size= 32M
- he felt the space after the equal sign was wrong. That's the way I've always edited those files, but I went in and removed all of the spaces from each line. I saved the file, re-started the database server and tried to access the script to get the recommendations. No hope.
I hammered and banged on it and could not get it to work, no matter what I tried. I could comment out all of the variables and then run the script, but that meant the edits would not be reflected, so that was not an option.
I checked another server, and sure enough, it would not allow me to run the script without log-in details.
By then, I was ready to start throwing things. So, I checked a third server, and the script ran just fine. So I checked the config file on that script, and, well, <cough, cough> I had managed to stick the fork in myself. For any of you who are DBAs, go ahead and try to set up a my.cnf file without [mysqld].
I've no idea how that line was removed, but I had to have removed it. The day I got this server, I installed mysqltuner.pl on it, ran cp /etc/my.cnf /etc/my.cnf.old and started editing my.cnf. Today, when I apparently knackered the my.cnf file, I just did a cp /etc/my.cnf.old /etc/my.cnf and started editing. But somehow, I managed to delete [mysqld].
If anyone is interested, a synonym for bloody idiot would be Mike.
If there is a DBA around, can you tell me if there is any real difference in these lines -
innodb_buffer_pool_size= 1024M
innodb_buffer_pool_size=1024M
I've always used the first, but the datacenter tech insisted I had to use the second.
So, how did you spend your afternoons? I think I am going to unplug from the matrix and go watch the Super Bowl...
I think the site likely suffered about 10 minutes downtime, as a result. If that was the case, my apologies, but I really was in it, up to my eyebrows. And believe me, you cannot beat up on me any more than I have already beat up on myself.
I run a read-only script on all of my servers that keeps watch on the MySQL server. I run the script, every week or so, to get recommendations on how to better optimize the MySQL server. The script makes recommendations, which I evaluate and then decide which recommendations to apply to the MySQL server configuration file.
Once the changes are made to the configuration file, I have to re-start the MySQL server, for those changes to take effect. So the process requires three steps - get the recommendations, apply the changes to a file and then re-start the MySQL server. Easy-peasy, right? All of this is to try to optimize the MySQL server against this site in its current state.
I've been making tiny changes, over the last 5 or 6 months, trying to sneak up on more optimal settings and, until today, things have been going quite well. I found a couple of small suggestions, early this afternoon, so I thought I applied them to the configuration file. Only my fat fingers must have made a typo, because when I went to re-start the MySQL server, it rolled over on its back and died.
I checked the file, but did not see any obvious errors, so I attempted another re-start. Which was a waste of my time. Who was it, Einstein, who said doing the same thing over and over, expecting a different result is the definition of insanity?
I finally just moved the configuration file and moved in the original file, that I had backed up, prior to making any changes. I attempted a re-start and was golden. Of course, that was a blank config file, so I was back at square 1. And after searching the server high and low, I was unable to find the file that I had moved, so I could get my variable settings back.
I could recall most of the most recent edits to the config file, so I stuck them in, saved the file, attempted another restart and everything was good. But there were two variables I could not recall the most recent settings for, so I am digging around this stack of notes on my desks, looking for the numbers. Finally, I decided rather than trying to read every scrap of paper on my desk, I would just run the script and see what it recommended for those two variables.
I logged into the server and tried to run the script. But it was asking for an administrative log-in and password. Huh?
That was the first time that had ever happened. No matter what I tried, it would not allow me to run the script against the database.After about an hour of frustration, I contacted a tech at the datacenter, and asked his advice. He thought the syntax on my variable settings was incorrect. Where I would have a line that would read -
query_cache_size= 32M
- he felt the space after the equal sign was wrong. That's the way I've always edited those files, but I went in and removed all of the spaces from each line. I saved the file, re-started the database server and tried to access the script to get the recommendations. No hope.
I hammered and banged on it and could not get it to work, no matter what I tried. I could comment out all of the variables and then run the script, but that meant the edits would not be reflected, so that was not an option.
I checked another server, and sure enough, it would not allow me to run the script without log-in details.
By then, I was ready to start throwing things. So, I checked a third server, and the script ran just fine. So I checked the config file on that script, and, well, <cough, cough> I had managed to stick the fork in myself. For any of you who are DBAs, go ahead and try to set up a my.cnf file without [mysqld].
I've no idea how that line was removed, but I had to have removed it. The day I got this server, I installed mysqltuner.pl on it, ran cp /etc/my.cnf /etc/my.cnf.old and started editing my.cnf. Today, when I apparently knackered the my.cnf file, I just did a cp /etc/my.cnf.old /etc/my.cnf and started editing. But somehow, I managed to delete [mysqld].
If anyone is interested, a synonym for bloody idiot would be Mike.
If there is a DBA around, can you tell me if there is any real difference in these lines -
innodb_buffer_pool_size= 1024M
innodb_buffer_pool_size=1024M
I've always used the first, but the datacenter tech insisted I had to use the second.
So, how did you spend your afternoons? I think I am going to unplug from the matrix and go watch the Super Bowl...
