Hi all, So it is really database issue ? I've truncate radacct table, and now i turning on slow query on mysql database. I see weird at radipool, some of user connected but no data on that table ony 18 from 64 row, is this normal too ? Is it from that make FR receive conficting packet ? Thank you. Regards, Franky Yu
Ah. The usual word 'mysql'. There are many many optimising tweaks you can use with that database. Firstly look at the engine in use, next look at your config and tweak buffers - there are many utilities that can help you with this. As already mentioned by others, this is a DB issue not a freeradius one so go onto a couple of mysql lists and forums. One immediate plan i'd suggest is to move to eg postgresql - its 'out of box' operation is much faster with needs for less tweaks. If you do have a fixation with mysql then move to MariaDB alan
Hi Alan, After i turning on slow query log, this conflicting packet still come even there are no slow query come on the log. I'll try your suggestion join mysql milist. Thanks. Regards, Franky Yu On Apr 8, 2015 6:40 PM, "Alan Buxey" <A.L.M.Buxey@lboro.ac.uk> wrote:
Ah. The usual word 'mysql'. There are many many optimising tweaks you can use with that database. Firstly look at the engine in use, next look at your config and tweak buffers - there are many utilities that can help you with this. As already mentioned by others, this is a DB issue not a freeradius one so go onto a couple of mysql lists and forums.
One immediate plan i'd suggest is to move to eg postgresql - its 'out of box' operation is much faster with needs for less tweaks. If you do have a fixation with mysql then move to MariaDB
alan
On Wed, Apr 8, 2015 at 9:09 PM, battossai <battossai@gmail.com> wrote:
Hi Alan,
After i turning on slow query log, this conflicting packet still come even there are no slow query come on the log
You DO know that there is a tunable for mimimum time a query must be before it shows up in slow query log? Try reducing it .
I'll try your suggestion join mysql milist.
And read mysql docs. Really. You'll find out about the tunable above, plus simple things like "show full processlist" -- Fajar
On Thu, Apr 9, 2015 at 8:19 AM, Fajar A. Nugraha <list@fajar.net> wrote:
You DO know that there is a tunable for mimimum time a query must be before it shows up in slow query log? Try reducing it
I have try set long_query_time from 0,1 still not seen what kind of slow query log :(.
On 9 Apr 2015, at 22:34, battossai <battossai@gmail.com> wrote:
On Thu, Apr 9, 2015 at 8:19 AM, Fajar A. Nugraha <list@fajar.net> wrote:
You DO know that there is a tunable for mimimum time a query must be before it shows up in slow query log? Try reducing it
I have try set long_query_time from 0,1 still not seen what kind of slow query log :(.
Use tcmpdump, it's pretty easy to follow. Query should pop out in plaintext, and any string fields in the result should be visible too. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Hi All, After successfully logged slow query and try the tool for percona, i got following information : # Files: /var/log/mysqld-query.log # Overall: 14.41k total, 76 unique, 0.24 QPS, 0.01x concurrency __________ # Time range: 2015-04-10 18:49:07 to 2015-04-11 11:29:38 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 393s 1us 15s 27ms 515us 402ms 35us # Lock time 31s 0 6s 2ms 93us 76ms 0 # Rows sent 33.81k 0 30.21k 2.40 0.99 256.76 0 # Rows examine 249.17k 0 30.21k 17.71 124.25 259.70 0 # Query size 1.89M 6 823 137.21 400.73 115.56 118.34 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============== # 1 0x1A61082779F6EA70 155.2667 39.5% 688 0.2257 7.69 UPDATE radippool # 2 0x649FE766E6C792F2 111.2241 28.3% 102 1.0904 4.66 UPDATE radippool # 3 0x233A3C990AB652AF 21.2620 5.4% 656 0.0324 7.71 SELECT radcheck # 4 0x09C707F15C885832 15.5012 3.9% 89 0.1742 0.59 UPDATE radacct # 5 0xC28E6B22647A9A2D 14.4608 3.7% 3 4.8203 6.32 SHOW TABLE STATUS # 6 0x814513E415DE18DF 13.8613 3.5% 904 0.0153 4.99 SET # 7 0xAD494B098E55BCEB 11.4666 2.9% 659 0.0174 1.66 SELECT rm_settings # 8 0x8D4E82CE1D6A3531 10.4838 2.7% 85 0.1233 0.48 UPDATE radacct # 9 0x6C9260C121FCCE11 9.2282 2.3% 660 0.0140 1.22 SELECT radacct # 10 0xB79BB638732D11F9 5.9229 1.5% 655 0.0090 2.20 SELECT radippool # 11 0xB9A6F64D487D2A65 5.0763 1.3% 657 0.0077 0.87 SELECT rm_specperbw # MISC 0xMISC 19.5249 5.0% 9250 0.0021 0.0 <65 ITEMS> Is there any action i can do ? Thanks. Regards, Franky Yu
Indexes, smarter queries, change db engine etc. There are many more basic tools that will actually tell you some steps to take. I really suggest you go off and read some mysql books. Then you can rewritten to your database with some 'explain' commands and know what you are doing, what the results mean and what to do to fix things. alan
participants (4)
-
Alan Buxey -
Arran Cudbard-Bell -
battossai -
Fajar A. Nugraha