Re: [Sqlgrey-users] Using memcached with SQLGrey

[Dave S. <dst...@ma...>]

>>> Dan Faerch <da...@ha...> 6:33 PM Saturday, January 27, 2007 >>>
Dave Strickler wrote:
> I altered the source to do memcached lookups on everything except the =
"connect" table since that would get a lot of INSERTs that were never read =
again. I am seeing a 20%-40% hit on memcached, and the load on the =
Postgres server that handles the SQLGrey DB is down by a few LA "points".
>  =20
Sounds very interesting. One of my collegues have been going on and on=20
about how i should be using memcached for sqlgrey, but i was unsure =
that=20
it would do anything for us. By "LA" you mean Load Average?. And what=20
how much are "points"? -0.3 or -2.0 ect.?
>>>> Yes, LA=3DLoad Average. Our SQLGrey DB runs on a beefy server with =
one other DB that's almost all ready-only, and is not (yet) served via =
memcached. Since using SQLGrey with memcached, we are seeing a drop of =
about 2 LA points. From about 5 to about 3. Of course, this varies with =
time of day, etc.
=20

I dont know much about memcached, but INSERTS (and generally write=20
operations) are usually pretty heavy on SQL servers, so why not include=20
the "connect" table as well?
>>> I tried the connect table, and got "good", but not "great" performance.=
 I think this is due to the nature of the table, and the nature of a =
cache. I don't know about your site (and I would like to hear about it), =
but our connect table gets about 30% of it's initial INSERTs come back for =
a 2nd attempt, and are then removed from the table. So, the best cache we =
could ever expect was 30%, and by it's nature, the 30% will only benefit =
from a cache once. Think of the connect table as a "write-once, read-once" =
table. Tables like the AWL, are "write-once, read-many", and therefore =
greatly benefit from a cache. As with any caching system, you have to =
*watch* what you are caching. Just "caching it all" can add overhead, and =
fill up your cache with needless data.

And what about the boxes running memcached. Do they take the perfomance=20
hit instead of the SQL server?
>>> Boxes are older CPU servers that used to run SQL, etc, and have a lot =
of RAM, but CPUs that are lacking by today's standards. With memcached =
using RAM only, they certainly take the hit off SQL, but don't really take =
a hit themselves. Much like writing to a RAM disk in Linux, you don't see =
any noticeable increase in LA (or anything else) on heavy I/O functions. =
So, the servers running memcached aren't even dedicated servers, although =
we don't have them running any large CPU loads.

Im very curious as to what i can gain in a clustered setup like mine,=20
where each mailserver has its own SQL-slave, if each mailserver also =
has=20
to run memcached.
>>> My guess is your Cluster will get a lot less reads ;-)

> So, I am pleased with the memcached changes, and will tune the system a =
bit more and let everyone know when I have squeezed every bit of power =
from it. Once this is done, I will make the changes for v1.7 and get them =
to Lionel for anyone to use.=20
>  =20
You mean 1.7.4 right? That would be great, since it probably will make=20
it much easier for either of us to patch the CVS version.
>>> Yes, any version you want. I am going to share how to make a module =
(like is_in_awl()) use memcached, and let you all write the code the =
'real' way. I am not a full-time programmer, just a salty-dog CTO, and =
don't even know Perl. What I have is a hack, and I need a Perl coder to =
clean up the code. It's only about 20 lines of templated change in a few =
modules, so it won't be much work. Who and where should I send this code =
snippet ?
=20
--- Dave


- Dan

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share =
your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=3Djoin.php&p=3Dsourceforge&CID=3DDE=
VDEV=20
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...=20
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

This message has been certified virus-free by MailWise Filter - The real-ti=
me, intelligent, e-mail firewall used to scan inbound and outbound messages =
for SPAM, Viruses and Content. =0A=0A  For more information, please visit: http:=
//www.mailwise.com=0A