• News
• New
• Index
• About

One quick update before I crash for the night...

Added Character Popularity to all anime and game pages. Look for it in the details summary, near the end of the list.

How it works is for every character assigned to the series, we do a count of how many people have added them to their favorites list.

So if the characters have been favorite'd 20 times, the series will have a Character Popularity of 20.

Kinda makes for a cool way to see how popular a series is. I'll do up a page listing series ordered by Character Popularity in a bit as well.

Made a little progress on the slow pages issue.

While about to start coding my C optimized database stats server, I began thinking what IPC protocols I'd implement, and how to design the functions relevant to what I need right now... then it hit me.

Primary key look ups, properly indexed will take O(log(n)log(n)), as will similar updates.

However, COUNT(*) operations, on a constantly changing table, have no better optimization than O(n) . The entire list must be traversed every time.

When you consider 30,000 rows requiring 30,000 comparison operations, repeated some 500,000 times daily... it's no wonder the CPU usage of the MySQLD has been so high as of lately.

Perhaps the better solution is to properly design cached tables with the same information, calculating it once and using for the entire day. Rather than calculating it on the fly.



I also wrote a simple script to check into the process list of MySQLD. I'd love to catch it at a stall point, but for now it's a game of chance that so far has confirmed one thing I know.

Locked rows stall a lot of reads.

Currently using a memory table. May consider moving back to InnoDB. I'll have to review the locks supported / used on memory table though.


Also reduced some redundant queries on character pages.

And implemented bin-log-sync. Less to worry about next server crash.


hah, and our London Server - what a joke. so much down time. so many crashes. i might move to another data center. somewhere with a better track record as of late.

Hit 30,000 characters just a couple days ago. Wonder what the odds are we can keep up a thousand a month? We're at 30134 right now.

Also with the start of September, there's been a massive spike in traffic. 35,721 visitors and 8,557,151 page views on September 2nd? Jeez.

I've made some tweaks with the MySQL DB in hopes of temporary buying some more time.

tuning-primer.sh suggested
low_priority_updates=1
sync_binlog=1

So I'll be trying that.

All these view counters... seem to be the root of the problem.

Also binary tree indexes, now that I understand them in more detail....

Auto increment primary key, binary tree index. WHAT THE fork. How do sites with 100,000,000+ entries deal with the auto rebuild of the index after x new records are added triggering an index rebuild to recenter the index?

Buy faster hardware? Stupid.

Use a different type of index? Now we're getting somewhere...

Hopefully as I study the problem, I'll learn more about what the options are so I can laugh at the ignorant me later on. XD

Seeing how Memory Table storage engine in MySQL supports only hash indexes... I'd consider that as a reasonable alternative.... YET

even a memory table with 10% writes and 90% selects can still LOCK up with 10+ seconds from time to time. un****ing believable. must be a design flaw in mysql, cause i sure as **** can design something a lot better in C. and will.

30,000 records spanning 4 INTs can, and should not take more than mere nano seconds for any operation. heck, even 300,000 rows shouldn't take more than a few nano seconds.

there must be indexes well suited for sequential numbers....

anyways.


Optimizing the site has rather been my only focus as of lately. Not that there isn't a lot else going on.


Character pages have been updated to include

* Date Created
* Date Modified
* References


Started work on a Dash Board, something of a Heads Up Display with the goal to quickly show what changes have been happening on the site of interest to moderators.


Plans to rework the Who's This Character Board are also in the works.

More of a focus on displaying images, moving away from the original "linked images only" model.


Also updating large parts of the character code, and trying to handle different types of duplicates correctly. Redirecting to original in case of a complete duplicate. And informing in the case of an update / outdated version.