2019年の秋~2023年の夏まで、Twitter APIのトレンドキーワードデータを10分おきにため込んでいました。そのあたりの詳細はこちらの記事でもご紹介していますので、興味があればお読み下さい。

2023年7月にイーロンマスク体制となり、今まで大盤振る舞いで無料開放されていたTwitter APIも高額有料サービス化をされてしまい、データ収集はそこでやめてしまいましたが、この期間に集めたキーワードが269,000ワード、トランデータで9,600,000レコードを超えるデータとなってテーブルにたまっていました。

APIからデータが取れなくなって以降、データを使って作ったサイト(ツイータン)も放置してしまっていましたが、この年末年始に突然サイトの復活を思いついて作業を始めてしまい、その途中にこの900万レコードテーブルのインデックス再構築をやってみるかとやってしまったことで、さくらサーバのリソース制限を受けてしまうことになりました。

最終的に、別途用意したVPSサーバにDBを構築して、そこにデータ移行をするなどして負荷をさげることで無事解決できましたが、解決までに二週間くらい運営中の各サイトを死なせてしまっていました。

ということで、せっかくなので、ここまでの顛末をご紹介しようと思います。

さくらレンタルサーバのリソース制限

さくらのレンタルサーバでは、プログラム等によってサーバーに大きな負荷を生じさせてしまった場合に、リソース制限という形で、サーバ側での制限を受けることがあります。共用サーバなので、隣近所の方々にご迷惑かけないような配慮がされている感じです。

リソース制限を受けると

リソース制限を受けた状態になると、サイト表示が猛烈に遅くなったり、503エラーが多発したりが生じていました。単純にプロセス数を制限するといっただけではなく、CPUやメモリやネットワークも制限している感じでしたが、とにかくレンタルサーバにのせて運営中のサイトがほぼまともに表示できなくなっていました。

というのも、dtn.jpで運営しているほとんどのサイトが、何らかの形でDBアクセスをする機能があるため、リソースがなくなってしまうと、DB接続に使うPHPもまともに動かなくなり、サイト表示すらできなくなってしまっていたということのようです。

リソース制限時の管理画面表示

制限を受けると、管理画面内のリソース制限状況のボタンもONになり、何が原因で制限を受けているかがコメント表示されるようになっていました。

さくらインターネットのレンタルサーバでリソース制限中の管理画面

今回私の管理画面に表示されていたのはこのような内容でした。

2025/01/12 アクセス過多のため、アクセス数が制限されています。 データベースサーバーに負荷が掛かっております。 そのためCGI/PHPが制限されております。 ・サーバー(******.db.sakura.ne.jp)・データベース名(********)

さくらサーバ管理画面に表示されたリソース制限の詳細

データベース名は先ほどご紹介した900万超えレコードのテーブルがあるDB名でしたので、インデックス再構築がきっかけでリソース制限になったということのようです。

しかも、今回はALTER TABLEでもなく、DROP INDEXとCREATE INDEXでやろうと思って、DROPをしただけだったので、インデックスを削除しただけで、DBサーバに相当負荷がかかったということのようでした。恐ろしいですね。

リソース制限中のDBアクセス

サイトのフロントは503多発の状態になりましたが、レンタルサーバに標準装備のphpMyAdminもたまには動きますし、SSHも接続はできていたので、これらでなんとかインデックスを張り直して使えないかと最初は試していました。

とはいえ、PHPで動くphpMyAdminはLIMIT 100でデータを取るもできない状態でしたので、SQLクライアントかSSHで直接サーバに接続してしか期待できそうにありませんでした。

DBeaverを使ってDBアクセスを試みる

DBeaver」を仕事でも使っているので、いつも通りにこれでDB操作ができるか試してみました。LIMIT200であればデータ取得はできるのですが、もう少し多めに取ってみようとすると、DBeaverが処理落ちしてしまって使い物になりません。

DBeaverでiniファイルのMEMサイズを上げていないので、こちら側に原因があるのかもとは思いましたが、DBeaverを強制再起動して再度該当のDBに接続しようとすると、今度は接続もできないといった感じになっていたので、DBサーバ(***.db.sakura.ne.jp)側で、このDBへのアクセスをはじいている?といった感じなのかと思いました。

何度かやっても状況変わらずでしたので、今度はSSHでサーバーにログインして、直接mysqlコマンドを使って共用DBサーバに接続したりも試してはみました。

mysql -h 共用DBサーバのホスト名 -u ユーザー名 -p

mySQLには接続できているものの、該当のDBに処理をしようとすると、途中でMySQLの応答がなくなってしまい、一度処理落ちしてしまうと再接続すら受け付けてくれなくなってしまうという、SQLクライアントと似たような雰囲気にはなっていました。裏でゆっくり処理が進んでいるといった感じなのかもしれないですが、、、DBのログも見れないので、よく分かりません。

警告を受けたDB以外には問題なく接続は可能

サイト毎にDBは分けていましたが、接続できなくなったDB以外へのアクセス自体は特に問題ない感じはしていました。が、WEBサーバ側でリソース制限を受けている状態なので、サイト表示が猛烈に遅くなったり、PHPが処理落ちしたりするため、サイトはほぼ死んでいるような感じになっていました。

リソース制限の解除はメールで申請が必要

リソース制限を解除するには、さくらサーバ側にメールで改善報告をすることが必要になります。

※ プログラムの過負荷によりCGI/PHPが制限されている場合は、プログラムの設計を見直す、アクセス数の多いコンテンツは静的コンテンツに切り替えるなど、プログラムの起動数について改善をお願いいたします。

対応完了後、『メール』にてご連絡ください。
弊社にて「サーバーの負荷」 などを確認し、制限解除を実施いたします。

リソース情報に記載されている「制限」について知りたい

DBを別サーバに移行することにした

テーブルのインデックス再構築を諦めたタイミングで、もう変なことはやらないからリソース解除してくださいと一度お願いはしていたのですが、その翌日「お前やっぱりDB(サーバー:******.db.sakura.ne.jp、データベース名:********)に対してなんかやってるからダメ」と言われてしまったので、このDBにはしばらく触らないようにして、別サーバでDBを再構築する方法を考えることにしました。

ちなみに、その時のご案内はこんな感じでした。

本日2025年1月17日14時30分ごろ、問題の再発が確認されたことから、改めて対象サーバーサービスに対し、制限がかかっております。

恐れ入りますが問題の再発となることからも、解除の判断については慎重に行っており、即座の解除については対応ができかねます。今後も弊社にてサービスの状況確認を継続し、弊社の判断のもと解除をさせていただきたく存じます…

さくらインターネット カスタマーセンターからの返答

データ抽出にはやっぱりA5:SQL Mk-2

データ移行にあたり、最低限のマスタやトランデータを現行データベースから出力することは必要でしたので、ひとまずこれを考えることにしました。

DBeaverでデータ抽出をして失敗して、更にリソース制限が長引いてしまうのは嫌だったので、データ抽出に実績のある「A5:SQL Mk-2」を久しぶりに使ってみることにしました。

本業の方だったか、dtn.jp絡みだったかはさっぱり覚えていないですが、だいぶ昔にDBデータ移行をやった際に使ったことがあり、めちゃくちゃ軽くてスムーズに抜けた記憶があったので、久々クライアントをインストールしてみました。

使ってみると、昔ながらのサクサク感もそのままで、テーブルデータもスッキリ簡単に抜くことができました。しかも、DBeaverでコケまくっていたあの960万レコードのデータも、あっさりと全件取り出すことができてビックリしてしまいました。

もしかしたら、こっちでインデックス再構築をやってれば、そもそもこんなことにはならなかったのか???とも思いましたが、またDBに負荷をかけるのも嫌だったので試すのもやめました。

それにしても、動作も軽くて、win11対応もして下さっていて、本当に素晴らしいソフトです。

さくらサーバのリソースブースト機能

ちなみに、さくらサーバでは、リソース制限ボタンの隣にリソースブースト機能のボタンが用意がされています。これを押すと、2日後の24時までサーバの火力増強をさせることができるようになっているという、ありがたい機能です。

一度使うと7日間の利用制限があったので、最初にリソース制限を受けた時に慌てて使ったくらいでしたが、こういう時にも使えるというのは優しい感じがしました。私のような状態になった際には、ぜひポチッと押してみてください。

本来であれば、サイトのイベント時に火力増強するとか、そんな使い方をする機能とは思いますので、イベント前とかに押しておくといいのかもしれないです。

移行先はさくらのVPS(仮想専用サーバ)

一方、今回このDBデータの移行先として選んだサーバが、さくらのVPSサーバでした。

さくらインターネットのVPS

選んだといっても、ほかのホスティングサービスなんかと比較して選んだという感じではなく、たまたま別目的で契約して構築しつつあったVPSがあったので、慌ててそれを流用することにしたというのが正直なところです。

とはいえ、月額も牛丼大盛卵付き一杯よりも安く、Linux + Maria DBの環境が手軽に運用できるので、DBサーバとして使うには十分すぎるという感じです。

VPSサーバ初心者にも優しいサポート

VPSサーバなので、サーバ自体は自分で構築をしていく必要があります。

私がVPSを使っていたのがFreeBSD 6か7の時代だったので、今時のLinuxサーバ構築なんてよく分からい!といった感じでしたが、そんな私のような人間にも使えるように、ネコでもわかる!VPS講座や、構築関連ヘルプといったコンテンツが山ほど用意されていました。このあたりを順に読んでいけばVPS構築も簡単にできるようになっていて助かりました。

さくらのVPS講座

ネコでもわかる!さくらのVPS講座 〜第一回:VPSてなんだろう?〜

VPSでは、物理サーバーにインストールされているOS(ホストOS)の上に、ユーザーそれぞれに仮想サーバーが割り当てられ、ユーザーはその仮想サーバーを専有して使えます。仮想サーバーにはホストOSとは別のOS(ゲストOS)がインストールされており、ユーザーが管理者権限を持って自由に操作することができます。

ネコでもわかる!さくらのVPS講座 〜第一回:VPSてなんだろう?〜

使って分かった!さくらのVPSが超進化していたところ

dtn.jpドメインでも2008年~2015年ぐらいまでVPSを使っていた時代がありましたが、一周回ってレンタルサーバに戻ってしまっていたので、改めて久々VPSを使ってみてビックリしたことがありました。

超使えるかっこいい管理画面

さくらのVPSでは、VPSサーバ専用の管理画面が用意されていて、サーバの電源操作、OS再インストールや、パケットフィルタや監視に至るまで、サーバに関する色々な操作をこの画面からできるようになっていました。

さくらのVPS管理画面イメージ

私がVPSを使っていたのが2008年~2015年ぐらいだったと思いますが、その時代にはこんなカッコいい画面なんかありませんでしたので、契約後はSSHでVPSに接続して設定開始といった感じだったので、これにはほんとビックリしてしまいました。

昔だったら、VPSがプロセス不足でSSHにも接続できなくなったときなどは、サポセンにメールをしてリブートしてもらったりでしたし、この画面には本当に感動してしまいました。リモートマネージメント機能付きのオンプレサーバを数百万で買うとか馬鹿らしくなってしまいます。

インストールOSも自分で選べる

インストールするOSも、CentOS・・・Ubuntu・・・Debianまで選んで好きなものを画面から入れられるというのを見て、ひっくり返ってしまいました。

さくらのVPSでOSを選んでインストール

今どきは他社のVPSでもこれが標準なのかもしれないですが、時代の流れに感動してしまいました。

ちなみに、Windows Server OSプランもあるようですので、業務サーバもこれで十分ということなのかもと思ってしまったりしました。凄い時代です。

CentOSとMariaDBでDBサーバを構築

OSには馴染みのあるCentOS Stream 9を選びまして、DBも移行が簡単なようにMySQL後継のMariaDBを使うことにしました。リポジトリもEPEL、remiとかができていて、パッケージも探しやすくなっていました。

VPS構築にあたっては、さくらサーバ内の諸々のコンテンツや下記あたりが参考になりました。

リソース制限がいつの間にか解除に

VPSサーバの構築とDBデータ移行を一週間くらいで終え、サイト(ツイータン)側のDBアクセス部品改修も終わり、サイト表示も問題なくできるようになってほっとしたあたりで、さくらサーバの管理画面を見てみると、警告表示がいつの間にか消えておりました。

リソース制限の解除はメール連絡が必要と書きましたが、私の場合は、「もう信用できんから、さくらサーバ側で負荷状況を見てリソース制限解除を判断するよ」との状態になっていたので、サポセン側で使用状況を見て勝手に解除の判断をしていたようです。

あのままずるずるDB復旧をやっていたら、いつリソース制限解除されたかわからなかったので、サーバ移行して本当に良かったと思いました。

おわりに

年始早々に色々とやらかしてしまいましたが、結果的にdtn.jpの環境改善をやることができたのでよかったのかもしれません。月次で数百円のサーバ代が増えることにはなりましたが、毎月牛丼一杯分を小遣い減らして頑張りたいと思います。

このブログも1年前のちょうど2024年2月に記事を書いて放置してしまっていましたが、もう少しまめに更新できるように頑張ります。