Segmented Key Cache

[amjohnson]

has anyone tried using segmented key caches? It appears to be a feature in the MYISAM engine under Maria... It claims to reduce table locking.

Heres the link that has me intrigued..

https://mariadb.com/kb/en/segmented-key-cache/

I was wondering if it would freak Vicidial out or not to turn it on... I do not want to risk crashing my production system tho..

-Andrew

[williamconley]

Back up your DB after shift, turn it on (and configure it ... in some meaningful fashion) and run a "fake" campaign with a fake agent for a few minutes and see if anything breaks under light fake load.

If that gives you confidence, you can then try it under normal load. Or you can try a full benchmark with the load testing leads (60k of them in a sql file for your amusement in the extras folder I believe).

I would do a little more research first and make sure it's stable and that it truly applies. Also on your linked page the section "Statistics" doesn't ... have any statistics. So you may find that it is broken, worthless, or perfect. No idea. Do report back your findings though!

[amjohnson]

The statics page appears to work now and they are saying up to a 250% speed boost... DANG I would love to see that in the vicidialdb!!!

https://mariadb.com/kb/en/segmented-key ... rformance/

Unfortunately at the moment I do not have much time to experiment hence why i asked... I will put this on my to-do list and build a test system that will have standard hd's in it so it should be pretty easy to overwhelm. Then run the vicidial load test files etc like we did in training.

Trust me I wasn't going to just flip it on my production system with 150 agents and 5 million lead database... That could get ugly lol..

[williamconley]

I hear clucking.

[amjohnson]

Whats your cell phone and home phone again? I'm going to need someone to blame if the server crashes as well as someone for my boss to ream.

[williamconley]

As long as I get the credit card information first, we can work that out I think.

[amjohnson]

Just to let you know the results.. We have Segmented Key Cache running and it appears to have helped TREMENDOUSLY server load on an 8 core server went from 5.70-6.5 to 4.5-5.2.. Also It appears to have had about a 30% performance increase at the same usage level... IE faster queries. and less long queries while they are running..

I will get the exact settings we are using from the other tech here Daniel.. You met him at training.. He just kinda took it and ran with it.. Not sure what he has it set to at the moment. But at 28k-32k queries per sec and no issues I am not complaining...

[Vince-0]

Nice work! I've got vicidial_list table lock issues on about 6mil rows.

I'm investigating implementing a segmented key cache. I will let you know if I get any progress. Please let us know of your settings.

Vin

[Vince-0]

MariaDB docs for the Segmented Key Cache explain better IO performance by using noop scheduler.
https://mariadb.com/kb/en/segmented-key-cache-performance/

Is cfq scheduler any good? It looks to be default on Vicibox.

So if I look at my Vicidial 5.03 installed DB server my disk scheduler is on cfq:

Code: Select all

server:/var/run/mysqld # cat /sys/block/dm-0/queue/scheduler
none
server:/var/run/mysqld # cat /sys/block/dm-1/queue/scheduler
none
server:/var/run/mysqld # cat /sys/block/sda/queue/scheduler
noop deadline [cfq] 
server:/var/run/mysqld # cat /sys/block/sr0/queue/scheduler
noop deadline [cfq]

There does appear to be very little disk usage (LSI MegaRAID RAID 10 SAS) on from iostat even when load is 23.00+ on 16 cores:

Code: Select all

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   144.00    0.00  146.00     0.00  1156.00    15.84     4.14   28.36    0.00   28.36   1.87  27.30
dm-0              0.00     0.00    0.00  289.00     0.00  1156.00     8.00    14.18   49.08    0.00   49.08   0.94  27.30
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

My apologies if I hijacked your thread somewhat, I am planning a segmented key cache to get around locks on vicidial_list of over 8 seconds Query_time.

--
Version: 2.8-418a BUILD: 131029-2008 from Vicibox 5.0.3 ISO
Load: 16.61, 20.14, 20.59
HW Specs:
Master DB: Dual Xeon Hex E5630@2.53GHz 32GB RAM LSI MegaRAID SAS-926508i RAID10
Slave DB: Dual Xeon E5420@2.50GHz 16GB RAM LSI MegaRAID SAS 1078 RAID1
Dialer x7: Xeon Quad X5355@2.66GHz 8GB RAM SAS Perc5i RAID1
Web: Dual Xeon Quad E5420@2.50GHz 16GB RAM SAS Perc5i RAID1
Codecs: ulaw on softphones / g729 to carrier.
Termination: VoIP IAX
OS: DB: Vicibox OpenSuse 12.3, Web+Dialers OpenSuse from Vicibox 3.1.15

[amjohnson]

As far as database performance I usually run mtop --dbuser=root --seconds=1 to gauge how many long queries on have.. mtops QPS seems to be broken so for that I use MYSQL Workbench..

Also is your DM-0 and DM-1 a soft raid? I've never seen a Megaraid enumerate as a DM device thats usually soft raids... softraids on the database is bad even for the os since it uses the processor for raid... Altho all of my dialer machines are running the Intel Softraid without issue.

We are running 16 SSD's in raid 10 on a MEGARAID 9280-16i4e as one ridiculously fast drive. I know we changed the io scheduler for SD's.... The default scheduler is horrible for sd's.. Let me take a look and see what its set at..

cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

Looks like we are using noop...

We also have a TON of changes we do to the stock systems so much that I extracted the stock install image and modified it and recompiled the disk with most of our changes already made to speed up new server installation. We have been adding 2-4 servers per month lol..

[williamconley]

There does appear to be very little disk usage (LSI MegaRAID RAID 10 SAS) on from iostat even when load is 23.00+ on 16 cores:

Code: Select all

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   144.00    0.00  146.00     0.00  1156.00    15.84     4.14   28.36    0.00   28.36   1.87  27.30
dm-0              0.00     0.00    0.00  289.00     0.00  1156.00     8.00    14.18   49.08    0.00   49.08   0.94  27.30
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Remember that enough RAM will allow cacheing to greatly reduce i/o. Kinda the point of mysql Cacheing.

In fact, I would be greatly appreciative if someone would test out the ability to load the whole dang database into cache by loading the mysql DB into a virtual hard drive in memory then starting sql using that db. Of course, you'd need to replicate the db to protect against loss at regular intervals, but could likely do that when the system is idle or low enough on usage to be safe. I suspect this could cause the DB to jump ahead ... but if cacheing is already done properly, there may actually not be an advantage. Would like to know, though, and you two are all over this topic ...

We also have a TON of changes we do to the stock systems so much that I extracted the stock install image and modified it and recompiled the disk with most of our changes already made to speed up new server installation. We have been adding 2-4 servers per month lol..

Why not a drive image? Skip the "install" altogether. Just modify the network config and identity files after imaging each box if the Hardware is the same.

[Vince-0]

In fact, I would be greatly appreciative if someone would test out the ability to load the whole dang database into cache by loading the mysql DB into a virtual hard drive in memory then starting sql using that db.

The only problem is the asterisk database is 66GB..

[williamconley]

you should run the archiving script and move the archive tables into a different database. reduce asterisk db lag.

[amjohnson]

William,
The reason I do not do images is the fact that if your doing software raid dmraid seems to not play so nice from images... That and I have had kernel panic issues.. All of my dialer/agent servers (i keep them separate to make load calculating easier) are either ASUS rs100-X7's or the newer RS100-E8-PI2.. Atho there are issues with 3rd Gen Xeon and Intels MEI driver under SUSE 12.3 ... The servers hang on reboot... Adding 'blacklist mei' fixes the issue and does not seem to effect performance.

I also just sent you a PM with a modified script I use for making custom vicibox images. I'm interested in your opinion on it.

-Andrew

[williamconley]

software raid? that's crazy talk with a vicidial system.

[geoff3dmg]

Intel's MEI is for low level remote management (e.g. hung system half a world away). If you don't know what it is, you probably don't need it.

[williamconley]

Intel's MEI is for low level remote management (e.g. hung system half a world away). If you don't know what it is, you probably don't need it.

So far we have not found a reliable remote management system that isn't stupidly priced or dramatically handicapped (ie: in no way useful). Unless all you want is to reboot, in which case many of them are quite nice. But (LOL), we have specific devices for this that do not actually enter "the box" (external device for remote rebooting! not part of the server!). We've had a couple clients who needed them for direct control of their boxes during "challenging" software installations.

But MEI ... skip it. High-level remote management built into the Intel chip is something unlikely to be useful in a NONwindows environment unless careful planning goes into the integration and use of the package. Not something built into Vicibox or Goautodial. I wonder if Ubuntu will have a viable driver some day, though.

[okli]

Sorry to bring up an old topic, but that's quite interesting new(now old) feature of MariaDB.

Has anyone else used it and got similarly good results?