Can not add new environment to tenant/project

(Andrei Rochev) #1

I try add new environment on tenant -> projects page and catch error:
SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.",
“FullException”: "Exception occured while executing a reader for SELECT * FROM dbo.[TenantVariable] WHERE ( (OwnerId IN (@projectid_0, @projectid_1, @projectid_2, @projectid_3, @projectid_4, @projectid_5
, @projectid_6, @projectid_7, @projectid_8, @projectid_9, @projectid_10, @projectid_11, @projectid_12, @projectid_13, @projectid_14, @projectid_15, @projectid_16, @projectid_17, @projectid_18, @projectid_1
9, @projectid_20, @projectid_21, @projectid_22, @projectid_23, @projectid_24, @projectid_25, @projectid_26, @projectid_27, @projectid_28, @projectid_29, @projectid_30, @projectid_31, @projectid_32, @projec
tid_33, @projectid_34, @projectid_35, @projectid_36, @projectid_37, @projectid_38, @projectid_39, @projectid_40, @projectid_41, @projectid_42, @projectid_43, @projectid_44, @projectid_45, @projectid_46, @p
rojectid_47, @projectid_48, @projectid_49, @projectid_50, @projectid_51, @projectid_52, @projectid_53, @projectid_54, @projectid_55, @projectid_56, @projectid_57, @projectid_58, @projectid_59, @projectid_6
0, @projectid_61, @projectid_62, @projectid_63, @projectid_64, @projectid_65, @projectid_66, @projectid_67, @projectid_68, @projectid_69, @projectid_70, @projectid_71, @projectid_72, @projectid_73, @projec
tid_74, @projectid_75, @projectid_76, @projectid_77, @projectid_78, @projectid_79, @projectid_80, @projectid_81, @projectid_82, @projectid_83, @projectid_84, @projectid_85, @projectid_86, @projectid_87, @p
rojectid_88, @projectid_89, @projectid_90, @projectid_91, @projectid_92, @projectid_93, @projectid_94, @projectid_95, @projectid_96, @projectid_97, @projectid_98, @projectid_99, @projectid_100, @projectid_
101, @projectid_102, @projectid_103, @projectid_104, @projectid_105, @projectid_106, @projectid_107, @projectid_108, @projectid_109, @projectid_110, @projectid_111, @projectid_112, @projectid_113, @project
id_114, @projectid_115, @projectid_116, @projectid_117, @projectid_118, @projectid_119, @projectid_120, @projectid_121, @projectid_122, @projectid_123, @projectid_124, @projectid_125, @projectid_126, @proj
ectid_127, @projectid_12



265, @projectid_3266, @projectid_3267, @projectid_3268, @projectid_3269, @projectid_3270, @projectid_3271, @projectid_3272, @projectid_3273, @projectid_3274, @projectid_3275, @projectid_3276, @projectid_32
77, @projectid_3278, @projectid_3279, @projectid_3280, @projectid_3281, @projectid_3282, @projectid_3283, @projectid_3284, @projectid_3285, @projectid_3286, @projectid_3287, @projectid_3288, @projectid_328
9, @projectid_3290, @projectid_3291, @projectid_3292, @projectid_3293, @projectid_3294, @projectid_3295, @projectid_3296, @projectid_3297, @projectid_3298, @projectid_3299, @projectid_3300, @projectid_3301
, @projectid_3302, @projectid_3303, @projectid_3304, @projectid_3305, @projectid_3306, @projectid_3307, @projectid_3308, @projectid_3309, @projectid_3310, @projectid_3311, @projectid_3312, @projectid_3313,
@projectid_3314, @projectid_3315, @projectid_3316, @projectid_3317, @projectid_3318, @projectid_3319, @projectid_3320, @projectid_3321, @projectid_3322, @projectid_3323, @projectid_3324, @projectid_3325,
@projectid_3326, @projectid_3327, @projectid_3328, @projectid_3329, @projectid_3330, @projectid_3331, @projectid_3332, @projectid_3333, @projectid_3334, @projectid_3335, @projectid_3336, @projectid_3337, @
projectid_3338, @projectid_3339, @projectid_3340, @projectid_3341, @projectid_3342, @projectid_3343, @projectid_3344, @projectid_3345, @projectid_3346, @projectid_3347, @projectid_3348, @projectid_3349, @p
rojectid_3350, @projectid_3351, @projectid_3352, @projectid_3353, @projectid_3354, @projectid_3355, @projectid_3356, @projectid_3357, @projectid_3358, @projectid_3359, @projectid_3360, @projectid_3361, @pr
ojectid_3362, @projectid_3363, @projectid_3364, @projectid_3365, @projectid_3366, @projectid_3367, @projectid_3368, @projectid_3369, @projectid_3370, @projectid_3371, @projectid_3372, @projectid_3373, @pro
jectid_3374, @projectid_3375, @projectid_3376, @projectid_3377, @projectid_3378, @projectid_3379, @projectid_3380, @projectid_3381, @projectid_3382, @projectid_3383, @projectid_3384, @projectid_3385, @proj
ectid_3386, @projectid_3387, @projectid_3388, @projectid_3389, @projectid_3390, @projectid_3391, @projectid_3392, @projectid_3393, @projectid_3394, @projectid_3395, @projectid_3396, @projectid_3397, @proje
ctid_3398, @projectid_3399, @projectid_3400, @projectid_3401, @projectid_3402, @projectid_3403, @projectid_3404, @projectid_3405, @projectid_3406, @projectid_3407, @projectid_3408, @projectid_3409, @projec
tid_3410, @projectid_3411, @projectid_3412, @projectid_3413, @projectid_3414, @projectid_3415, @projectid_3416, @projectid_3417, @projectid_3418, @projectid_3419, @projectid_3420, @projectid_3421, @project
id_3422, @projectid_3423, @projectid_3424, @projectid_3425, @projectid_3426, @projectid_3427, @projectid_3428, @projectid_3429, @projectid_3430, @projectid_3431, @projectid_3432, @projectid_3433, @projecti
d_3434, @projectid_3435, @projectid_3436, @projectid_3437, @projectid_3438, @projectid_3439, @projectid_3440, @projectid_3441, @projectid_3442, @projectid_3443, @projectid_3444, @projectid_3445, @projectid
_3446, @projectid_3447, @projectid_3448, @projectid_3449) AND EnvironmentId IS NOT NULL) OR (OwnerId IN (@librarysetids_0, @librarysetids_1, @librarysetids_2, @librarysetids_3, @librarysetids_4, @librarys
etids_5, @librarysetids_6, @librarysetids_7, @librarysetids_8, @librarysetids_9, @librarysetids_10, @librarysetids_11, @librarysetids_12, @librarysetids_13, @librarysetids_14, @librarysetids_15, @libraryse
tids_16, @librarysetids_17, @librarysetids_18, @librarysetids_19, @librarysetids_20, @librarysetids_21, @librarysetids_22, @librarysetids_23, @librarysetids_24, @librarysetids_25, @librarysetids_26, @libra
rysetids_27, @librarysetids_28, @librarysetids_29, @librarysetids_30, @librarysetids_31, @librarysetids_32, @librarysetids_33, @librarysetids_34, @librarysetids_35, @librarysetids_36, @librarysetids_37, @l
ibrarysetids_38, @librarysetids_39, @librarysetids_40, @librarysetids_41, @librarysetids_42, @librarysetids_43, @librarysetids_44, @librarysetids_45, @librarysetids_46, @librarysetids_47, @librarysetids_48
, @librarysetids_49, @librarysetids_50, @librarysetids_51, @librarysetids_52, @librarysetids_53, @librarysetids_54, @librarysetids_55, @librarysetids_56, @librarysetids_57, @librarysetids_58, @librarysetid
s_59, @librarysetids_60, @librarysetids_61, @librarysetids_62, @librarysetids_63, @librarysetids_64, @librarysetids_65, @librarysetids_66, @librarysetids_67, @librarysetids_68, @librarysetids_69, @librarys
etids_70) AND EnvironmentId IS NULL)) ORDER BY [Id]
System.Exception
at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, String operationName)
at Nevermore.RelationalTransaction.Stream
at System.Collections.Generic.List1…ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at Octopus.Server.Orchestration.Deploy.TenantVariableLoader.GetTenantVariables(List1 projects, IEnumerable1 libraryVariableSets, List1 tenants, String tenantId, String projectId, String environmentId)
at Octopus.Server.Orchestration.Deploy.TenantVariableLoader.GetIterator(String tenantId, String projectId, String environmentId)
at Octopus.Server.Orchestration.Deploy.TenantVariableLoader.MisingByTenant(String tenantId, String projectId, String environmentId)
at Octopus.Server.Web.Api.Actions.TenantVariablesMissingAction.Execute()
at Octopus.Server.Web.Infrastructure.Api.Responder1.Respond(TDescriptor options, NancyContext context)
at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x)
at Nancy.Routing.Route.<>c__DisplayClass4.b__3(Object parameters, CancellationToken context)

–Inner Exception–
SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataR
eady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean
inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& tas
k, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass5_0.b__0()
at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)"
}

Now we have 3450 projects and octopus works with this realy realy slow - dashboard loading time 1-2 mins!

(Cameron MacFarland) #2

Hi Andrei,

Thanks for contacting us. Wow, that’s a lot of projects. Why do you have so many?

I can confirm that there is a bug with that query and we are working on it right now. As for performance in general we have a page that discusses what can affect performance, and how best to tackle the problem. https://octopus.com/docs/administration/performance

Meanwhile, I’ve opened an issue for the bug here - https://github.com/OctopusDeploy/Issues/issues/3742

Thanks
Cam

(Andrei Rochev) #3

We have so many project because we have so many customers, and for each customer we can deploy many our products/

This projects ware before “tenant” feature was implemented.

Thanks for link to perf page and for the issue

(Andrei Rochev) #4

Cameron, may be You are interested in our octopus server, I guess You can learn may pitfalls and corner cases

(Dmitry Lobanov) #5

Hello Cameron, yes, we have so many projects, and Octopus cannot help us reduce this number at this moment. If you´re interested we can have some call where we can describe our usage pattern. It seems that we use Octopus in a quite special way. Maybe you will have some insights how this can be improved.

Tenanted deployments won´t work for us for some reasons as well.

Also, Octopus performance is really awful. We know you guys do your best, but Octopus performance is really devastating. It´s so bad that our operations cry with bloody tears. We just hope that you guys will slowly fix this problem. We´ve reported about our problems for quite some time, but it didn´t get any better. For example, we even tweak MS SQL DB views/indexes ourselves after each Octopus upgrade. But the major concern is UI performance - Octopus crashes Chrome browser in several minutes, depending on circumstances, and when it’s not - UI is very slow. Sometimes it takes several minutes to just change value for 1 variable.

(system) closed #6