Sorry About That

[Eric]

I found out why the site's hard drive space is filling up faster than it should be and I broke the database server trying to fix it. Sorry about that.

[Eric]

The problem was some runaway database log files. They started piling up the second I did the freaking site software update. I'm not sure why it is happening, but I have the log files automatically pruning themselves now, so it won't be an issue while I try to figure out why it is occurring, in the first place.

[crockett]

💪

[crockett]

all log files and sql log tables >/dev/null

just sayin lol

I really did that with apache access logs and more. If there's an issue, I turn it on for a couple hours and find the problem.

[Eric]

10 minutes ago, crockett said:

all log files and sql log tables >/dev/null

just sayin lol

I really did that with apache access logs and more. If there's an issue, I turn it on for a couple hours and find the problem.

These were binlog files and I'm not sure why they suddenly started gaining so much weight. I don't want to just zap them until I know what's up. I doubt though that any operational database issue gives a crap about a daily log file generated more than a week ago though. I've got them set to prune at two days and the site just lost 80gb of unneeded baggage. Whatever is happening is not affecting the operation of the site and the log files are reined in. Of course, I'll lose sleep until I figure out the why of it. That is how my twisted mind works.

[crockett]

18 minutes ago, Eric said:

These were binlog files and I'm not sure why they suddenly started gaining so much weight. I don't want to just zap them until I know what's up. I doubt though that any operational database issue gives a crap about a daily log file generated more than a week ago though. I've got them set to prune at two days and the site just lost 80gb of unneeded baggage. Whatever is happening is not affecting the operation of the site and the log files are reined in. Of course, I'll lose sleep until I figure out the why of it. That is how my twisted mind works.

Did you check what is dumping into the binlogs? You can get a sample with mysqlbinlog.

https://mariadb.com/kb/en/using-mysqlbinlog/

Also, look for statements that modify a large number of rows.

Only option may be to put binlogs on a compressed file system or reduce their retention period further.

[Eric]

Just now, crockett said:

Did you check what is dumping into the binlogs? You can get a sample with mysqlbinlog.

https://mariadb.com/kb/en/using-mysqlbinlog/

 

Yeah. There aren't any errors and not much binary data. The files mostly contain formatted webpages from the site. There are only about eighty of them a day being generated, so they are a small fraction of the overall output of the site. There has to be a common thread to them and with a little study, it shouldn't be all that hard to find it. The frustrating part is sorting through all the extraneous crap to get to the meat of it.

[crockett]

6 minutes ago, Eric said:

Yeah. There aren't any errors and not much binary data. The files mostly contain formatted webpages from the site. There are only about eighty of them a day being generated, so they are a small fraction of the overall output of the site. There has to be a common thread to them and with a little study, it shouldn't be all that hard to find it. The frustrating part is sorting through all the extraneous crap to get to the meat of it.

I added this to my last comment, just making sure you see it:

 

Also, look for statements that modify a large number of rows.

Only option may be to put binlogs on a compressed file system or reduce their retention period further.

[Eric]

12 minutes ago, crockett said:

I added this to my last comment, just making sure you see it:

 

Also, look for statements that modify a large number of rows.

Only option may be to put binlogs on a compressed file system or reduce their retention period further.

Yeah. I'm crawling the files now, trying to find any commonalities. They are there. I just have to find them. I'll find the problem, if my eyes hold out. This stuff is murder on the eyes.

[Historian]

1 hour ago, Eric said:

The problem was some runaway database log files. They started piling up the second I did the freaking site software update. I'm not sure why it is happening, but I have the log files automatically pruning themselves now, so it won't be an issue while I try to figure out why it is occurring, in the first place.

Not truncating?

[Eric]

9 minutes ago, Historian said:

Not truncating?

Nope. If there was a default limit at which they were pruned, it had not been reached and the file sizes were very large.

[Eric]

52 minutes ago, crockett said:

I added this to my last comment, just making sure you see it:

 

Also, look for statements that modify a large number of rows.

Only option may be to put binlogs on a compressed file system or reduce their retention period further.

 

For ****'s sake. I'm an idiot. I updated MySQL just before updating the site's software. These freaking log files are part of the master-slave replication system in the current versions of MySQL. It's not a bug, it's a feature.

All I had to do was turn the feature off. I don't need it. I feel like a dumbass, but I'm glad to have figured out what was causing this.

[crockett]

15 minutes ago, Eric said:

 

For ****'s sake. I'm an idiot. I updated MySQL just before updating the site's software. These freaking log files are part of the master-slave replication system in the current versions of MySQL. It's not a bug, it's a feature.

All I had to do was turn the feature off. I don't need it. I feel like a dumbass, but I'm glad to have figured out what was causing this.

You have no clue how many times I saved my eyes and stressing my ass out, by not ... you know the song ... updating lol

[Eric]

1 minute ago, crockett said:

You have no clue how many times a saved my eyes and stressing my ass out, by not ... you know the song ... updating lol

All the complex software dependencies, coupled with the always-emerging security threats just don't make that a realistic option for a server and a site like this. Most of the port scans and hack attempts that hit servers like mine are coming from compromised servers that weren't up-to-date and let the scumbags in. Keeping updated isn't a guarantee that the server won't get hacked, but I guarantee it will get hacked if I don't

[Eric]

I'm glad enough though that this problem drew my attention to the issue of the bloated file attachment system. On it's on, it wasn't going to swamp the server tomorrow, but the directory that houses the attachment files was enormous and simply backing it up and syncing it with other locations was a real PITA, not to mention the chunk of space it was taking up on my local computer. After putting that directory on a diet, it lost more than 40% of it's size, which is actullay a savings of about three times that, when you factor in the backups of the data that reside on the server.

[Eric]

Removing the overhead of the binlog replication system seems to have sped up page load times as well and the server load is down about 8%.

[crockett]

2 minutes ago, Eric said:

All the complex software dependencies, coupled with the always-emerging security threats just don't make that a realistic option for a server and a site like this. Most of the port scans and hack attempts that hit servers like mine are coming from compromised servers that weren't up-to-date and let the scumbags in. Keeping updated isn't a guarantee that the server won't get hacked, but I guarantee it will get hacked if I don't

My data center monitors port activity on network level, any outbound ddosing etc gets blocked and notified in real time. They also use honey pots and block IPs quickly.

On the server I use fail2ban, that gets basically everything before they get in.

[root ~]#  php -v
PHP 5.4.16 (cli) (built: Aug 11 2016 21:24:59)

5 Years and running.

[Eric]

12 minutes ago, crockett said:

My data center monitors port activity on network level, any outbound ddosing etc gets blocked and notified in real time. They also use honey pots and block IPs quickly.

On the server I use fail2ban, that gets basically everything before they get in.

[root ~]#  php -v
PHP 5.4.16 (cli) (built: Aug 11 2016 21:24:59)

5 Years and running.

I'm just one guy, with limited resources. I'm competent with network security, but I'm not an expert. I need all the edge I can get and keeping things up-to-date helps.

I do agree, as far as the core services like mysql and php go. Nothing much changes there. When a security exploit does emerge, there will be a patch for it, as long as the version you are using isn't end-of-life. I updated mysql this time  because the current version of the site's software would no longer run on it and there were several security issues for the site software that required me to update.

I use fail2ban, along with some other things and a decent firewall. Even so, there is no magic bullet. Good security is layered as deeply as possible. There is so much to know though and it all changes so quickly. Keeping up is difficult. I haven't had an incident since 2010 and that one was because of Tapatalk. I am a worrier however and I worry.

It sounds like you have a good handle on your setup though.

[Eric]

25 minutes ago, crockett said:

My data center monitors port activity on network level, any outbound ddosing etc gets blocked and notified in real time. They also use honey pots and block IPs quickly.

On the server I use fail2ban, that gets basically everything before they get in.

[root ~]#  php -v
PHP 5.4.16 (cli) (built: Aug 11 2016 21:24:59)

5 Years and running.

Now the next PITA issue I have to deal with is email. MY server's IP number isn't blacklisted by any of the RBLs, but several big ISPs are currently blocking legitimate email from my server, because of the nature of the email and because email servers and adminstrators are evil bastards. I have to sort through all the crap stacking up in my mail queue and sort out what ISP's admin's asses I have to kiss to get them to accept my email. I hate email issues more than security issues. The whole system is so rampant with abuse and standards & practices vary so much, from ISP to ISP. This is going to suck.

[willie-pete]

 

 

 

 

[crockett]

37 minutes ago, Eric said:

Now the next PITA issue I have to deal with is email. MY server's IP number isn't blacklisted by any of the RBLs, but several big ISPs are currently blocking legitimate email from my server, because of the nature of the email and because email servers and adminstrators are evil bastards. I have to sort through all the crap stacking up in my mail queue and sort out what ISP's admin's asses I have to kiss to get them to accept my email. I hate email issues more than security issues. The whole system is so rampant with abuse and standards & practices vary so much, from ISP to ISP. This is going to suck.

I know all to well about these issues. I have been using my own domains for emails since the mid 90s and I refuse to use liberal shits like Google or Yahoo.

Obviously they want to force everybody to use their free email service until everybody is hooked, so they can introduce paid services. Yet another (semi) monopoly forming. They keep adding "security" requirements that many small holsters can't comprehend or maintain.

I'm not shy telling businesses that they didn't get my emails because they use a tyrannical provider named Google. Back in the day I sold many hosting packages and domains with that slogan alone. At some point I made enough money from hosting. I had over 80 businesses on ONE box with well over 100 domains. I paid 120 bucks for that dedicated server each month and charged some companies 200 bucks for hosting, a domain and a few email addresses. The icing was the website development and maintenance.

But at some point I got tired of customer problems and started selling products through my own shop fork.

Service sector is nice but not scalable, too much overhead, and you just turn into a fire extinguisher for other people's problems, and burn out on your own stress.

[Eric]

1 hour ago, crockett said:

I know all to well about these issues. I have been using my own domains for emails since the mid 90s and I refuse to use liberal shits like Google or Yahoo.

Obviously they want to force everybody to use their free email service until everybody is hooked, so they can introduce paid services. Yet another (semi) monopoly forming. They keep adding "security" requirements that many small holsters can't comprehend or maintain.

I'm not shy telling businesses that they didn't get my emails because they use a tyrannical provider named Google. Back in the day I sold many hosting packages and domains with that slogan alone. At some point I made enough money from hosting. I had over 80 businesses on ONE box with well over 100 domains. I paid 120 bucks for that dedicated server each month and charged some companies 200 bucks for hosting, a domain and a few email addresses. The icing was the website development and maintenance.

But at some point I got tired of customer problems and started selling products through my own shop fork.

Service sector is nice but not scalable, too much overhead, and you just turn into a fire extinguisher for other people's problems, and burn out on your own stress.

Sometimes this stuff makes me tired all over.

I just changed the DB engine on all the database tables to innoDB. This post is a test.

[Eric]

Things seem to still work. Will wonders never cease.

[Swampfox762]

5 minutes ago, Eric said:

Things seem to still work. Will wonders never cease.

Thank ya sir...

[kerbie18]

You stated in another thread that a single user was eating up 10 percent of your space??  Are you going to make us guess? I'm guessing it's swampfox. Come on, you have to let us know.....