[OFF-TOPIC] Freeradius and MySQL Cluster
Good Morning, I am doing some tests with MySQL Cluster, and I want to migrate my backend to it. But I am having some "troubles/doubts" with it. When I use a empty radius database, it works ok. But when I try to import my current database to MySQL with the NDBCLUSTER engine, I got this error: ERROR 1114 (HY000) at line 143: The table 'radacct' is full I think that I must do some tuning to adjust MySQL cluster to my requirements, but I don't know exactly what to change, below is my config.ini: [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=2 # Number of replicas DataMemory=512M # How much memory to allocate for data storage IndexMemory=512M # How much memory to allocate for index storage # For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. [tcp default] # TCP/IP options: portnumber=2202 # This the default; however, you can use any # port that is free for all the hosts in the cluster # Note: It is recommended that you do not specify the port # number at all and simply allow the default value to be used # instead [ndb_mgmd] # Management process options: hostname=192.168.1.32 # Hostname or IP address of MGM node datadir=/var/lib/mysql-cluster # Directory for MGM node log files [ndbd] hostname=192.168.1.30 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files [ndbd] hostname=192.168.1.31 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files [mysqld] [mysqld] I know that this is not the best place to talk about database problems, but I think that there is a lot of people here that use Freeradius with MySQL Cluster that could help me. Thanks in advance.
Hi,
I know that this is not the best place to talk about database problems, but I think that there is a lot of people here that use Freeradius with MySQL Cluster that could help me.
used to - moved to postgres. anyway, there *are* other more useful resources for using MySQL: http://www.lmgtfy.com/?q=mysql+cluster+reference+the+table+is+full&l=1 alan
2012/2/27 Antonio Modesto <modesto@isimples.com.br>:
Good Morning,
I am doing some tests with MySQL Cluster, and I want to migrate my backend to it. But I am having some "troubles/doubts" with it. When I use a empty radius database, it works ok. But when I try to import my current database to MySQL with the NDBCLUSTER engine, I got this error:
ERROR 1114 (HY000) at line 143: The table 'radacct' is full
Is your cluster full?
I think that I must do some tuning to adjust MySQL cluster to my requirements, but I don't know exactly what to change, below is my config.ini:
mysql cluster is a different beast altogether. My best advice, if you go down that route, is to get the help of an expert. Seriously. If you DO get it running though, it will worth every effort. Some things you could try to check: - is the db full? mysql cluster is limited by how much amount of memory you allocate to the data nodes. Don't be surpised if you ended up with only 1 or 2 GB usable space. - try splitting the import into smaller transaction. It's a hassle, I know. But if you try to insert/update/delete a large number of rows (e.g. 1 million rows) at once, then mysql cluster will complain. Splitting it manually into a small number (e.g. 1k rows) on each transaction should work. Again, if you still can't get it to work, hire an expert. Or ask in mysql list/forums, as this is not really radius-specific. Any huge write in one transaction should cause the same problem. -- Fajar
This is about the best tool for MySQL cluster for tuning your DB. http://www.severalnines.com/cluster-configurator/ David -----Original Message----- From: freeradius-users-bounces+davidp=wirelessconnections.net@lists.freeradius.org [mailto:freeradius-users-bounces+davidp=wirelessconnections.net@lists.freera dius.org] On Behalf Of Fajar A. Nugraha Sent: Monday, February 27, 2012 8:20 AM To: FreeRadius users mailing list Subject: Re: [OFF-TOPIC] Freeradius and MySQL Cluster 2012/2/27 Antonio Modesto <modesto@isimples.com.br>:
Good Morning,
I am doing some tests with MySQL Cluster, and I want to migrate my backend to it. But I am having some "troubles/doubts" with it. When I use a empty radius database, it works ok. But when I try to import my current database to MySQL with the NDBCLUSTER engine, I got this error:
ERROR 1114 (HY000) at line 143: The table 'radacct' is full
Is your cluster full?
I think that I must do some tuning to adjust MySQL cluster to my requirements, but I don't know exactly what to change, below is my config.ini:
mysql cluster is a different beast altogether. My best advice, if you go down that route, is to get the help of an expert. Seriously. If you DO get it running though, it will worth every effort. Some things you could try to check: - is the db full? mysql cluster is limited by how much amount of memory you allocate to the data nodes. Don't be surpised if you ended up with only 1 or 2 GB usable space. - try splitting the import into smaller transaction. It's a hassle, I know. But if you try to insert/update/delete a large number of rows (e.g. 1 million rows) at once, then mysql cluster will complain. Splitting it manually into a small number (e.g. 1k rows) on each transaction should work. Again, if you still can't get it to work, hire an expert. Or ask in mysql list/forums, as this is not really radius-specific. Any huge write in one transaction should cause the same problem. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mon, 2012-02-27 at 20:20 +0700, Fajar A. Nugraha wrote:
2012/2/27 Antonio Modesto <modesto@isimples.com.br>:
Good Morning,
I am doing some tests with MySQL Cluster, and I want to migrate my backend to it. But I am having some "troubles/doubts" with it. When I use a empty radius database, it works ok. But when I try to import my current database to MySQL with the NDBCLUSTER engine, I got this error:
ERROR 1114 (HY000) at line 143: The table 'radacct' is full
Is your cluster full?
I am not using the cluster, it was inactive, I just set it up, did some tests, then I tried to import the actual radius database to it.
I think that I must do some tuning to adjust MySQL cluster to my requirements, but I don't know exactly what to change, below is my config.ini:
mysql cluster is a different beast altogether. My best advice, if you go down that route, is to get the help of an expert. Seriously. If you DO get it running though, it will worth every effort.
Some things you could try to check: - is the db full? mysql cluster is limited by how much amount of memory you allocate to the data nodes. Don't be surpised if you ended up with only 1 or 2 GB usable space. - try splitting the import into smaller transaction. It's a hassle, I know. But if you try to insert/update/delete a large number of rows (e.g. 1 million rows) at once, then mysql cluster will complain. Splitting it manually into a small number (e.g. 1k rows) on each transaction should work.
Again, if you still can't get it to work, hire an expert. Or ask in mysql list/forums, as this is not really radius-specific. Any huge write in one transaction should cause the same problem.
I didn't know about the memory limit. So if I have a 10GB database, Must I have, at least, 10GB configured for each cluster node? Thanks.
On Mon, Feb 27, 2012 at 9:48 PM, Antonio Modesto <modesto@isimples.com.br> wrote:
I didn't know about the memory limit. So if I have a 10GB database, Must I have, at least, 10GB configured for each cluster node?
David posted a link which can help assign memory on data nodes. Short version is: - while mysql cluster can store some data on disk, you really should put it all on memory. Otherwise there wouldn't be much speed improvement - only a portion of memory on data node can be used as data memory - multiple data nodes can add the available data memory, but replicas trade memory for availability (thus reducing the number of memory for data) so if you have (for example) 4 datanodes, 16GB total memory each, and configure them as 2 node group @2 replicas, don't be surprised if total usable memory for data is around 10-20 GB. When your memory is limited and radacct is huge, better keep them separated: - put tables OTHER than radacct on cluster - put radacct on normal mysql instance - store "live" accouting data on cluster The last part is kinda hard, since you need to create your own queries. The default sql queries doesn't split "live" (e.g. records that don't have acct-stop yet) and "archive" data. -- Fajar
On Mon, 2012-02-27 at 22:09 +0700, Fajar A. Nugraha wrote:
On Mon, Feb 27, 2012 at 9:48 PM, Antonio Modesto <modesto@isimples.com.br> wrote:
I didn't know about the memory limit. So if I have a 10GB database, Must I have, at least, 10GB configured for each cluster node?
David posted a link which can help assign memory on data nodes. Short version is: - while mysql cluster can store some data on disk, you really should put it all on memory. Otherwise there wouldn't be much speed improvement - only a portion of memory on data node can be used as data memory - multiple data nodes can add the available data memory, but replicas trade memory for availability (thus reducing the number of memory for data)
so if you have (for example) 4 datanodes, 16GB total memory each, and configure them as 2 node group @2 replicas, don't be surprised if total usable memory for data is around 10-20 GB.
When your memory is limited and radacct is huge, better keep them separated: - put tables OTHER than radacct on cluster - put radacct on normal mysql instance - store "live" accouting data on cluster
The last part is kinda hard, since you need to create your own queries. The default sql queries doesn't split "live" (e.g. records that don't have acct-stop yet) and "archive" data.
Understood. Today my radius database is small, about 1GB, I think I can run everything on memory. I will configure another datanodes with more available memory and see what happens.
participants (4)
-
Alan Buxey -
Antonio Modesto -
David Peterson -
Fajar A. Nugraha