☰ MENU
Characters▼
ACDB
Ads

Anime Characters Database Logo News

Reducing COUNT(*)'s Sep 5, 2011 | Rei
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.
30,000 and Sept traffic spike Sep 4, 2011 | Rei
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 ----. 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.
Tags, 29,000, Productivity, Updates Aug 24, 2011 | Rei
Tags

Past few days, my energy has been focused on creating a Tag Search, and expanding that to have all the features I can think of in a robust tags suite.

The next update will include browsing tags, and exploring member contributions.

29,000

We've officially past 29,000 characters! 30,000 can't be far off, and what an amazing milestone that will be! Thank you to everyone who has contributed, and a big thank you to anonymous17 who's added the most this summer! EatsSweets, flareburst, oo7565, many thanks as well!!

London Server Down, AGAIN

Ah, VPS.net ... this time last month it was down as well. But now it's been offline for an entire day while they replace their flawed HD infrastructure. Can only hope things will be better. A lot of the random stuff I post, I like to upload there. It being offline, shouldn't affect the site in any meaningful way.

Productivity

What does productivity have to do with the ACDB? Everything. It's the force that decides how much I get done, and when.

I look at productivity as a game. Discussions on the topic with a friend revealed I may have to accept it is perhaps a game that cannot be won. Nonetheless, I feel it is a game worth playing. An experience uniquely human, which all who "do" have unique insight into solving the puzzle of how to achieve maximum productivity.

At this point, I have no answers, merely more questions.

Updates

Made a few tweaks to the top of the site. Adding a complete feel to the theme.

I've expanded my programing skill to include knowledge of variable function references:


#include
#define MSG "Hello world!"

void print(char * s) {
printf("%s\n", s);
}

int main(void) {
void (*funcp)(char *);

(funcp) = &print;
(funcp)(MSG);

printf("%lx\n", (unsigned long)&print);

return 0;
}


And that's certainly NOT a Hello World prog I've ever seen before.

This knowledge is a prerequisite for creating software in C that will support "plugins" - new functions added at run time.

I'm looking forward to coding an empty program that serves as a template for plugins. Once it's running, I can add any function to it using dlfcn.h

From there, it'll be possible to continue my work replacing mysqld with my own database server.

Ads
Please know that cookies are required to operate and enhance our services as well as for advertising purposes. We value your privacy. If you are not comfortable with us using this information, please review your settings before continuing your visit. [close]
Ads

Anime Characters Database Logo Who's This Character Board | New Thread

06:19 am
Anonymous
Who is this ?
03:07 am
Anonymous
Who is this ?
03:06 am
Anonymous
Who is this ?

New Thread

Ads
All images are copyright of their respective owners. Copyright © 2007-2020 Goral Software

We had 57737 visitors yesterday. Tell a friend about ACDB and let's get that over 100000 tomorrow!