Hello Hans,
Some months ago you told me that you fix memory leak under heavy load.
We have just deploy it on Debian Wheezy + DHCPD 4.2.2 ... Cross fingers :)
About database design, I think that 'leases' table should be partitioned by year and month.
In our case we use it for ISP that has over 10.000 subscribers that means many many leases per day.
And after few weeks running it will be probably imposible to query database quickly
During my table partitioning process I've got issues :
First of all be able to generate partition "lstart" fied type must be 'datetime' instead of 'timestamp' else Mysql returns following error : "Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed".
Moreover lstart must be set as primary key.
I've created partitions as bellow :
PARTITION p2015 VALUES LESS THAN (2016)
(SUBPARTITION p201501 ENGINE = InnoDB,
SUBPARTITION p201502 ENGINE = InnoDB,
SUBPARTITION p201503 ENGINE = InnoDB,
SUBPARTITION p201504 ENGINE = InnoDB,
SUBPARTITION p201505 ENGINE = InnoDB,
SUBPARTITION p201506 ENGINE = InnoDB,
SUBPARTITION p201507 ENGINE = InnoDB,
SUBPARTITION p201508 ENGINE = InnoDB,
SUBPARTITION p201509 ENGINE = InnoDB,
SUBPARTITION p201510 ENGINE = InnoDB,
SUBPARTITION p201511 ENGINE = InnoDB,
SUBPARTITION p201512 ENGINE = InnoDB)
As you can see I've created only partition for this current yes. It should be interesting to add an Event that create partition/subpartition automaticaly per year.
Finaly I think that default encoding should be UTF8 and we must add somes foreign keys into leases tables to maintain coherence.
Least but not last, a view that join all the hws, cids, rids, ips and leases tables would be great.
Please give me some feedback
Thank you
Hello Hans,
Some months ago you told me that you fix memory leak under heavy load.
We have just deploy it on Debian Wheezy + DHCPD 4.2.2 ... Cross fingers :)
About database design, I think that 'leases' table should be partitioned by year and month.
In our case we use it for ISP that has over 10.000 subscribers that means many many leases per day.
And after few weeks running it will be probably imposible to query database quickly
During my table partitioning process I've got issues :
First of all be able to generate partition "lstart" fied type must be 'datetime' instead of 'timestamp' else Mysql returns following error : "Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed".
Moreover lstart must be set as primary key.
I've created partitions as bellow :
PARTITION p2015 VALUES LESS THAN (2016)
(SUBPARTITION p201501 ENGINE = InnoDB,
SUBPARTITION p201502 ENGINE = InnoDB,
SUBPARTITION p201503 ENGINE = InnoDB,
SUBPARTITION p201504 ENGINE = InnoDB,
SUBPARTITION p201505 ENGINE = InnoDB,
SUBPARTITION p201506 ENGINE = InnoDB,
SUBPARTITION p201507 ENGINE = InnoDB,
SUBPARTITION p201508 ENGINE = InnoDB,
SUBPARTITION p201509 ENGINE = InnoDB,
SUBPARTITION p201510 ENGINE = InnoDB,
SUBPARTITION p201511 ENGINE = InnoDB,
SUBPARTITION p201512 ENGINE = InnoDB)
As you can see I've created only partition for this current yes. It should be interesting to add an Event that create partition/subpartition automaticaly per year.
Finaly I think that default encoding should be UTF8 and we must add somes foreign keys into leases tables to maintain coherence.
Least but not last, a view that join all the hws, cids, rids, ips and leases tables would be great.
Please give me some feedback
Thank you