hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work. I trying to add indexes to this table. It make fast selects, but slow updates. [code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code] Next I tried to make table from the begin. Here it is [code] CREATE TABLE `radippool_new` ( `id` int(4) unsigned NOT NULL AUTO_INCREMENT, `pool_name` tinyint(1) unsigned NOT NULL, `framedipaddress` int(4) unsigned NOT NULL, `nasipaddress` int(4) unsigned DEFAULT NULL, `calledstationid` bigint(8) unsigned NOT NULL, `callingstationid` bigint(8) unsigned NOT NULL, `expiry_time` timestamp NULL DEFAULT NULL, `username` varchar(64) NOT NULL, `pool_key` int(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Ip pool for customers' CREATE TABLE `radippool_names` ( `id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 [/code] It's working two time faster, than default with updates queries. But two time slower with selects queries. Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it? *Regards,* Alexander
On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh <avkosykh@gmail.com> wrote:
hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work.
I have over 1 million subscribers. Then again, I'm using mysql cluster :)
I trying to add indexes to this table. It make fast selects, but slow updates.
Duh! :D That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously.
[code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code]
You really shouldn't use myisam for heavy-write tables.
Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it?
It's kinda complicated. I've been meaning to submit a patch, but it's just not that easy. For starters, about mysql storage engines: - myisam -> good for majority of reads, terrible for writes. Plus it lacks transactional support, which usually means that with the default setup you can either hand out duplicate IP to clients, or stuck with slow table locks. - innodb -> somewhat balanced for both read-write, and have transactional support, but mostly disk-bound. especially if you enforce cosistency by flushing to disk on every transaction. - memory -> good-enough for writes (i.e. not disk-bound, obviously), but still suffer from table locks - NDB (i.e. mysql cluster) -> good for lots of parallel writes, usually not disk-bound Now performance-wise using NDB or memory would be best, but it won't be the best choice for common mysql setups. You MIGHT be able to gain some improvements by using innodb (without changing anything else). Maybe. Then there's the problem with queries. The default sqlippool query would basically lock the table during IP assignment (due to SELECT ... FOR UPDATE). I opted to change the query to NOT use table locks (using randomization instead), at the expense of possible duplicate ip assignment. In my case when a client gets duplicate IP, the NAS will reject the user, so the user will dial again, and (hopefully) get a unique free ip address this time. It's an acceptable workaround for me, but it won't be implementable in "normal" setups. Add to that the fact that I had to implement it using stored procedures, and you can see how it gets pretty complicated. So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix) -- Fajar
On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote:
That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously.
Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them. However, I'm quite surprised that you're having problems with 25k subscribers; that's not a large table. What is the query rate? Do you have very low interim accounting values perhaps, meaning you're extending the IP "lease" times too frequently?
So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix)
- use postgres ;o) In all seriousness, It's worth noting that postgres does have the advantage that "select ... for update" uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally.
On Thu, Jan 12, 2012 at 8:15 PM, Phil Mayers <p.mayers@imperial.ac.uk> wrote:
So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix)
- use postgres
;o)
I avoid postgres like plague in the past due to vacuum :) It has autovacuum now, but the bad image remains. To be fair, it's probably similar with the way many people avoid mysql due to myisam-related issues :)
In all seriousness, It's worth noting that postgres does have the advantage that "select ... for update" uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally.
Really? Did you personally test it, running the select queries manually? Mysql is also capable to do so (at least innodb and ndb does), but the thing is how many rows got locked depends on how many rows were selected. So a query like SELECT ... FOR UPDATE LIMIT .... would end up locking all rows (the limit clause pretty much don't matter), in effect doing a table lock, while SELECT ... FOR UPDATE LIMIT .... WHERE ID IN (...) would end up locking only several rows. This is where ndb truly shines, as huge amounts of concurrent process with row locks (around 128 in my setup) can run in paralel without interfering with each other, as long as the selected rows don't overlap (this is where randomization comes in). The process to limit the id in my setup right now has the possiblity of causing duplicate IP address handed out. It should be possible to fix this, but I haven't had the time and my current setup is good enough for now. -- Fajar
On 01/12/2012 01:33 PM, Fajar A. Nugraha wrote:
I avoid postgres like plague in the past due to vacuum :) It has autovacuum now, but the bad image remains. To be fair, it's probably similar with the way many people avoid mysql due to myisam-related issues :)
Funnily enough I avoid MySQL like the plague because of <list of things> You're right of course. People must weigh the products and make their own choice.
In all seriousness, It's worth noting that postgres does have the advantage that "select ... for update" uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally.
Really? Did you personally test it, running the select queries manually?
Yes. This is a core feature. Postgres has done row-level locking for many, many years. We use it all over the place for lots of things.
would end up locking only several rows. This is where ndb truly shines, as huge amounts of concurrent process with row locks (around
The multiple storage engine nonsense (just my opinion, of course) is one of the many reasons I don't use MySQL.
I have interim accounting value 10 minutes and IP lease time is 30 minutes. 2012/1/12 Phil Mayers <p.mayers@imperial.ac.uk>
On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote:
That's why having a dba is important. If you can't do it yourself,
hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously.
Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them.
However, I'm quite surprised that you're having problems with 25k subscribers; that's not a large table.
What is the query rate? Do you have very low interim accounting values perhaps, meaning you're extending the IP "lease" times too frequently?
So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix)
- use postgres
;o)
In all seriousness, It's worth noting that postgres does have the advantage that "select ... for update" uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 01/12/2012 01:40 PM, Alexander Kosykh wrote:
I have interim accounting value 10 minutes and IP lease time is 30 minutes.
So you should be getting, what, ~40 accounting packets/second with 25k users online. Does this correspond to the query load you're seeing? 40 SQL updates/second is not a lot... If you increase your interim accounting to 1800 seconds, you'll decrease that load to about 15 queries/second, which might be a quick and easy win. But it shouldn't be going that slow with that load IMHO.
One more question. Where can I take nas-type value to use it in user authorization? Radius take it from mysql nasinfo table at startup. I take it from DB every time subscriber try to authorize. Regards, Alexander. 2012/1/12 Fajar A. Nugraha <list@fajar.net>
On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh <avkosykh@gmail.com> wrote:
hi Is anyone have a success story of optimizing ippool sql table to make it work faster? The default scheme is very slow, and then you have about 25000 subscribers it's not work.
I have over 1 million subscribers. Then again, I'm using mysql cluster :)
I trying to add indexes to this table. It make fast selects, but slow updates.
Duh! :D That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously.
[code] CREATE TABLE `radippool` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pool_name` varchar(30) NOT NULL, `framedipaddress` varchar(15) NOT NULL DEFAULT '', `nasipaddress` varchar(15) NOT NULL DEFAULT '', `calledstationid` varchar(30) NOT NULL, `callingstationid` varchar(30) NOT NULL, `expiry_time` datetime DEFAULT NULL, `username` varchar(64) NOT NULL DEFAULT '', `pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8 [/code]
You really shouldn't use myisam for heavy-write tables.
Maybe some one have a good modification of ippool table or another changes of default scheme, and could share it?
It's kinda complicated. I've been meaning to submit a patch, but it's just not that easy.
For starters, about mysql storage engines: - myisam -> good for majority of reads, terrible for writes. Plus it lacks transactional support, which usually means that with the default setup you can either hand out duplicate IP to clients, or stuck with slow table locks. - innodb -> somewhat balanced for both read-write, and have transactional support, but mostly disk-bound. especially if you enforce cosistency by flushing to disk on every transaction. - memory -> good-enough for writes (i.e. not disk-bound, obviously), but still suffer from table locks - NDB (i.e. mysql cluster) -> good for lots of parallel writes, usually not disk-bound
Now performance-wise using NDB or memory would be best, but it won't be the best choice for common mysql setups. You MIGHT be able to gain some improvements by using innodb (without changing anything else). Maybe.
Then there's the problem with queries. The default sqlippool query would basically lock the table during IP assignment (due to SELECT ... FOR UPDATE). I opted to change the query to NOT use table locks (using randomization instead), at the expense of possible duplicate ip assignment. In my case when a client gets duplicate IP, the NAS will reject the user, so the user will dial again, and (hopefully) get a unique free ip address this time. It's an acceptable workaround for me, but it won't be implementable in "normal" setups. Add to that the fact that I had to implement it using stored procedures, and you can see how it gets pretty complicated.
So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix)
-- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Thu, Jan 12, 2012 at 8:30 PM, Alexander Kosykh <avkosykh@gmail.com> wrote:
One more question.
Where can I take nas-type value to use it in user authorization? Radius take it from mysql nasinfo table at startup. I take it from DB every time subscriber try to authorize.
That's pretty much the only way. Unless your nas sends it as an attribute (which should be visible when you ran FR in debug mode) -- Fajar
participants (3)
-
Alexander Kosykh -
Fajar A. Nugraha -
Phil Mayers