I am writing on the forum to see if you can help me with this issue, I have an A2Billing box with 8E1s which is used by a Dialer. A2Billing seems to not handle the load of calls as in the Dialer report I get several CALL_REJECTED that are not present when disabling the A2Billing script and just using it as gateway to PSTN.
I have 3 providers in the E1s and I do failover on MAXUSAGE (Don't know if this can be doing any harm) also I do LCR on this providers.
The database was tuned to better performance but the gain was very low, I changed cc_call and cc_trunk to INNODB in order to avoid locks but it didn't improve much. Another thing I tried was to move all the calls older than 1 day to cc_call_archive because we had 3 millions records in cc_call table, but that didn't help either.
Probably tonight or tomorrow I will build a test infrastructure (Probably better than this server) but using SIP trunks to see how A2Billing behave under load, I already did this some time ago but I don't know if the server can't handle the load or if its possible to tweak anything.
The Apache+PHP and Asterisk (Installed from Digium RPMs) are stock, here are the versions:
>> MySQLTuner 1.2.0 - Major Hayden <
[email protected]>
>> Bug reports, feature requests, and downloads at
http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3M (Tables: 91)
[--] Data in InnoDB tables: 520M (Tables: 4)
[OK] Total fragmented tables: 0
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 23h 3m 31s (5M q [14.732 qps], 552K conn, TX: 5B, RX: 1B)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 8.3G global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 9.6G (81% of installed RAM)
[OK] Slow queries: 0% (6/5M)
[OK] Highest usage of available connections: 41% (209/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/413.0K
[OK] Key buffer hit rate: 99.9% (37M cached / 19K reads)
[!!] Query cache efficiency: 16.7% (268K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 469K sorts)
[OK] Temporary tables created on disk: 0% (231 on disk / 199K total)
[OK] Thread cache hit rate: 98% (7K created / 552K connections)
[!!] Table cache hit rate: 0% (4 open / 27K opened)
[OK] Open file limit used: 0% (2/8K)
[OK] Table locks acquired immediately: 98% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 520.7M/8.0G
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.77 x86_64
Uptime = 3 days 23 hrs 3 min 53 sec
Avg. qps = 14
Total Questions = 5041646
Threads Connected = 3
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.htmlVisit
http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 6 out of 5041667 that take longer than 10 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See
http://dev.mysql.com/doc/refman/5.0/en/ ... overy.htmlWORKER THREADS
Current thread_cache_size = 120
Current threads_cached = 117
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 3
Historic max_used_connections = 209
The number of used connections is 41% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 293 M
Current InnoDB data space = 520 M
Current InnoDB buffer pool free = 71 %
Current innodb_buffer_pool_size = 8.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 8.82 G
Configured Max Per-thread Buffers : 1.34 G
Configured Max Global Buffers : 8.25 G
Configured Max Memory Limit : 9.60 G
Physical Memory : 11.73 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 417 K
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 1934
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 255 M
Current query_cache_used = 19 K
Current query_cache_limit = 31 M
Current Query cache Memory fill ratio = 0 %
Current query_cache_min_res_unit = 4 K
Query Cache is 50 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 27 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 8702 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 16384 tables
You have a total of 112 tables
You have 112 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 199773 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 181 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 98
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
Thank you very much.